Re: Why this lock?

On Tue, Aug 25, 2015 at 8:33 AM, Johann Spies <johann.spies@gmail.com> wrote:
> I have a long-running query (running now for more than 6 days already
> (process 17434).  It involves three tables of which one contains XML-data.
>
> On another, unrelated table with  30718567 records, I ran a query to create
> an index on a field.  This morning I cancelled this process because it did
> not finish after 5 days.
>
> I then did a "vacuum analyze" on that table and rerun the query (process
> 9732) to create the index.  It soon stalled again and the following result
> shows that proces 17434 is blocking it:
>
> locktype  | database | relation | page | tuple | virtualxid | transactionid
> | classid | objid | objsubid | virtualtransaction | pid  |   mode    |
> granted | fastpath | virtualtransaction |  pid  |     mode      | granted
>
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+-----------+---------+----------+--------------------+-------+---------------+---------
>  virtualxid |          |          |      |       | 6/24891    |
> |         |       |          | 7/27906            | 9732 | ShareLock | f
> | f        | 6/24891            | 17434 | ExclusiveLock | t
>
>
> Now my questions:
>
> What would cause such a lock?
> What can I do to remove the lock without stopping the long-running process
> which started before the query to create the index?  I suppose I just have
> to wait for the first process to finish...

creating and index requires exclusive access.  did you try the
concurrent variant?

merlin