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

(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 <masao(dot)fujii(at)gmail(dot)com> wrote:
>
> 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