Neil Conway <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly