Re: NOT ENFORCED constraint feature

amul sul <sulamul@gmail.com>

From: Amul Sul <sulamul@gmail.com>

To: Peter Eisentraut <peter@eisentraut.org>

Cc: jian he <jian.universality@gmail.com>, Alvaro Herrera <alvherre@alvh.no-ip.org>, pgsql-hackers <pgsql-hackers@postgresql.org>, Joel Jacobson <joel@compiler.org>

Date: 2025-01-17T12:50:15Z

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 →

Add support for NOT ENFORCED in foreign key constraints
- eec0040c4bcd 18.0 landed
Expand test a bit
- 5d5f415816a6 18.0 landed
refactor: Pass relation OID instead of Relation to createForeignKeyCheckTriggers()
- ef7a5af77d44 18.0 landed
refactor: Split ATExecAlterConstraintInternal()
- 639238b978fe 18.0 landed
refactor: Move some code that updates pg_constraint to a separate function
- a3280e2a494f 18.0 landed
Move RemoveInheritedConstraint() call slightly earlier
- dabccf45139a 18.0 landed
refactor: Split tryAttachPartitionForeignKey()
- 1d26c2d2c4b8 18.0 landed
refactor: re-add ATExecAlterChildConstr()
- 64224a834ce4 18.0 landed
Add ATAlterConstraint struct for ALTER .. CONSTRAINT
- 80d7f990496b 18.0 landed
refactor: split ATExecAlterConstrRecurse()
- 7a947ed25b54 18.0 landed
Add support for NOT ENFORCED in CHECK constraints
- ca87c415e2fc 18.0 landed

Attachments

v9-0001-POSTED_SEPARATLY-Allow_NOT_VALID_FK_on_Partitione.patch (application/x-patch) patch v9-0001
v9-0002-refactor-Split-tryAttachPartitionForeignKey.patch (application/x-patch) patch v9-0002
v9-0003-refactor-Change-ATExecAlterConstrRecurse-argument.patch (application/x-patch) patch v9-0003
v9-0004-Remove-hastriggers-flag-check-before-fetching-FK-.patch (application/x-patch) patch v9-0004
v9-0005-Add-support-for-NOT-ENFORCED-in-foreign-key-const.patch (application/x-patch) patch v9-0005
v9-0006-Merge-the-parent-and-child-constraints-with-diffe.patch (application/x-patch) patch v9-0006

On Thu, Jan 16, 2025 at 6:55 PM Amul Sul <sulamul@gmail.com> wrote:
>
> On Thu, Jan 16, 2025 at 6:07 PM Peter Eisentraut <peter@eisentraut.org> wrote:
> >
> > On 11.01.25 18:26, Amul Sul wrote:
> > > On Saturday, 11 January 2025, Peter Eisentraut <peter@eisentraut.org
> > > <mailto:peter@eisentraut.org>> wrote:
> > >
> > >     I have applied v8-0001, with some editing of the documentation and
> > >     in the tests.  I'll continue reviewing the subsequent patches.
> > >
> > >
> > > Thank you for the improvement and commit.
> >
> > I have also committed v8-0002, the refactor of ATExecAlterConstrRecurse().
> >
> > All the remaining patches go together, so w are now waiting on an
> > updated patch set from you.
> >
>
> Thanks!
>
> Yes, I'm working on it and will post it tomorrow.
>

Attached is a new set of patches. Please ignore patch 0001 here, which
was posted separately [1] -- proposes allowing invalid foreign key
constraints on partitioned tables. Patch 0002 refactors
tryAttachPartitionForeignKey(), primarily needed by the new patch
v9-0006 included in this set. This patch handles merging constraints
with different enforceability. Without it, a new constraint would be
created on the child. However, this patch introduces additional code
that may appear somewhat messy or confusing. I've added plenty of
comments to clarify the logic. While I’ve done some testing, it hasn’t
been extensive. I plan to do more testing in the next week.

Please let me know if we should continue with patch 0006 improvement,
or if the feature up to patch 0005, which enforces matching
enforceability before merging constraints, is sufficient.

1]  https://postgr.es/m/CAAJ_b96Bp=-ZwihPPtuaNX=SrZ0U6ZsXD3+fgARO0JuKa8v2jQ@mail.gmail.com

Regards,
Amul