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
