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 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)
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 | tNow 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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)
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general