Re: BUG #18019: misbehaviour by replication

On Thu, Jul 13, 2023 at 1:19 PM Kyotaro Horiguchi
<horikyota.ntt@gmail.com> wrote:
>
> At Wed, 12 Jul 2023 10:08:54 +0000, PG Bug reporting form <noreply@postgresql.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.
>

It might be a good idea to discuss this topic on -hackers as this is
the way it works from the beginning. So changing it requires broader
discussion.

--
With Regards,
Amit Kapila.