Why this lock?

From: Johann Spies <johann(dot)spies(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Why this lock?
Date: 2015-08-25 13:33:11
Message-ID: CAGZ55DTamTh-TaRP_WCytGTXzaLUwEviewKsj4oeSw=b-mMfQA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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...

Regards
Johann
--
Because experiencing your loyal love is better than life itself,
my lips will praise you. (Psalm 63:3)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Merlin Moncure 2015-08-25 13:52:53 Re: Why this lock?
Previous Message Neil Tiffin 2015-08-25 13:19:30 Re: PostgreSQL Developer Best Practices