Postgres: DELETE hangs on a table with a self-referential foreign key

Question

I am trying to delete rows from a table that has a self-referential foreign key:

CREATE TABLE items (
    id        SERIAL  PRIMARY KEY,
    parent_id INTEGER      NOT NULL,
    FOREIGN KEY (parent_id) REFERENCES refers(id)
);

Now when I simply do:

DELETE FROM items;

the query freezes forever. If I kill it with Ctrl-C, it says

ERROR: canceling statement due to user request CONTEXT: SQL statement "UPDATE ONLY "public"."items" SET "parent" = NULL WHERE $1 OPERATOR(pg_catalog.=) "parent""

How to delete rows from such a table?

kolypto · Accepted Answer · 2020-06-17 22:18:03Z

3

According to this answer, foreign keys in Postgres are implemented as triggers, so you can temporarily disable foreign key checks like this:

ALTER TABLE items DISABLE TRIGGER ALL; 
DELETE FROM items; 
ALTER TABLE items ENABLE TRIGGER ALL;

answered Jun 17, 2020 at 22:18

kolypto

36.3k20 gold badges118 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Belayer Over a year ago

Please post a minimal reproducible example for debug help. By the way your table Items does not contain a self-reference , The column foreign key parent_id references the table refer not item. Also if you actually want a self reference in a table be extremely cautious of not null for the child column. That forces an entry to be its own parent. IMHO not a good design.

kolypto Over a year ago

there's no way to post a minimal example. The freeze was not a deadlock; it was a very very long operation)

Collectives™ on Stack Overflow

Postgres: DELETE hangs on a table with a self-referential foreign key

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related