Re: pg_reorg in core?

On Tue, Sep 25, 2012 at 8:13 AM, Andres Freund <andres(at)2ndquadrant(dot)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(at)agliodbs(dot)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