From: | Tobias Bussmann <t(dot)bussmann(at)gmx(dot)net> |
---|---|
To: | Pg Hackers <pgsql-hackers(at)postgresql(dot)org> |
Cc: | "Jonathan S(dot) Katz" <jkatz(at)postgresql(dot)org>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> |
Subject: | Re: Press Release Draft - 2016-02-09 Cumulative Update |
Date: | 2017-02-08 19:08:07 |
Message-ID: | 1AC2870E-4FF6-4B02-8B72-05B33041AAFF@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Am 07.02.2017 um 18:44 schrieb Alvaro Herrera <alvherre(at)2ndquadrant(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2017-02-08 19:17:38 | Re: Press Release Draft - 2016-02-09 Cumulative Update |
Previous Message | Andres Freund | 2017-02-08 19:01:03 | Re: pg_bsd_indent: implement -lps ("leave preprocessor space") |