public inbox for [email protected]  

help / color / mirror / Atom feed

From: Kyotaro Horiguchi <[email protected]>
To: [email protected]
Subject: Do we want to enable foreign key constraints on subscriber?
Date: Wed, 19 Jul 2023 15:49:57 +0900 (JST)
Message-ID: <[email protected]> (raw)

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/[email protected]


Attachments:

  [text/x-patch] fkey_eanble_on_subscribers.diff (617B, ../[email protected]/2-fkey_eanble_on_subscribers.diff)
  download | inline diff:
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);
 }
 
 /*

view thread (2+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Do we want to enable foreign key constraints on subscriber?
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox