Give guidelines for dropping a DB table

Problem

When we want to drop a table that has foreign keys to a high-traffic table (for example: projects), the DROP TABLE statement times out.

To demonstrate the problem, you need two psql sessions:

Session 1:

CREATE TABLE omg (
    project_id int NOT NULL,
    FOREIGN KEY (project_id) REFERENCES projects(id)
);

Session 2:

BEGIN;
LOCK TABLE projects in exclusive mode;

Now go back to Session 1:

drop table omg;

The statement is blocked.

Ideas:

  • As Andreas mentioned we might need to disable the statement timeout while dropping the table.
  • Truncate but keep the table. Could be reused for other purposes or could be dropped later.

Tasks:

Edited by Toon Claes