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