Re: Support for REINDEX CONCURRENTLY

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
> 2) Lock table in share update exlusive
> 3) lock old index
> 3) create new index
> 4) obtain session locks on table, old index, new index
> 5) commit

Build new index.

> 6) process till newindex->insisready (no new locks)
>
validate new index

> 7) process till newindex->indisvalid (no new locks)
>
Forgot the swap old index/new index.

> 8) process till !oldindex->indisvalid (no new locks)
> 9) process till !oldindex->indisready (no new locks)
> 10) drop all session locks
> 11) lock old index exclusively which should be "invisible" now
> 12) drop old index
>
The code I sent already does that more or less btw. Just that it can be
more simplified...

> 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?
--
Michael Paquier
http://michael.otacoo.com