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 :) Tobias -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
