Horton

Re: NOT ENFORCED constraint feature

Andrew Dunstan <andrew@dunslane.net>

View thread
From: Andrew Dunstan <andrew@dunslane.net>
To: Joel Jacobson <joel@compiler.org>, Amul Sul <sulamul@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2024-10-09T13:15:12Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add support for NOT ENFORCED in foreign key constraints

  2. Expand test a bit

  3. refactor: Pass relation OID instead of Relation to createForeignKeyCheckTriggers()

  4. refactor: Split ATExecAlterConstraintInternal()

  5. refactor: Move some code that updates pg_constraint to a separate function

  6. Move RemoveInheritedConstraint() call slightly earlier

  7. refactor: Split tryAttachPartitionForeignKey()

  8. refactor: re-add ATExecAlterChildConstr()

  9. Add ATAlterConstraint struct for ALTER .. CONSTRAINT

  10. refactor: split ATExecAlterConstrRecurse()

  11. Add support for NOT ENFORCED in CHECK constraints

On 2024-10-09 We 5:14 AM, Joel Jacobson wrote:
> 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