Re: NOT ENFORCED constraint feature

amul sul <sulamul@gmail.com>

From: Amul Sul <sulamul@gmail.com>

To: Peter Eisentraut <peter@eisentraut.org>

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

Date: 2025-02-10T06:03:06Z

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

v13-0001-Add-AlterConstraintStmt-struct-for-ALTER-.-CONST.patch (application/x-patch) patch v13-0001
v13-0002-refactor-Split-tryAttachPartitionForeignKey.patch (application/x-patch) patch v13-0002
v13-0003-Move-the-RemoveInheritedConstraint-function-call.patch (application/x-patch) patch v13-0003
v13-0004-refactor-Pass-Relid-instead-of-Relation-to-creat.patch (application/x-patch) patch v13-0004
v13-0005-refactor-Change-ATExecAlterConstrRecurse-argumen.patch (application/x-patch) patch v13-0005
v13-0006-Remove-hastriggers-flag-check-before-fetching-FK.patch (application/x-patch) patch v13-0006
v13-0007-Ease-the-restriction-that-a-NOT-ENFORCED-constra.patch (application/x-patch) patch v13-0007
v13-0008-Add-support-for-NOT-ENFORCED-in-foreign-key-cons.patch (application/x-patch) patch v13-0008
v13-0009-Merge-the-parent-and-child-constraints-with-diff.patch (application/x-patch) patch v13-0009

On Tue, Feb 4, 2025 at 7:22 PM Peter Eisentraut <peter@eisentraut.org> wrote:
>
> On 03.02.25 06:19, Ashutosh Bapat wrote:
> > Here's how I see the state conversions happening.
> >
> > NOT VALID, NOT ENFORCED changed to NOT_VALID, ENFORCED - no data
> > validation required, constraint is enforced on the new tuples/changes
> > NOT VALID, ENFORCED changed to NOT VALID, NOT ENFORCED - no data
> > validation, constraint isn't enforced anymore
> > VALID, NOT ENFORCED changed to VALID, ENFORCED - data validation
> > required, constraint is enforced
> > VALID, ENFORCED changed to VALID, NOT ENFORCED - no data validation
> > required, constrain isn't enforced anymore, we rely on user to enforce
> > the constraint on their side
>
> This looks sensible to me.

Attached patch implemented this behaviour. To achieve this, we have to
revert (see 0007) some committed code and relax the restriction that
the NOT ENFORCED constraint must also be NOT VALID. Now, NOT ENFORCED
and NOT VALID are independent statuses, and the psql-meta meta command
will display NOT VALID alongside the NOT ENFORCED constraint.
Previously, we hid NOT VALID for NOT ENFORCED constraints, assuming it
would be implied, but that is no longer the case.

Apart from this, I have added a few more tests in 0009. Additionally,
I made some minor code rearrangements in the
AttachPartitionForeignKey() function -- was introduced in the 0002
refactoring patch. I kept these rearrangement changes separate in 0003
to highlight them, allowing the reviewer to identify any potential
issues, though I don't believe there are any. Of course, I could be
wrong.

Also, I need more time for additional testing of the 0008 and 0009
patches, which I plan to complete by the end of this week. Thanks.

Regards,
Amul