Re: [HACKERS] Press Release Draft - 2016-02-09 Cumulative Update

Tobias Bussmann wrote:

> But I could put this
> snippet as a "REINDEX CONCURRENTLY" workaround into the Administrative
> Snippets category of the wiki, if there are no further objections
> about the way it works.

Sounds like a good idea.  There are further complications:

* you can't DROP indexes belonging to constraints, so this recipe
doesn't work for them.  One useful trick is to create the index first,
then ADD CONSTRAINT USING INDEX.

* For unique constraints referenced by FKs, the above doesn't work
either.  One thing you can do is create a second index and swap the
relfilenode underneath.  This is a nasty, dirty, dangerous, unsupported
trick, but it can save people's neck at times.

> I always have a bit of mixed feelings with these kind of string
> manipulations on dynamic SQL.

It may look a bit nasty, but locking tables for long periods (or being
without an important index for a period) is much worse in production
scenarios.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services