Re: Support for REINDEX CONCURRENTLY

On Wednesday, October 03, 2012 01:15:27 PM Michael Paquier wrote:
> On Wed, Oct 3, 2012 at 8:08 PM, Andres Freund <andres(at)2ndquadrant(dot)com>wrote:
> > On Wednesday, October 03, 2012 12:59:25 PM Greg Stark wrote:
> > > Just for background. The showstopper for REINDEX concurrently was not
> > > that it was particularly hard to actually do the reindexing. But it's
> > > not obvious how to obtain a lock on both the old and new index without
> > > creating a deadlock risk. I don't remember exactly where the deadlock
> > > risk lies but there are two indexes to lock and whichever order you
> > > obtain the locks it might be possible for someone else to be waiting
> > > to obtain them in the opposite order.
> > >
> > > I'm sure it's possible to solve the problem. But the footwork needed
> > > to release locks then reobtain them in the right order and verify that
> > > the index hasn't changed out from under you might be a lot of
> > > headache.
> >
> > Maybe I am missing something here, but reindex concurrently should do
> > 1) BEGIN
> > 12) drop old index
>
> The code I sent already does that more or less btw. Just that it can be
> more simplified...
The above just tried to describe the stuff thats relevant for locking, maybe I
wasn't clear enough on that ;)

> > I don't see where the deadlock danger is hidden in that?
> > I didn't find anything relevant in a quick search of the archives...
>
> About the deadlock issues, do you mean the case where 2 sessions are
> running REINDEX and/or REINDEX CONCURRENTLY on the same table or index in
> parallel?
No idea. The bit about deadlocks originally came from Greg, not me ;)

I guess its more the interaction with normal sessions, because the locking
used (SHARE UPDATE EXLUSIVE) prevents another CONCURRENT action running at the
same time. I don't really see the danger there though because we should never
need to acquire locks that we don't already have except the final
AccessExclusiveLock but thats after we dropped other locks and after the index
is made unusable.

Greetings,

Andres
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services