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

2. no support for concurrent on system tables (not easy for shared catalogs)

3. no support for the indexes of exclusion constraints (not hard I think)