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-21e3fdb5d9057921@postgresql.org