drop table rule

[froque]

Dropping a table with foreign keys can lead to a deadlock situation.

-- prepare tables for test
DROP TABLE IF EXISTS foo, bar, baz;

CREATE TABLE IF NOT EXISTS foo (
	id int PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS bar (
	id int PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS baz (
	id int PRIMARY KEY,
	
	foo_id int REFERENCES foo(id),
	bar_id int REFERENCES bar(id)
);

connection 1

-- connection 1
BEGIN TRANSACTION;

INSERT INTO foo VALUES(1);

-- execute DROP TABLE IF EXISTS baz; in connection 2

INSERT INTO bar VALUES(1);

COMMIT;

connection 2

-- connection 2
BEGIN TRANSACTION;

-- after INSERT INTO foo VALUES(1), before INSERT INTO bar VALUES(1);
DROP TABLE IF EXISTS baz;

COMMIT;

fails with

SQL Error [40P01]: ERROR: deadlock detected
  Detail: Process 462540 waits for RowExclusiveLock on relation 187503 of database 13797; blocked by process 462507.
Process 462507 waits for AccessExclusiveLock on relation 187498 of database 13797; blocked by process 462540.
  Hint: See server log for query details.
  Position: 14

Error position: line: 32 pos: 13

[froque]

correct solution 1:

-- connection 2
BEGIN TRANSACTION;
ALTER TABLE IF EXISTS baz DROP CONSTRAINT IF EXISTS baz_foo_id_fkey;
COMMIT;

BEGIN TRANSACTION;
ALTER TABLE IF EXISTS baz DROP CONSTRAINT IF EXISTS baz_bar_id_fkey;
COMMIT;

BEGIN TRANSACTION;
DROP TABLE IF EXISTS baz;
COMMIT;

correct solution 2:

BEGIN TRANSACTION;
ALTER TABLE IF EXISTS baz DROP COLUMN IF EXISTS foo_id;
COMMIT;

BEGIN TRANSACTION;
ALTER TABLE IF EXISTS baz DROP COLUMN IF EXISTS bar_id;
COMMIT;

BEGIN TRANSACTION;
DROP TABLE IF EXISTS baz;
COMMIT;