Re: BUG #18019: misbehaviour by replication

At Wed, 12 Jul 2023 10:08:54 +0000, PG Bug reporting form <noreply(at)postgresql(dot)org> wrote in
(In short, foreign constraint trigger doesn't fire on apply woker.)
> this is (imho) misbehaviour!
> replication should not break integrity and break references logic!
> or explain why it's right and how to live with it?

You can activate the fkey constraints on the subscriber by setting the
underlying triggers to ENABLE ALWAYS (or REPLICA) using the ALTER
TABLE command.

By design, triggers are not active on subscribers by default. It might
seem peculiar, especially in the context of foreign key constraints,
as check constraints are operational on subscribers. Moreover,
although the documentation mentions that the purpose of the behavior
is avoid propagating data between tables again on subscribers, foreign
key triggers don't contribute to this.

If we're willing to enable constraint triggers on subscribers by
default, CreateTrigger can choose trigger_fires_when passed to
CreateTriggerFiringOn based on constraintOid. However, I'm unceratin
about the case of CREATE CONSTRAINT TRIGGER.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center