Search code examples
sqlpostgresqlsql-deletepartitioning

How to find the queries that is blocking another query?

I am trying to drop an empty table drop table temp; the query is running forever.

So, I tried to find other queries that could block the current query. Here is what I tried:

SELECT *
  FROM pg_locks l
  JOIN pg_class t ON l.relation = t.oid AND t.relkind = 'r'
 WHERE t.relname = 'temp';

This query returns with zero results

SELECT database, gid FROM pg_prepared_xacts;

This does not give any result as well.

The table is a partitioned table, the partitions attached to the table has been deleted successfully already.

I am not even able to get result for \d temp.

What has happened? How can I get this fixed?

This does not happen when I tried the same in local. Works perfectly fine.

Solution

  • Before running DROP TABLE, execute

    SELECT pg_backend_pid();

    That will tell you the backend process ID. Then run DROP TABLE in the same database session.

    Then, when DROP TABLE is hanging, start a new session and run

    SELECT pg_blocking_pids(<backend PID>);

    Then you know which sessions are blocking you, and can kill them or take less disruptive measures for getting rid of them.