Re: Support for REINDEX CONCURRENTLY

On Wednesday, October 03, 2012 11:42:25 PM Michael Paquier wrote:
> On 2012/10/04, at 5:41, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> > On Wednesday, October 03, 2012 10:12:58 PM Michael Paquier wrote:
> >> On 2012/10/03, at 23:52, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> >>> 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
> >>
> >> Just à note...
> >> My patch drops the locks on parent table and indexes at the end of
> >> process, after dropping the old indexes ;)
> >
> > I think that might result in deadlocks with concurrent sessions in some
> > circumstances if those other sessions already have a lower level lock on
> > the index. Thats why I think dropping the lock on the index and then
> > reacquiring an access exlusive might be necessary.
> > Its not a too likely scenario, but why not do it right if its just 3
> > lines...
>
> Tom is right. This scenario does not cover the case where you drop the
> parent table or you drop the index, which is indeed invisible, but still
> has a pg_class and a pg_index entry, from a different session after step
> 10 and before step 11. So you cannot either drop the locks on indexes
> until you are done at step 12.
Yep:
> Yea, the session lock on the table itself probably shouldn't be dropped.
But that does *not* mean you cannot avoid lock upgrade issues by dropping the
lower level lock on the index first and only then acquiring the access exlusive
lock. Note that dropping an index always includes *first* getting a lock on the
table so doing it that way is safe and just the same as a normal drop index.

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