public inbox for [email protected]  

help / color / mirror / Atom feed

From: Fujii Masao <[email protected]>
To: Yasuo Honda <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Subject: Re: [PATCH] Fix unexpected loss of DEFERRABLE property after toggling NOT ENFORCED / ENFORCED
Date: Tue, 24 Mar 2026 15:29:55 +0900
Message-ID: <CAHGQGwFrsVLxmXrtrQD2cR1K74de6sq3eEvPOPiEDDoRDdhdSw@mail.gmail.com> (raw)
In-Reply-To: <CAKmOUTms2nkxEZDdcrsjq5P3b2L_PR266Hv8kW5pANwmVaRJJQ@mail.gmail.com>
References: <CAKmOUTms2nkxEZDdcrsjq5P3b2L_PR266Hv8kW5pANwmVaRJJQ@mail.gmail.com>

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

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: <CAHGQGwFrsVLxmXrtrQD2cR1K74de6sq3eEvPOPiEDDoRDdhdSw@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