[SOLVED] How to perform a list of DROP INDEX CONCURRENTLY in Postgresql?

How to perform a list of DROP INDEX CONCURRENTLY in Postgresql?

In PostgreSql 9.2, when I try to use DROP INDEX CONCURRENTLY independently in "PGAdmin-SQL Panel", the single query is executing fine. But when I use the same query inside a Postgresql function or from Java JDBC, I am getting the error - "DROP INDEX CONCURRENTLY cannot be called in a function or transaction block". I have a requirement where I'd like to DROP a set of indices even when the DB is being accessed by other read/write operations without conflicting with one another. Can you suggest some way to drop a list of indices in such a scenario ?

Solution

you cannot use drop index concurrently in function or inline code or in transaction. if you cannot do it in JDBC app, you can should tune on autocommit first. OR you can use SQL generate some drop index's sql LIKE:

select 'drop index concurrently "'||schemaname||'"."'||indexname||'";' from pg_indexes where schemaname='public';
 drop index concurrently "public"."idx_tbl_id";
 drop index concurrently "public"."tbl1_pkey";
 drop index concurrently "public"."tbl_join_1_pkey";
 drop index concurrently "public"."tbl_join_2_pkey";
 drop index concurrently "public"."tbl_join_3_pkey";
 drop index concurrently "public"."tbl_join_4_pkey";
 drop index concurrently "public"."tbl_join_5_pkey";
 drop index concurrently "public"."tbl_join_6_pkey";
 drop index concurrently "public"."tbl_join_7_pkey";
 drop index concurrently "public"."tbl_join_8_pkey";
 drop index concurrently "public"."tbl_join_9_pkey";
 drop index concurrently "public"."pgbench_branches_pkey";
 drop index concurrently "public"."pgbench_tellers_pkey";
 drop index concurrently "public"."pgbench_accounts_pkey";
 drop index concurrently "public"."tbl_userinfo_pkey";
 drop index concurrently "public"."i_test_pkey";

AND then do it in PGAdmin window. NOT in BEGIN; END; BLOCKs.