MIME-Version: 1.0
References: 
 <CAKmOUTms2nkxEZDdcrsjq5P3b2L_PR266Hv8kW5pANwmVaRJJQ@mail.gmail.com>
In-Reply-To: 
 <CAKmOUTms2nkxEZDdcrsjq5P3b2L_PR266Hv8kW5pANwmVaRJJQ@mail.gmail.com>
From: Fujii Masao <masao.fujii@gmail.com>
Date: Tue, 24 Mar 2026 15:29:55 +0900
Message-ID: 
 <CAHGQGwFrsVLxmXrtrQD2cR1K74de6sq3eEvPOPiEDDoRDdhdSw@mail.gmail.com>
Subject: Re: [PATCH] Fix unexpected loss of DEFERRABLE property after toggling
 NOT ENFORCED / ENFORCED
To: Yasuo Honda <yasuo.honda@gmail.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAHGQGwFrsVLxmXrtrQD2cR1K74de6sq3eEvPOPiEDDoRDdhdSw%40mail.gmail.com>
Precedence: bulk

On Fri, Feb 27, 2026 at 6:51=E2=80=AFPM Yasuo Honda <yasuo.honda@gmail.com>=
 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 are=
a,
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 catalo=
g
state (e.g., pg_trigger) but also the actual behavior? For example, we coul=
d
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,

--=20
Fujii Masao