Re: pg_reorg in core?

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...

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