Skip site navigation (1) Skip section navigation (2)

Re: pg_reorg in core?

From: Andres Freund <andres(at)2ndquadrant(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Cc: Josh Berkus <josh(at)agliodbs(dot)com>, Simon Riggs <simon(at)2ndquadrant(dot)com>
Subject: Re: pg_reorg in core?
Date: 2012-09-24 23:13:15
Message-ID: 201209250113.16076.andres@2ndquadrant.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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


In response to

Responses

pgsql-hackers by date

Next:From: Andres FreundDate: 2012-09-24 23:46:18
Subject: Re: DROP INDEX CONCURRENTLY is not really concurrency safe & leaves around undroppable indexes
Previous:From: Andrew DunstanDate: 2012-09-24 22:59:12
Subject: Oid registry

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group