Re: Support for REINDEX CONCURRENTLY

On 2013-01-24 13:29:56 -0500, Robert Haas wrote:
> On Wed, Jan 23, 2013 at 1:45 PM, Alvaro Herrera
> <alvherre(at)2ndquadrant(dot)com> wrote:
> > Andres Freund escribió:
> >> I somewhat dislike the fact that CONCURRENTLY isn't really concurrent
> >> here (for the listeners: swapping the indexes acquires exlusive locks) ,
> >> but I don't see any other naming being better.
> >
> > REINDEX ALMOST CONCURRENTLY?
>
> I'm kind of unconvinced of the value proposition of this patch. I
> mean, you can DROP INDEX CONCURRENTLY and CREATE INDEX CONCURRENTLY
> today, so ... how is this better?

In the wake of beb850e1d873f8920a78b9b9ee27e9f87c95592f I wrote a script
to do this and it really is harder than one might think:
* you cannot do it in the database as CONCURRENTLY cannot be used in a
TX
* you cannot do it to toast tables (this is currently broken in the
patch but should be fixable)
* you cannot legally do it when foreign key reference your unique key
* you cannot do it to exclusion constraints or non-immediate indexes

All of those are fixable (and most are) within REINDEX CONCURRENLY, so I
find that to be a major feature even if its not as good as it could be.

Greetings,

Andres Freund

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