On Tue, Sep 25, 2012 at 8:13 AM, Andres Freund <and...@2ndquadrant.com>wrote:
> On Tuesday, September 25, 2012 12:55:35 AM Josh Berkus wrote: > > On 9/24/12 3:43 PM, Simon Riggs wrote: > > > On 24 September 2012 17:36, Josh Berkus <j...@agliodbs.com> wrote: > > >>>> For me, the Postgres user interface should include > > >>>> * REINDEX CONCURRENTLY > > >> > > >> I don't see why we don't have REINDEX CONCURRENTLY now. > > > > > > Same reason for everything on (anyone's) TODO list. > > > > Yes, I'm just pointing out that it would be a very small patch for > > someone, and that AFAIK it didn't make it on the TODO list yet. > Its not *that* small. > > 1. You need more than you can do with CREATE INDEX CONCURRENTLY and DROP > INDEX > CONCURRENTLY because the index can e.g. be referenced by a foreign key > constraint. So you need to replace the existing index oid with a new one by > swapping the relfilenodes of both after verifying several side conditions > (indcheckxmin, indisvalid, indisready). > > It would probably have to look like: > > - build new index with indisready = false > - newindex.indisready = true > - wait > - newindex.indisvalid = true > - wait > - swap(oldindex.relfilenode, newindex.relfilenode) > - oldindex.indisvalid = false > - wait > - oldindex.indisready = false > - wait > - drop new index with old relfilenode > > Every wait indicates an externally visible state which you might > encounter/need > to cleanup... > Could you clarify what do you mean here by cleanup? I am afraid I do not get your point here. > 2. no support for concurrent on system tables (not easy for shared > catalogs) > Doesn't this exclude all the tables that are in the schema catalog? > > 3. no support for the indexes of exclusion constraints (not hard I think) > This just consists in a check of indisready in pg_index. -- Michael Paquier http://michael.otacoo.com