Re: Why this lock?

On Tue, Aug 25, 2015 at 8:33 AM, Johann Spies <johann(dot)spies(at)gmail(dot)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