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:
-
New page added to DB guidelines -
@skarbek started a discussion: (+5 comments)
Edited by Toon Claes