Postgresql logical replication and foreign key constraints

Are you using logical replication? Did you know that, by default, triggers In Postgresql are not fired when a table is updated by logical replication? If you didn’t, you are not alone. This feature is implemented as follows: before updating a table, logical replication sets session_replication_role to “replica”. When the value is “replica”, triggers are not fired unless ALTER TABLE … ENABLE REPLICA TRIGGER or ALTER TABLE … ENABLE ALWAYS TRIGGER has been applied to the trigger.

Logical replication does not respect foreign key constraints

Postgresql implements foreign key constraints using triggers. So does that mean that foreign key constraints are not respected when updates are performed by logical replication? Yes it does! In the following example replicated is a table that is updated via logical replication (for simplicity I won’t use logical replication, instead I’ll use the session_replication_role setting of “replica” to fake it), while constrained is a “local” table with a foreign key reference to replicated. By “local” I mean that it is not updated via logical replication.

postgres=# CREATE TABLE replicated(id int PRIMARY KEY);
CREATE TABLE
postgres=# CREATE TABLE constrained(ref int PRIMARY KEY);
CREATE TABLE
postgres=# ALTER TABLE constrained ADD FOREIGN KEY(ref) REFERENCES replicated(id);
ALTER TABLE
postgres=# INSERT INTO replicated VALUES(1), (2), (3);
INSERT 0 3
postgres=# INSERT INTO constrained VALUES(1), (2), (3);
INSERT 0 3

Trying to delete from replicated fails as expected, because the key is used by constrained:

postgres=# DELETE FROM replicated WHERE id=1;
ERROR:  update or delete on table "replicated" violates foreign key constraint "constrained_ref_fkey" on table "constrained"
DETAIL:  Key (id)=(1) is still referenced from table "constrained".

But what if the delete was performed as part of logical replication? We can pretend to be doing logical replication by setting session_replication_role to replica (if you aren’t an admin user then this will likely fail with “permission denied”):

postgres=# SET session_replication_role=replica;
SET
postgres=# DELETE FROM replicated WHERE id=1;
DELETE 1

Yup, the delete went through and the foreign key constraint on constrained has been violated:

postgres=# SELECT * FROM replicated WHERE id=1;
 id 
----
(0 rows)
postgres=# SELECT * FROM constrained WHERE ref=1;
 ref 
-----
   1
(1 row)

The internal triggers used to implement foreign key constraints

If this is not what you want, and presumably you wouldn’t be here if it was, then what you need to do is to find the names of the internal triggers used to implement the foreign key constraint, and use ALTER TABLE … ENABLE ALWAYS TRIGGER to make sure the trigger fires both when logical replication is used and when it isn’t. This is a hack. Maybe the postgresql devs will add something more convenient one day, so if you are reading this long after 2022 then I suggest you first search the postgresql docs for something better.

postgres=# SELECT tgrelid::regclass, tgconstrrelid::regclass, tgname, tgenabled FROM pg_trigger WHERE 'replicated'::regclass IN (tgrelid, tgconstrrelid) AND 'constrained'::regclass IN (tgrelid, tgconstrrelid);
   tgrelid   | tgconstrrelid |            tgname            | tgenabled 
-------------+---------------+------------------------------+-----------
 replicated  | constrained   | RI_ConstraintTrigger_a_21281 | O
 replicated  | constrained   | RI_ConstraintTrigger_a_21282 | O
 constrained | replicated    | RI_ConstraintTrigger_c_21283 | O
 constrained | replicated    | RI_ConstraintTrigger_c_21284 | O
(4 rows)

Four internal triggers have been created to implement one foreign key constraint. The first two are triggers on the replicated table, and the last two are on the constrained table. The tgconstrrelid column corresponds to referenced_table_name in CREATE TRIGGER. These triggers only fire when session_replication_role is origin (the default), as shown by the tgenabled column being ‘O’. Why are there four triggers, and why are some of the triggers on constrained and not all on replicated? If you insert a new row into constrained, or update a row, then a check is performed that the new key exists in the replicated table. This is what the two “check” (c) triggers on constrained do. Why two? One takes care of inserts, the other takes care of updates. If you delete a row in replicated that is used by constrained, then the delete needs to fail (or be cascaded if we had defined the constraint differently), and similarly for updates. This is what the two “action” (a) triggers on replicated do. One takes care of deletes, the other takes care of updates.

Ensuring foreign key constraints are preserved

In our example only replicated is updated by logical replication, while constrained is a “local” table that makes use of replicated and is not updated by logical replication. Thus we need to ensure that the first two triggers always fire, both for ordinary updates and for logical replication updates, but there is no need to do anything special for the last two triggers.

postgres=# ALTER TABLE replicated ENABLE ALWAYS TRIGGER "RI_ConstraintTrigger_a_21281";
ALTER TABLE
postgres=# ALTER TABLE replicated ENABLE ALWAYS TRIGGER "RI_ConstraintTrigger_a_21282";
ALTER TABLE

We can see in pg_trigger that the trigger types changed to ‘A’:

postgres=# SELECT tgrelid::regclass, tgconstrrelid::regclass, tgname, tgenabled FROM pg_trigger WHERE 'replicated'::regclass IN (tgrelid, tgconstrrelid) AND 'constrained'::regclass IN (tgrelid, tgconstrrelid);
   tgrelid   | tgconstrrelid |            tgname            | tgenabled 
-------------+---------------+------------------------------+-----------
 constrained | replicated    | RI_ConstraintTrigger_c_21283 | O
 constrained | replicated    | RI_ConstraintTrigger_c_21284 | O
 replicated  | constrained   | RI_ConstraintTrigger_a_21281 | A
 replicated  | constrained   | RI_ConstraintTrigger_a_21282 | A
(4 rows)

But did it work?

postgres=# SET session_replication_role=replica;
SET
postgres=# DELETE FROM replicated WHERE id=2;
ERROR:  update or delete on table "replicated" violates foreign key constraint "constrained_ref_fkey" on table "constrained"
DETAIL:  Key (id)=(2) is still referenced from table "constrained".

It did! Please note that pg_dump does not preserve these settings, so you will need to redo the ENABLE ALWAYS TRIGGER commands by hand after a dump + restore.