On Wed, Oct 3, 2012 at 8:08 PM, Andres Freund
<andres@2ndquadrant.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
