Re: NOT ENFORCED constraint feature

On Tue, Oct 8, 2024, at 11:06, Amul Sul wrote:

The attached patch proposes adding the ability to define CHECK and
FOREIGN KEY constraints as NOT ENFORCED.

Thanks for working on this!

Adding NOT ENFORCED to CHECK constraints is simple, see 0001 patch,

I've looked at the 0001 patch and think it looks simple and straight forward.

but implementing it for FOREIGN KEY constraints requires more code due
to triggers, see 0002 - 0005 patches.

I can't say that much yet about the code changes in 0002 - 0005 yet,
but I've tested the patches and successfully experimented with the feature.

Also think the documentation is good and sound. Only found a minor typo:
-    Adding a enforced <literal>CHECK</literal> or <literal>NOT NULL</literal>
+    Adding an enforced <literal>CHECK</literal> or <literal>NOT NULL</literal>

There are various approaches for
implementing NOT ENFORCED foreign keys, what I thought of:

1. When defining a NOT ENFORCED foreign key, skip the creation of
triggers used for referential integrity check, while defining an
ENFORCED foreign key, remain the same as the current behaviour. If an
existing foreign key is changed to NOT ENFORCED, the triggers are
dropped, and when switching it back to ENFORCED, the triggers are
recreated.

2. Another approach could be to create the NOT ENFORCED constraint
with the triggers as usual, but disable those triggers by updating the
pg_trigger catalog so that they are never executed for the check. And
enable them when the constraint is changed back to ENFORCED.

3. Similarly, a final approach would involve updating the logic where
trigger execution is decided and skipping the execution if the
constraint is not enforced, rather than modifying the pg_trigger
catalog.

In the attached patch, the first approach has been implemented. This
requires more code changes but prevents unused triggers from being
left in the database and avoids the need for changes all over the
place to skip trigger execution, which could be missed in future code
additions.

I also like the first approach, since I think it's nice the pg_trigger
entires are inserted / deleted upon enforced / not enforced.

I also prefer this, as it gets us out from any possible dance with enabling / disabling triggers.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com