Re: Support for REINDEX CONCURRENTLY

On Wednesday, October 03, 2012 04:28:59 PM Tom Lane wrote:
> Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> > 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
>
> You can't drop the session locks until you're done. Consider somebody
> else trying to do a DROP TABLE between steps 10 and 11, for instance.
Yea, the session lock on the table itself probably shouldn't be dropped. If
were holding only that one there shouldn't be any additional deadlock dangers
when dropping the index due to lock upgrades as were doing the normal dance
any DROP INDEX does. They seem pretty unlikely in a !valid !ready table
anyway.

Greetings,

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