Re: deadlock in REINDEX

Neil Conway <neilc(at)samurai(dot)com> writes:
> 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?

Only if the cure isn't worse than the disease.

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

If you release the lock then I think you are opening yourself to worse
troubles than this one, having to do with someone renaming/deleting the
table and/or index out from under you. The fact that REINDEX INDEX
might fail if there are concurrent conflicting operations doesn't bother
me a whole lot; but not holding a lock throughout the operation does.

AFAICS, REINDEX INDEX is only a disaster-recovery tool anyway, and so is
not likely to be run in parallel with other operations. The scenarios
I can think of where you might want to do REINDEX routinely would always
use REINDEX TABLE, I should think.

BTW, I imagine DROP INDEX has a similar issue, and CLUSTER might
depending on what it locks first (but it would be easy to fix it
to lock the table first, since it has both names).

regards, tom lane