Tobias Bussmann wrote:
> Am 07.02.2017 um 18:44 schrieb Alvaro Herrera <>:
> >  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
> 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 
> 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      
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Sent via pgsql-hackers mailing list (
To make changes to your subscription:

Reply via email to