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

Am 08.02.2017 um 20:17 schrieb Alvaro Herrera <alvherre@2ndquadrant.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
simplifieda 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
couldput this snippet as a "REINDEX CONCURRENTLY" workaround into the Administrative Snippets category of the wiki, if
thereare no further objections about the way it works. I always have a bit of mixed feelings with these kind of string
manipulationson dynamic SQL. 

Best,
Tobias