(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
v2-Restore-tgdeferrable-and-tginitdeferred-after-NOT.patch
Description: Binary data
