Re: Press Release Draft - 2016-02-09 Cumulative Update

From: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
To: Tobias Bussmann <t(dot)bussmann(at)gmx(dot)net>
Cc: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>, "Jonathan S(dot) Katz" <jkatz(at)postgresql(dot)org>
Subject: Re: Press Release Draft - 2016-02-09 Cumulative Update
Date: 2017-02-08 20:51:10
Message-ID: 20170208205110.elkdhlszxlfnrdjh@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2017-02-08 20:52:07 Re: Patch: Avoid precision error in to_timestamp().
Previous Message Tobias Bussmann 2017-02-08 20:15:53 Re: Press Release Draft - 2016-02-09 Cumulative Update