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

Am 08.02.2017 um 20:17 schrieb Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>:
> Note that this is likely to fail if the original index name is close to
> the 63 chars limit. Perhaps it's enough to add substring() when
> computing index_name_tmp. (You could just not use :'index_name' there
> and rely on the random md5 only, actually). Watch out for UNIQUE too.

thank you for your valuable input! Here is a version that should take both into account - the query also could be simplified a bit:

\set index_name 'my_bad_index'
\set table_schema 'public'
SELECT 'tmp_'||md5(random()::text) AS index_name_tmp \gset
SELECT replace(pg_get_indexdef((quote_ident(:'table_schema')||'.'||quote_ident(:'index_name'))::regclass), ' '||quote_ident(:'index_name')||' ON', ' CONCURRENTLY '||:'index_name_tmp'||' ON') \gexec
DROP INDEX CONCURRENTLY :"table_schema".:"index_name";
ALTER INDEX :"table_schema".:"index_name_tmp" RENAME TO :"index_name";

> FWIW for previous problems we've documented them in wiki pages along
> with suggested solutions, and added a link to that wiki page in the
> announce. Perhaps one thing to do is create a wiki page for this one
> too (not volunteering myself).

I'm not even remotely into the details of the CIC issue, so I'm not the right one to create a page on that topic. 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. I always have a bit of mixed feelings with these kind of string manipulations on dynamic SQL.

Best,
Tobias