Skip site navigation (1) Skip section navigation (2)

Re: Support for REINDEX CONCURRENTLY

From: Andres Freund <andres(at)2ndquadrant(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Cc: Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Greg Stark <stark(at)mit(dot)edu>
Subject: Re: Support for REINDEX CONCURRENTLY
Date: 2012-10-03 21:53:52
Message-ID: 201210032353.53544.andres@2ndquadrant.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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


In response to

pgsql-hackers by date

Next:From: Daniel FarinaDate: 2012-10-03 22:08:18
Subject: Detecting libpq connections improperly shared via fork()
Previous:From: Martijn van OosterhoutDate: 2012-10-03 21:52:00
Subject: Re: do we EXEC_BACKEND on Mac OS X?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group