Обсуждение: Why this lock?
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 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)
my lips will praise you. (Psalm 63:3)
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
On 25 August 2015 at 15:52, Merlin Moncure <mmoncure@gmail.com> wrote:
h...
creating and index requires exclusive access. did you try the
concurrent variant?
Yes. The one which I stopped after 5 days, was running concurrently. There was a similar lock involved.
Regards
Johann
--
Because experiencing your loyal love is better than life itself,
my lips will praise you. (Psalm 63:3)
my lips will praise you. (Psalm 63:3)
Johann Spies <johann.spies@gmail.com> writes: > On 25 August 2015 at 15:52, Merlin Moncure <mmoncure@gmail.com> wrote: >> creating and index requires exclusive access. did you try the >> concurrent variant? > Yes. The one which I stopped after 5 days, was running concurrently. There > was a similar lock involved. That lock type is used by CREATE INDEX CONCURRENTLY when it has to wait out another transaction. There is no way around this, it's an inherent part of that algorithm. http://www.postgresql.org/docs/9.4/static/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY regards, tom lane