Table of Contents
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.
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 INSERT and AFTER UPDATE triggers on the referencing table that verify that the new row points to an existing row in the referenced tableAFTER UPDATE and AFTER DELETE triggers on the referenced table that verify that the UPDATE or DELETE do not create any orphaned rows in the referencing table (or DELETE or UPDATE the 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.
PostgreSQL clients like psql don't show these system triggers, but you can see them with a query on 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) |
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.
session_replication_roleWhen 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.
If 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.
|
1 2 3 4 |
SET session_replication_role = replica; INSERT INTO child (cid, pid) VALUES (1, 666); INSERT 0 1 |
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.
|
1 2 3 4 |
ALTER TABLE child DISABLE TRIGGER ALL; INSERT INTO child (cid, pid) VALUES (2, 666); INSERT 0 1 |
You can define a cascading foreign key with ON [ UPDATE | DELETE ] [ CASCADE | SET NULL | SET DEFAULT ]. Then an UPDATE or DELETE on the referenced table leads to an UPDATE or DELETE on the referencing table. This cascaded data modification on the referencing table restores the consistency of the foreign key.
The UPDATE or 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:
|
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 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!
+43 (0) 2622 93022-0
office@cybertec.at