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

Re: Support for REINDEX CONCURRENTLY

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


In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2012-10-05 23:56:40
Subject: Bugs in CREATE/DROP INDEX CONCURRENTLY
Previous:From: Michael PaquierDate: 2012-10-05 23:12:59
Subject: Re: Support for REINDEX CONCURRENTLY

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