Re: deadlock in REINDEX

Neil Conway <neilc(at)samurai(dot)com> writes:
> On Mon, 2003-02-17 at 18:39, Tom Lane wrote:
>> 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.

> Presumably, the renaming/deleting operation acquires an exclusive lock
> and then holds it until transaction commit, right? If so, then wouldn't
> we still be okay: the REINDEX would lock the index in access share mode,
> find the OID of the heap rel, unlock the index, lock the heap rel in
> access exclusive mode, then try to re-open & lock the index, find that
> it no longer exists and then elog(ERROR).

That approach might be deadlock-free, but that doesn't mean it is
surprise-free. For example, if the other guy did an ALTER TABLE RENAME
on the index, it'd be possible that what you are actually reindexing
is now differently named than it was before (and, perhaps, there is now
some other index that has the original name and is the one the user
really meant). This is not so dangerous in the REINDEX case, maybe,
but it could be unhappy-making in the DROP case.

> Whether or not that solution actually works, ISTM there must be *some*
> method of locking that is free of deadlocks -- saying "oh well, it's not
> a common case anyway" doesn't strike me as being satisfactory :-\

Basically, I'm not convinced that a deadlock failure is so much worse
than any other failure that we should open ourselves to other surprises
in order to avoid a deadlock. It's a judgment call though. Any other
comments out there?

regards, tom lane