PostgreSQL: DROP TABLE is not working

Problem: I want to drop my table, but I am not able to drop the table while I tried the query "DROP TABLE MyTable CASCADE;".

Solution: "DROP TABLE MyTable CASCADE;" this query is responsible to drop the table in PostgresSQL but it is not working it means we have to do some analysis step by step because there might be the other session using MyTable parallel.

1- Run the following query to obtain Access Exclusive lock

SELECT pid, relname FROM pg_locks l JOIN pg_class t ON l.relation = t.oid AND t.relkind = 'r' WHERE t.relname = 'MyTable';

Note: here relname is the table name.

2- Once you run the above query you will found the list of pid, now in the second step we will kill the all pid by using the following query.

KILL 1234

Killing pid one by one is a lengthy task. You can pipe it all together, use the following command.

psql -c "SELECT pid FROM pg_locks l     JOIN pg_class t ON l.relation = t.oid AND t.relkind = 'r'     WHERE t.relname = 'MyTable';" | tail -n +3 | head -n -2 | xargs kill

       

3- Now, you can drop your table.

DROP TABLE MyTable CASCADE;