Re: [PATCH] Fix unexpected loss of DEFERRABLE property after toggling NOT ENFORCED / ENFORCED - Mailing list pgsql-hackers

On Fri, Mar 27, 2026 at 4:41 PM Yasuo Honda <yasuo.honda@gmail.com> wrote:
>
> (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.

Thanks for updating the patch!

> I've also covered INITIALLY IMMEDIATE alongside INITIALLY DEFERRED.

I'm not sure this test adds much value. Testing DEFERRABLE INITIALLY DEFERRED
seems sufficient, so I removed the DEFERRABLE INITIALLY IMMEDIATE case.
The updated patch is attached.

+-- verify that tgdeferrable/tginitdeferred are preserved after NOT
ENFORCED -> ENFORCED
+ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_fk_fkey DEFERRABLE
INITIALLY DEFERRED;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_fk_fkey NOT ENFORCED;

It looks like this assumes the constraint is initially ENFORCED,
but in this part of the test it is already NOT ENFORCED. So I moved
this test to a section where the constraint is ENFORCED, which
seems more appropriate.

I also updated the commit log message.

Barring any objections, I will commit this and backpatch it to v18, where
NOT ENFORCED foreign keys were introduced.

Regards,

--
Fujii Masao