Tobias Bussmann wrote: > Am 07.02.2017 um 18:44 schrieb Alvaro Herrera <alvhe...@2ndquadrant.com>: > > 80 CREATE INDEX CONCURRENTLY bad_index_name ON table_name > > (column_name); /* replace names with your original index definition */ > > I was thinking if we could replace that "replace names with your original > index definition" with something more fancy using pg_get_indexdef in that > recipe. I ended up with quite a "REINDEX CONCURRENTLY" monster: > > \set index_name 'my_bad_index' > \set table_schema 'public' > SELECT :'index_name'||'_'||left(md5(random()::text), 5) AS index_name_tmp > \gset > SELECT > replace(replace(pg_get_indexdef((quote_ident(:'table_schema')||'.'||quote_ident(:'index_name'))::regclass), > 'INDEX '||quote_ident(:'index_name'), 'INDEX > '||quote_ident(:'index_name_tmp')), 'CREATE INDEX', 'CREATE INDEX > CONCURRENTLY') \gexec > DROP INDEX CONCURRENTLY :"table_schema".:"index_name"; > ALTER INDEX :"table_schema".:"index_name_tmp" RENAME TO :"index_name"; > > Probably not useable as a recipe in such an announcement but it was fun to > build and to see what is actually possible with some psql magic :)
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. 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). Probably too late to add the link to the press release now, since it's already out as "final". -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
