Postgres drop table (which has no rows) has taken > 20 minutes and isn't finished yet

Question

Before executing the drop table MY_TABLE, I checked count(*) from MY_TABLE and verified that the row count is 0.

There exist no foreign key constraints on other tables which reference MY_TABLE. Yet the drop table query has been running for 23 minutes now.

Can someone suggest possible causes?

Answer 1

If you deleted the data in the table yourself, then PostgreSQL might be waiting for you to COMMIT; your DELETE FROM <tablename>; statement. This depends on whether or not AUTOCOMMIT is turned ON or OFF in your PostgreSQL client.

For PGAdmin the default is:

By default auto commit will be enabled in pgadmin.

However, you might have inadvertently turned this off in your client.

Just enter the following command in the session that deleted the data:

COMMIT;

Query Running Statement

Another possible solution is to have a look at the pg_stat_activity table to find out who might be blocking your drop table... command.

SELECT psa.pid,
       psa.usename,
       psa.application_name,
       psa.wait_event,
       psa.wait_event_type,
       psa.state,
       pdb.datname,
       pl.mode,
       pl.locktype,
       pc.relname,
       pc.reltype
FROM pg_locks pl
    FULL OUTER JOIN pg_stat_activity psa
        ON pl.pid = psa.pid
    LEFT JOIN pg_class pc
        ON pl.objid = pc.oid
    LEFT JOIN pg_database pdb
        ON pl.database = pdb.oid
WHERE pdb.datname = '<the name of your database>';

The Polite Solution

If you can determine that another session is blocking your delete then you can cancel the statement with:

select * from pg_cancel_backend(<pid_from_previous_select>);

This might solve your problem. You shouldn't terminate any of the PostgreSQL's own sessions.

The Rude Solution

If you can determine that another session is blocking your delete and you couldn't cancel the statement, then you can terminate the session with the following command:

select * from pg_terminate_backend(<pid_from_previous_select>);