public inbox for [email protected]
help / color / mirror / Atom feedFrom: Yasuo Honda <[email protected]>
To: Fujii Masao <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Subject: Re: [PATCH] Fix unexpected loss of DEFERRABLE property after toggling NOT ENFORCED / ENFORCED
Date: Fri, 27 Mar 2026 16:41:36 +0900
Message-ID: <CAKmOUTkrgZCWq63Ab0Ff01JQ7u3ua__NyT=V8mZxcH3sSAhX3w@mail.gmail.com> (raw)
In-Reply-To: <CAHGQGwFrsVLxmXrtrQD2cR1K74de6sq3eEvPOPiEDDoRDdhdSw@mail.gmail.com>
References: <CAKmOUTms2nkxEZDdcrsjq5P3b2L_PR266Hv8kW5pANwmVaRJJQ@mail.gmail.com>
<CAHGQGwFrsVLxmXrtrQD2cR1K74de6sq3eEvPOPiEDDoRDdhdSw@mail.gmail.com>
(Resending to the list, as my previous reply went to the sender only.)
Thanks for the comment.
> Regarding the regression test, would it be better to verify not only catalog
state (e.g., pg_trigger) but also the actual behavior?
Agreed. In addition to inspecting pg_trigger contents, I've added test
cases that verify the actual runtime behavior of deferred constraints.
I've also covered INITIALLY IMMEDIATE alongside INITIALLY DEFERRED.
--
Yasuo Honda
On Tue, Mar 24, 2026 at 3:30 PM Fujii Masao <[email protected]> wrote:
>
> On Fri, Feb 27, 2026 at 6:51 PM Yasuo Honda <[email protected]> wrote:
> >
> > Hi,
> >
> > I have encountered an unexpected behavior where the DEFERRABLE and
> > INITIALLY DEFERRED properties of foreign keys are lost after toggling
> > them from NOT ENFORCED to ENFORCED.
> >
> > Background
> >
> > In the Ruby on Rails framework, there is a built-in mechanism to
> > temporarily bypass foreign key checks while loading test data.
> > Currently, this is implemented using: ALTER TABLE ... DISABLE TRIGGER
> > ALL; ALTER TABLE ... ENABLE TRIGGER ALL;
> >
> > However, this requires superuser privileges. With the newly introduced
> > support for "NOT ENFORCED" foreign keys in PostgreSQL 18, I am
> > interested in switching to: ALTER TABLE ... ALTER CONSTRAINT ... NOT
> > ENFORCED; ALTER TABLE ... ALTER CONSTRAINT ... ENFORCED;
> >
> > This would allow the operation to be performed by the table owner
> > without superuser rights. However, I discovered that switching the
> > state back to ENFORCED unexpectedly strips away the DEFERRABLE
> > property.
> >
> > Problem
> >
> > When re-enforcing a constraint, there is a discrepancy between the
> > constraint definition and its underlying triggers.
> > While the flags in pg_constraint remain correct, the corresponding
> > triggers in pg_trigger (tgdeferrable and tginitdeferred) are reset to
> > defaults ('f') when they are reconstructed during the ENFORCED
> > operation.
> >
> > I have attached a reproduction SQL script that demonstrates this by
> > comparing the values in pg_constraint and pg_trigger. In the current
> > PostgreSQL 18.3, you can see that the triggers lose their
> > deferrability even though the constraint itself is still defined as
> > DEFERRABLE. This causes "SET CONSTRAINTS ... DEFERRED" to fail.
> >
> > After Patch
> >
> > * The tgdeferrable and tginitdeferred flags in pg_trigger are
> > correctly preserved to match pg_constraint after the toggle, and
> > deferred execution works as expected.
> >
> > I've attached the reproduction SQL script and a patch to fix this in
> > src/backend/commands/tablecmds.c. The patch and reproduction SQL
> > script were developed with the assistance of Claude Code. I have
> > reviewed and verified the code myself.
> >
> > Any feedback is appreciated.
>
> Thanks for reporting the issue and providing a patch!
>
> I was able to reproduce the issue on the master.
>
> The patch looks good overall, but since I'm not very familiar with this area,
> I'd like to spend a bit more time reviewing the changes in detail.
>
> Regarding the regression test, would it be better to verify not only catalog
> state (e.g., pg_trigger) but also the actual behavior? For example, we could
> check that a foreign key violation is not raised immediately on INSERT,
> but instead at COMMIT even after ALTER CONSTRAINT ENFORCED:
>
> ALTER TABLE ... ALTER CONSTRAINT ... NOT ENFORCED;
> ALTER TABLE ... ALTER CONSTRAINT ... ENFORCED;
> BEGIN;
> INSERT INTO t VALUES (1);
> COMMIT;
>
> In this case, the foreign key violation should be reported at COMMIT,
> even after ALTER CONSTRAINT ... ENFORCED.
>
> Regards,
>
> --
> Fujii Masao
Attachments:
[application/octet-stream] v2-Restore-tgdeferrable-and-tginitdeferred-after-NOT.patch (6.7K, 2-v2-Restore-tgdeferrable-and-tginitdeferred-after-NOT.patch)
download | inline diff:
From 94c66da56a0d670c9fac96ed3fa1cc359eb6a3c5 Mon Sep 17 00:00:00 2001
From: Yasuo Honda <[email protected]>
Date: Thu, 26 Mar 2026 08:40:12 +0900
Subject: [PATCH v2] Restore tgdeferrable and tginitdeferred after NOT ENFORCED
then ENFORCED
When toggling a foreign key constraint from NOT ENFORCED to ENFORCED,
the deferrable and initdeferred flags were not copied from the existing
constraint to the new trigger structure, causing them to be lost.
Message-Id: CAKmOUTms2nkxEZDdcrsjq5P3b2L_PR266Hv8kW5pANwmVaRJJQ@mail.gmail.com
---
src/backend/commands/tablecmds.c | 2 +
src/test/regress/expected/foreign_key.out | 58 +++++++++++++++++++++++
src/test/regress/sql/foreign_key.sql | 36 ++++++++++++++
3 files changed, 96 insertions(+)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index c69c12dc014..dd00e36c69d 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -12558,6 +12558,8 @@ ATExecAlterFKConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
fkconstraint->fk_matchtype = currcon->confmatchtype;
fkconstraint->fk_upd_action = currcon->confupdtype;
fkconstraint->fk_del_action = currcon->confdeltype;
+ fkconstraint->deferrable = currcon->condeferrable;
+ fkconstraint->initdeferred = currcon->condeferred;
/* Create referenced triggers */
if (currcon->conrelid == fkrelid)
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 9ae4dbf1b0a..4ab8ed63ff4 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -1349,6 +1349,64 @@ UPDATE pktable SET id = 10 WHERE id = 5;
-- doesn't match PK, but no error.
INSERT INTO fktable VALUES (0, 20);
ROLLBACK;
+-- verify that tgdeferrable/tginitdeferred are preserved after NOT ENFORCED -> ENFORCED
+ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_fk_fkey DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_fk_fkey NOT ENFORCED;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_fk_fkey ENFORCED;
+SELECT tgdeferrable, tginitdeferred FROM pg_trigger
+WHERE tgconstraint = (SELECT oid FROM pg_constraint
+ WHERE conrelid = 'fktable'::regclass
+ AND conname = 'fktable_fk_fkey');
+ tgdeferrable | tginitdeferred
+--------------+----------------
+ t | t
+ t | t
+ t | t
+ t | t
+(4 rows)
+
+-- verify actual behavior: violation should be deferred to end of transaction
+BEGIN;
+-- doesn't match PK, but no error yet (INITIALLY DEFERRED)
+INSERT INTO fktable VALUES (2, 20);
+-- should catch error from INSERT at commit
+COMMIT;
+ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
+DETAIL: Key (fk)=(20) is not present in table "pktable".
+-- reset
+ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE;
+-- same, but with DEFERRABLE INITIALLY IMMEDIATE: tginitdeferred should be false
+ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_fk_fkey DEFERRABLE INITIALLY IMMEDIATE;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_fk_fkey NOT ENFORCED;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_fk_fkey ENFORCED;
+SELECT tgdeferrable, tginitdeferred FROM pg_trigger
+WHERE tgconstraint = (SELECT oid FROM pg_constraint
+ WHERE conrelid = 'fktable'::regclass
+ AND conname = 'fktable_fk_fkey');
+ tgdeferrable | tginitdeferred
+--------------+----------------
+ t | f
+ t | f
+ t | f
+ t | f
+(4 rows)
+
+-- verify actual behavior: violation should be caught immediately (INITIALLY IMMEDIATE)
+-- doesn't match PK, error at INSERT time
+INSERT INTO fktable VALUES (2, 20);
+ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
+DETAIL: Key (fk)=(20) is not present in table "pktable".
+-- verify that SET CONSTRAINTS DEFERRED still works
+BEGIN;
+SET CONSTRAINTS fktable_fk_fkey DEFERRED;
+-- doesn't match PK, but no error yet (explicitly deferred)
+INSERT INTO fktable VALUES (2, 20);
+-- should catch error from INSERT at commit
+COMMIT;
+ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
+DETAIL: Key (fk)=(20) is not present in table "pktable".
+-- reset
+ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE;
-- try additional syntax
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE;
-- illegal options
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index 3b8c95bf893..d5d6b9a8cef 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -1023,6 +1023,42 @@ UPDATE pktable SET id = 10 WHERE id = 5;
INSERT INTO fktable VALUES (0, 20);
ROLLBACK;
+-- verify that tgdeferrable/tginitdeferred are preserved after NOT ENFORCED -> ENFORCED
+ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_fk_fkey DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_fk_fkey NOT ENFORCED;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_fk_fkey ENFORCED;
+SELECT tgdeferrable, tginitdeferred FROM pg_trigger
+WHERE tgconstraint = (SELECT oid FROM pg_constraint
+ WHERE conrelid = 'fktable'::regclass
+ AND conname = 'fktable_fk_fkey');
+-- verify actual behavior: violation should be deferred to end of transaction
+BEGIN;
+-- doesn't match PK, but no error yet (INITIALLY DEFERRED)
+INSERT INTO fktable VALUES (2, 20);
+-- should catch error from INSERT at commit
+COMMIT;
+-- reset
+ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE;
+-- same, but with DEFERRABLE INITIALLY IMMEDIATE: tginitdeferred should be false
+ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_fk_fkey DEFERRABLE INITIALLY IMMEDIATE;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_fk_fkey NOT ENFORCED;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_fk_fkey ENFORCED;
+SELECT tgdeferrable, tginitdeferred FROM pg_trigger
+WHERE tgconstraint = (SELECT oid FROM pg_constraint
+ WHERE conrelid = 'fktable'::regclass
+ AND conname = 'fktable_fk_fkey');
+-- verify actual behavior: violation should be caught immediately (INITIALLY IMMEDIATE)
+-- doesn't match PK, error at INSERT time
+INSERT INTO fktable VALUES (2, 20);
+-- verify that SET CONSTRAINTS DEFERRED still works
+BEGIN;
+SET CONSTRAINTS fktable_fk_fkey DEFERRED;
+-- doesn't match PK, but no error yet (explicitly deferred)
+INSERT INTO fktable VALUES (2, 20);
+-- should catch error from INSERT at commit
+COMMIT;
+-- reset
+ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE;
-- try additional syntax
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE;
--
2.53.0
view thread (6+ messages) latest in thread
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: [PATCH] Fix unexpected loss of DEFERRABLE property after toggling NOT ENFORCED / ENFORCED
In-Reply-To: <CAKmOUTkrgZCWq63Ab0Ff01JQ7u3ua__NyT=V8mZxcH3sSAhX3w@mail.gmail.com>
* 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