How to drop an index in PostgreSQL 9.6?

Question

I have tried below commands as postgres(super) user and the owner user:

Drop index <index_name>;
Drop index <index_name> cascade;
Drop index concurrently <index_name>;

The query does not give any error but is in the hung state forever till cancelled.

Your statements are ok in general. You have probably other tasks running in your database which preventing the completion. — clemens
– clemens, Commented Oct 17, 2017 at 5:34
Yes, that can be the case. Is there any way to find out which query/session is using the index? — pricha
– pricha, Commented Oct 17, 2017 at 5:40

clemens · Accepted Answer · 2017-10-17 11:23:17Z

5

Your statements are ok in general. There are other processes having locks on the indices preventing the drop statements from finishing. You can see the active statements with

SELECT * FROM pg_stat_activity;

You should set log_statement appropriately if necessary.

Edit: pg_locks will give you information about specific locks:

SELECT a.datname,
         c.relname,
         l.transactionid,
         l.mode,
         l.GRANTED,
         a.usename,
         a.query, 
         a.query_start,
         age(now(), a.query_start) AS "age", 
         a.pid 
    FROM  pg_stat_activity a
     JOIN pg_locks         l ON l.pid = a.pid
     JOIN pg_class         c ON c.oid = l.relation
    ORDER BY a.query_start;

edited Oct 17, 2017 at 11:23

answered Oct 17, 2017 at 5:49

clemens

17.9k12 gold badges52 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

pricha Over a year ago

Yes, it worked. After closing the dependent sessions, I was able to drop the index.

clemens Over a year ago

@CraigRinger: You are right. I've extended my answer.

Collectives™ on Stack Overflow

How to drop an index in PostgreSQL 9.6?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related