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... To make it viable to use that systemwide it might be necessary to batch the individual steps together for multiple indexes because all that waiting is going to suck if you do it for every single table in the database while you also have longrunning queries... 2. no support for concurrent on system tables (not easy for shared catalogs) 3. no support for the indexes of exlusion constraints (not hard I think) Greetings, Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
