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