Обсуждение: Do we want to enable foreign key constraints on subscriber?

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

On Wed, Jul 19, 2023 at 12:21 PM Kyotaro Horiguchi
<horikyota.ntt@gmail.com> wrote:
>
> 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.
>

Right and recent reports indicate that this does cause inconvenience for users.

> My understanding is that constraint triggers, including ones created
> using the "CREATE CONSTRAINT TRIGGER" command, aren't spposed to alter
> data.
>

I also think so. You need to update the docs for this.

Peter E., do you remember if there is any specific problem in enabling
such triggers by default for apply side? The only thing that I can
think of is that the current behavior keeps the trigger-firing rules
the same for all kinds of triggers which has merits but OTOH it causes
inconvenience to users, especially for foreign-key checks.

--
With Regards,
Amit Kapila.