Re: NOT ENFORCED constraint feature

amul sul <sulamul@gmail.com>

From: Amul Sul <sulamul@gmail.com>

To: Álvaro Herrera <alvherre@alvh.no-ip.org>

Cc: Peter Eisentraut <peter@eisentraut.org>, Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>, jian he <jian.universality@gmail.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>, Joel Jacobson <joel@compiler.org>, Suraj Kharage <suraj.kharage@enterprisedb.com>

Date: 2025-02-17T10:11:20Z

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

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

On Tue, Feb 11, 2025 at 12:18 AM Álvaro Herrera <alvherre@alvh.no-ip.org> wrote:
>
[...]
>
> >  • v13-0001-Add-AlterConstraintStmt-struct-for-ALTER-.-CONST.patch
>
> I think the new node name is wrong, because it makes the code looks as if we support ALTER CONSTRAINT as a statement for this, which is false.  (This is a repeat of ReplicaIdentityStmt, which I think is a mistake).  I would suggest a name like ATAlterConstraint instead.  Perhaps we can use that in the patch for ALTER CONSTRAINT ... SET [NO] INHERIT as well, instead of (as I suggested Suraj) creating a separate subcommand number.

I have renamed AlterConstraintStmt to ATAlterConstraint as per your
suggestion in the attached version. Apart from this, there are no
other changes, as the final behavior is still unclear based on the
discussions so far.

If we don’t want to keep a constraint marked as valid when it is not
enforced, we should revert to the v12 version behavior, where the
constraint is marked invalid when changed to NOT ENFORCED and remains
so even after being changed to ENFORCED, until explicitly validated
using the existing ALTER ... VALIDATE CONSTRAINT command.

However, if we want to provide an option to validate the constraint
immediately when changing it to ENFORCED, we could introduce support
for a syntax like:

ALTER TABLE ... ALTER CONSTRAINT ... [ NOT ] ENFORCED [ (WITH |
WITHOUT) VALIDATION ]

Regards,
Amul