Hello. There's an issue brought up in the -bugs list [1]. Since triggers are deactivated on a subscriber by default, foreign key constraints don't fire for replicated changes. The docs state this is done to prevent repetitive data propagation between tables on subscribers. But foreign key triggers don't contribute to this issue.
My understanding is that constraint triggers, including ones created using the "CREATE CONSTRAINT TRIGGER" command, aren't spposed to alter data. If this holds true, I propose that we modify the function CreateTrigger() to make constraint triggers enabled on subscribers as attached. The function CreateTrigger() can choose the value for the parameter "trigger_fires_when" of CreateTriggerFireingOn() based on whether constraintOid is valid or not. What do you think about this change? A reproducer follows. The last UPDATE successfully propagates to the subscriber, removing a row that couldn't be locally removed on the subscriber due to the referencial constraint. Publisher: CREATE TABLE t (a int not null, b bool not null); ALTER TABLE t REPLICA IDENTITY FULL; INSERT INTO t VALUES (0, true), (1, true), (2, true); CREATE PUBLICATION p1 FOR TABLE t WHERE (b IS true); Subscriber: CREATE TABLE t (a int primary key, b bool); CREATE TABLE t1 (a int references t(a) ON UPDATE CASCADE); CREATE SUBSCRIPTION s1 CONNECTION 'host=/tmp port=5432' PUBLICATION p1; SELECT pg_sleep(0.5); INSERT INTO t1 VALUES (2); == trigger correctly fires Subscriber: DELETE FROM t WHERE a = 2; > ERROR: update or delete on table "t" violates foreign key constraint > "t1_a_fkey" on table "t1" > DETAIL: Key (a)=(2) is still referenced from table "t1". == trigger doesn't fire Publisher: UPDATE t SET b = false WHERE a = 2; Subscriber: SELECT * FROM t; -- (2 doesn't exist) regards. [1]: https://www.postgresql.org/message-id/18019-21e3fdb5d9057...@postgresql.org
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c index 52177759ab..c0eb8ea203 100644 --- a/src/backend/commands/trigger.c +++ b/src/backend/commands/trigger.c @@ -171,7 +171,10 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString, CreateTriggerFiringOn(stmt, queryString, relOid, refRelOid, constraintOid, indexOid, funcoid, parentTriggerOid, whenClause, isInternal, - in_partition, TRIGGER_FIRES_ON_ORIGIN); + in_partition, + OidIsValid(constraintOid) ? + TRIGGER_FIRES_ALWAYS: + TRIGGER_FIRES_ON_ORIGIN); } /*
