Re: [HACKERS] Press Release Draft - 2016-02-09 Cumulative Update

Tobias Bussmann wrote:
> Am 07.02.2017 um 18:44 schrieb Alvaro Herrera <alvherre@2ndquadrant.com>:
> >  80     CREATE INDEX CONCURRENTLY bad_index_name ON table_name (column_name); /* replace names with your original
indexdefinition */
 
> 
> I was thinking if we could replace that "replace names with your original index definition" with something more fancy
usingpg_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
withsome 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