Re: Support for REINDEX CONCURRENTLY

On Sat, Oct 6, 2012 at 6:14 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> > Maybe another idea is that the reindexing is staged: the user would
> > first run a command to create the replacement index, and leave both
> > present until the user runs a second command (which acquires a strong
> > lock) that executes the switch.  Somehow similar to a constraint created
> > as NOT VALID (which runs without a strong lock) which can be later
> > validated separately.
>
> Yeah.  We could consider
>
> 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. The possibility
to do that in a single command would be also better perhaps seen from the
user.

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? Is it a postgres
requirement to maintain the object OIDs consistent between DDL operations?
If the OID of old and new index are different, the relcache entries of each
index will be completely separated, and this would take care of any
visibility problems regarding visibility. pg_reorg for example changes the
relation OID of the table reorganized after operation is completed.

Thoughts about that?
-- 
Michael Paquier
http://michael.otacoo.com