Re: Support for REINDEX CONCURRENTLY

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
6) process till newindex->insisready (no new locks)
7) process till newindex->indisvalid (no new locks)
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 exlusively which should be "invisible" now
12) drop old index

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...

Greetings,

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