1

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 ?

Ponmudi VN
  • 1,493
  • 1
  • 18
  • 22
  • You can't do it within a function. You may be able to over JDBC with autocommit turned on, depends on details of how the driver implements autocommit. – Craig Ringer Apr 11 '14 at 05:37
  • @CraigRinger : Thank you. The auto-commit is working fine for both DROP and CREATE. But CREATE INDEX CONCURRENTLY is giving priority for table select/ update operations and waiting for a long time. I'd like to know if there is a way to CREATE INDEX while the table is being accessed for CRUD operations. – Ponmudi VN Apr 15 '14 at 12:33
  • `CREATE INDEX CONCURRENTLY` allows insert/update to proceed while it's working. It does not *stop* while they're happening, or restart. It will eventually complete, it just takes longer (sometimes much longer) than the non-concurrent one does. – Craig Ringer Apr 15 '14 at 12:41
  • @CraigRinger : That we understood. But what we'd like to know is whether there is a way to complete the CREATE INDEX before the CRUD operation is over. We are using hibernate for CRUD operations. So Hibernate will create an initial connection and close it only when Application Server stops. But we want to CREATE INDEX when the Hibernate connection is open. – Ponmudi VN Apr 15 '14 at 12:51
  • So long as Hibernate *commits the transaction*, it doesn't matter if the *connection* remains open. If it doesn't commit the transaction, i.e. there are " in transaction" entries in `pg_stat_activity`, you've got bigger problems. – Craig Ringer Apr 15 '14 at 12:53
  • @CraigRinger : Thank you Craig ! It works fine when I set "hibernate.connection.autocommit" to true in hibernate.cfg.xml. But here comes the next question : http://stackoverflow.com/questions/23100888/why-is-hibernate-connection-autocommit-true-not-recommended-in-hibernate – Ponmudi VN Apr 16 '14 at 05:41

1 Answers1

4

you cann't use drop index concurrently in function or inline code or in transaction. if you cann't do it in JDBC app, you can shold 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.

digoal.zhou
  • 434
  • 2
  • 3