Am 08.02.2017 um 20:17 schrieb Alvaro Herrera <alvhe...@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 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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply via email to