Re: pg_reorg in core? - Mailing list pgsql-hackers

On Tuesday, September 25, 2012 04:37:05 AM Michael Paquier wrote:
> On Tue, Sep 25, 2012 at 8:13 AM, Andres Freund <andres@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 <josh@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.
Sorry, was a bit tired when writing the above.

The point is that to work concurrent the CONCURRENT operations commit/start 
multiple transactions internally. It can be interrupted (user, shutdown, error, 
crash) and leave transient state behind every time it does so. What I wanted to 
say is that we need to take care that each of those can easily be cleaned up 
afterwards.

> > 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?
No. Only

SELECT array_to_string(array_agg(relname), ', ') FROM pg_class WHERE 
relisshared AND relkind = 'r';

their toast tables and their indexes are shared. The problem is that for those 
you cannot create a separate index and let it update concurrently because you 
cannot write into each databases pg_class/pg_index.

> > 3. no support for the indexes of exclusion constraints (not hard I think)
> This just consists in a check of indisready in pg_index.
It will probably be several places, but yea, I don't think its hard.

Andres
-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services