deadlock in REINDEX

From: Neil Conway <neilc(at)samurai(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: deadlock in REINDEX
Date: 2003-02-17 22:27:41
Message-ID: 1045520861.32097.20.camel@tokyo
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I noticed a pretty obscure deadlock condition with REINDEX in CVS HEAD:

client1:

nconway=# create table a (b int unique, c int unique);
CREATE TABLE
nconway=# begin;
BEGIN
nconway=# lock table a in access exclusive mode;
LOCK TABLE

client2:

nconway=# reindex index a_b_key;
< blocks, waiting to acquire an access exclusive lock on the heap
relation "a" >

client1:

nconway=# reindex table a;
ERROR: deadlock detected

Naturally, this situation is not a very common one. But it seems to me
that the practice of acquiring locks in REINDEX in an inconsistent order
is asking for trouble: REINDEX TABLE locks the heap rel first, followed
by any indexes of the heap rel, but REINDEX INDEX locks the target
index, followed by the heap rel. Hence a deadlock condition (the
explicit lock table above just serves to make the window of opportunity
much larger).

This should be fixed, right?

I was thinking of changing reindex_index() to acquire an AccessShareLock
on the index in question, find its parent rel ID, release the lock, then
acquire an AccessExclusiveLock on the parent rel, followed by an
AccessExclusiveLock on the index in question.

Comments?

Cheers,

Neil

P.S. I noticed this because I was browsing through REINDEX, trying to
see if it would be possible to allow it to acquire less exclusive
locks...
--
Neil Conway <neilc(at)samurai(dot)com> || PGP Key ID: DB3C29FC

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2003-02-17 22:42:21 Re: Version 7.2.3 Vacuum abnormality
Previous Message Tom Lane 2003-02-17 22:09:06 Re: POSIX regex performance bug in 7.3 Vs. 7.2