Re: Support for REINDEX CONCURRENTLY

Michael Paquier <michael(dot)paquier(at)gmail(dot)com> writes:
> On Sat, Oct 6, 2012 at 6:14 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> CREATE INDEX CONCURRENTLY (already exists)
>> SWAP INDEXES (requires ex-lock, swaps names and constraint dependencies;
>> or maybe just implement as swap of relfilenodes?)
>> DROP INDEX CONCURRENTLY

> OK. That is a different approach and would limit strictly the amount of
> code necessary for the feature, but I feel that it breaks the nature of
> CONCURRENTLY which should run without any exclusive locks.

Hm? The whole point is that the CONCURRENTLY commands don't require
exclusive locks. Only the SWAP command would.

> Until now all the approaches investigated (switch of relfilenode, switch of
> index OID) need to have an exclusive lock because we try to maintain index
> OID as consistent. In the patch I submitted, the new index created has a
> different OID than the old index, and simply switches names. So after the
> REINDEX CONCURRENTLY the OID of index on the table is different, but seen
> from user the name is the same. Is it acceptable to consider that a reindex
> concurrently could change the OID of the index rebuild?

That is not going to work without ex-lock somewhere. If you change the
index's OID then you will have to change pg_constraint and pg_depend
entries referencing it, and that creates race condition hazards for
other processes looking at those catalogs. I'm not convinced that you
can even do a rename safely without ex-lock. Basically, any DDL update
on an active index is going to be dangerous and probably impossible
without lock, IMO.

To answer your question, I don't think anyone would object to the
index's OID changing if the operation were safe otherwise. But I don't
think that allowing that gets us to a safe solution.

regards, tom lane