One of the fundamental requirements for a relational database is that the transaction system always maintains consistency. That means that database constraints must always be satisfied, even in the face of concurrent data modifications. PostgreSQL certainly strives to live up to this requirement. Still, you can end up with broken foreign key constraints if you perform certain dangerous operations. This article shows how broken foreign keys can come about, so that you know what you should avoid in order to maintain consistency.
How does PostgreSQL implement foreign keys?
To understand how you can end up with broken foreign keys, it is crucial to understand PostgreSQL’s implementation.
PostgreSQL implements foreign keys with system triggers. A simple foreign key constraint will create four triggers:
AFTER UPDATEtriggers on the referencing table that verify that the new row points to an existing row in the referenced table
AFTER DELETEtriggers on the referenced table that verify that the
DELETEdo not create any orphaned rows in the referencing table (or
UPDATEthe potential orphans in the case of a cascading foreign key)
These system triggers are written in C and ignore the ordinary MVCC rules to avoid race conditions. If you try to implement constraints with user-defined triggers, these triggers always suffer from race conditions unless you use the
SERIALIZABLE isolation level or serialize operations with locks.
Note that PostgreSQL also implements deferrable primary key and unique constraints with system triggers, so the methods shown in this article can break such constraints as well.
Examining foreign key triggers
PostgreSQL clients like
psql don’t show these system triggers, but you can see them with a query on the catalog table
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)
Trivial causes of broken foreign keys
I wrote about causes of PostgreSQL data corruption before. If your database suffers from data corruption, it is not surprising if you have some broken foreign keys. If you restore a bad backup or directly modify the catalog tables, you have to expect inconsistencies.
But there are some ways to end up with broken foreign keys even if you didn’t so anything obviously wrong. These ways will be the theme of the rest of this article.
Broken foreign keys caused by
When logical replication replays data modifications on the subscriber, the order of these changes might conflict with foreign key constraints on the subscriber. This can happen if you modify both tables in a single statement on the publisher, or if you defer a foreign key check to the end of the transaction. To deal with this problem, PostgreSQL has the parameter
session_replication_role is set to
replica, triggers won’t fire, including the triggers that implement foreign keys. You can use
ALTER TABLE ... ENABLE [ REPLICA | ALWAYS ] TRIGGER ... to define triggers that will fire when
session_replication_role is set to
replica. But that is neither the default nor a commendable setting for foreign key triggers.
It is simple to break foreign key constraints by using
session_replication_role. Therefore, you can only change this parameter if you are a superuser, or if a superuser has run
GRANT SET ON PARAMETER session_replication_role TO your user.
SET session_replication_role = replica; INSERT INTO child (cid, pid) VALUES (1, 666); INSERT 0 1
Broken foreign keys caused by disabled triggers
You can disable a trigger in PostgreSQL by using
ALTER TABLE ... DISABLE TRIGGER .... You can either specify an individual trigger name, use the keyword
USER to disable all user-defined triggers, or use
ALL to disable all triggers on the table, including foreign key triggers. You can only disable these system triggers if you are a superuser.
ALTER TABLE child DISABLE TRIGGER ALL; INSERT INTO child (cid, pid) VALUES (2, 666); INSERT 0 1
Broken foreign keys caused by user-defined triggers
You can define a cascading foreign key with
ON [ UPDATE | DELETE ] [ CASCADE | SET NULL | SET DEFAULT ]. Then an
DELETE on the referenced table leads to an
DELETE on the referencing table. This cascaded data modification on the referencing table restores the consistency of the foreign key.
DELETE that the system trigger executes on the referencing table can cause other triggers on that table to fire. If there is a user-defined
BEFORE trigger on the referencing table, that trigger can modify the new row or cancel the operation by returning NULL. If you don’t pay attention, that can easily break your foreign key:
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 way to break foreign key constraints is particularly nasty, since the problem is not obvious. Moreover, you don’t need superuser privileges to break a foreign key that way. However, the PostgreSQL project does not consider that behavior a bug.
There are surprisingly many ways to end up with broken foreign keys in PostgreSQL. If you don’t know that PostgreSQL implements foreign keys with system triggers that execute SQL statements, you might get a nasty surprise if you disable foreign keys. Fortunately, only superusers can disable foreign keys, and superusers should know what they are doing. However, normal users can also break foreign keys with ill-defined triggers. So watch out if you define
BEFORE triggers on tables with a cascading foreign key constraint!