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

Re: Support for REINDEX CONCURRENTLY

From: Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
To: Andres Freund <andres(at)2ndquadrant(dot)com>
Cc: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>, 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:42:25
Message-ID: 77E14896-E83D-4204-BCCE-DD822738DFDC@gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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.
> 
> Andres
> -- 
> Andres Freund        http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services


In response to

Responses

pgsql-hackers by date

Next:From: Martijn van OosterhoutDate: 2012-10-03 21:52:00
Subject: Re: do we EXEC_BACKEND on Mac OS X?
Previous:From: Alvaro HerreraDate: 2012-10-03 21:28:00
Subject: Re: ALTER command reworks

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