Re: [PATCH] Fix unexpected loss of DEFERRABLE property after toggling NOT ENFORCED / ENFORCED

On Fri, Feb 27, 2026 at 6:51 PM Yasuo Honda <yasuo(dot)honda(at)gmail(dot)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 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