Re: Support for REINDEX CONCURRENTLY

On 2012/10/04, at 5:41, Andres Freund <andres@2ndquadrant.com> wrote:

> On Wednesday, October 03, 2012 10:12:58 PM Michael Paquier wrote:
>> On 2012/10/03, at 23:52, Andres Freund <andres@2ndquadrant.com> wrote:
>>> On Wednesday, October 03, 2012 04:28:59 PM Tom Lane wrote:
>>>> Andres Freund <andres@2ndquadrant.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