PostgreSQL stores foreign key constraints as system triggers, and anything that stops those triggers from firing — or lets a user-defined trigger interfere with them — can leave orphaned rows behind. A simple foreign key creates four such triggers:
- row-level
AFTER INSERTandAFTER UPDATEtriggers on the referencing table, checking that the new row points at a row that exists in the referenced table; - row-level
AFTER UPDATEandAFTER DELETEtriggers on the referenced table, checking that the modification does not orphan rows in the referencing table (or, for a cascading constraint, deleting or updating the potential orphans).
These triggers are written in C and deliberately ignore the ordinary MVCC rules, which is what keeps them free of race conditions. The same is not true of constraints implemented with user-defined triggers: those always suffer from race conditions unless you use the SERIALIZABLE isolation level or serialize the operations with locks. Deferrable primary key and unique constraints are also implemented with system triggers, so every technique below can break those constraints too.
Inspecting the hidden triggers
Clients such as psql do not display system triggers, but they are visible in the catalog table pg_trigger:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
CREATE TABLE parent ( pid integer PRIMARY KEY ); CREATE TABLE child ( cid integer PRIMARY KEY, pid integer REFERENCES parent ON DELETE CASCADE ); SELECT tgname AS trigger_name, tgisinternal AS internal, CASE WHEN tgtype & 2 = 2 THEN 'BEFORE ' ELSE 'AFTER ' END || CASE tgtype & 60 WHEN 4 THEN 'INSERT' WHEN 8 THEN 'DELETE' WHEN 16 THEN 'UPDATE' ELSE '?' END || ' FOR EACH ' || CASE WHEN tgtype & 1 = 1 THEN 'ROW' ELSE 'STATEMENT' END AS firing_conditions FROM pg_trigger WHERE tgrelid = 'parent'::regclass; trigger_name │ internal │ firing_conditions ══════════════════════════════╪══════════╪═══════════════════════════ RI_ConstraintTrigger_a_44600 │ t │ AFTER DELETE FOR EACH ROW RI_ConstraintTrigger_a_44601 │ t │ AFTER UPDATE FOR EACH ROW (2 rows) SELECT tgname AS trigger_name, tgisinternal AS internal, CASE WHEN tgtype & 2 = 2 THEN 'BEFORE ' ELSE 'AFTER ' END || CASE tgtype & 60 WHEN 4 THEN 'INSERT' WHEN 8 THEN 'DELETE' WHEN 16 THEN 'UPDATE' ELSE '?' END || ' FOR EACH ' || CASE WHEN tgtype & 1 = 1 THEN 'ROW' ELSE 'STATEMENT' END AS firing_conditions FROM pg_trigger WHERE tgrelid = 'child'::regclass; trigger_name │ internal │ firing_conditions ══════════════════════════════╪══════════╪═══════════════════════════ RI_ConstraintTrigger_c_44602 │ t │ AFTER INSERT FOR EACH ROW RI_ConstraintTrigger_c_44603 │ t │ AFTER UPDATE FOR EACH ROW (2 rows) |
The obvious routes first
Corruption and careless restores are the uninteresting cases: a database that suffers from data corruption, a bad backup restored, or catalog tables edited directly will naturally contain inconsistencies. The rest of this article deals with the ways you can break foreign keys without doing anything obviously wrong.
session_replication_role
Logical replication replays changes on the subscriber in an order that may conflict with the subscriber's foreign key constraints — for instance when a single statement on the publisher touches both tables, or when a foreign key check is deferred to the end of the transaction. PostgreSQL's answer to that is the parameter session_replication_role.
Set to replica, it suppresses trigger firing altogether, foreign key triggers included. A trigger can be marked to fire anyway with ALTER TABLE ... ENABLE [ REPLICA | ALWAYS ] TRIGGER ..., but that is neither the default nor a sensible setting for foreign key triggers. Because switching this parameter is such an easy way to violate constraints, only superusers may change it, or users to whom a superuser has granted the right with GRANT SET ON PARAMETER session_replication_role TO.
|
1 2 3 4 |
SET session_replication_role = replica; INSERT INTO child (cid, pid) VALUES (1, 666); INSERT 0 1 |
Disabling triggers by hand
ALTER TABLE ... DISABLE TRIGGER ... turns a trigger off. It accepts a single trigger name, the keyword USER for all user-defined triggers, or ALL for every trigger on the table — including the foreign key triggers. Only a superuser can disable these system triggers.
|
1 2 3 4 |
ALTER TABLE child DISABLE TRIGGER ALL; INSERT INTO child (cid, pid) VALUES (2, 666); INSERT 0 1 |
Interference from user-defined triggers
A cascading foreign key, declared with ON [ UPDATE | DELETE ] [ CASCADE | SET NULL | SET DEFAULT ], causes a modification of the referenced table to propagate an UPDATE or DELETE to the referencing table, restoring consistency. That cascaded statement is ordinary SQL as far as the referencing table is concerned, so it can fire other triggers defined on that table. A user-defined BEFORE trigger is free to modify the new row, or to cancel the operation by returning NULL — and that can break the foreign key:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
INSERT INTO parent (pid) VALUES (1); INSERT 0 1 INSERT INTO child(cid, pid) VALUES (42, 1); INSERT 0 1 CREATE FUNCTION breakage() RETURNS trigger LANGUAGE plpgsql AS $$BEGIN RETURN NULL; END;$$; CREATE TRIGGER breakage BEFORE DELETE ON child FOR EACH ROW EXECUTE FUNCTION breakage(); DELETE FROM parent WHERE pid = 1; DELETE 1 TABLE child; cid │ pid ═════╪═════ 42 │ 1 (1 row) |
This failure mode is the nastiest of the three, because nothing about it is obvious, and because breaking a foreign key this way requires no superuser privileges. The PostgreSQL project has stated that it does not consider the behavior a bug.
Only superusers can disable foreign key checking outright, and they are expected to know what they are doing. But every user who can create a
BEFOREtrigger on a table carrying a cascading foreign key constraint can break that constraint, so those triggers deserve close scrutiny.
