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>, Alexandra Wang <alexandra.wang.oss@gmail.com>, Suraj Kharage <suraj.kharage@enterprisedb.com>

Date: 2025-03-28T09:00:39Z

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

v20-0004-Remove-hastriggers-flag-check-before-fetching-FK.patch (application/octet-stream) patch v20-0004
v20-0005-Add-support-for-NOT-ENFORCED-in-foreign-key-cons.patch (application/octet-stream) patch v20-0005
v20-0006-Merge-the-parent-and-child-constraints-with-diff.patch (application/octet-stream) patch v20-0006

On Thu, Mar 27, 2025 at 7:45 PM Álvaro Herrera <alvherre@alvh.no-ip.org> wrote:
>
> On 2025-Mar-27, Amul Sul wrote:
>
> > On Thu, Mar 27, 2025 at 6:28 PM Peter Eisentraut <peter@eisentraut.org> wrote:
>
> > > That said, is there a simpler way?  Patch 0003 appears to add a lot of
> > > complexity.  Could we make this simpler by saying, if you have otherwise
> > > matching constraints with different enforceability, make this an error.
> > > Then users can themselves adjust the enforceability how they want to
> > > make it match.
> >
> > We can simply discard this patch, as it still reflects the correct
> > behavior. It creates a new constraint without affecting the existing
> > constraint with differing enforceability on the child. I noticed
> > similar behavior with deferrability -- when it differs, the
> > constraints are not merged, and a new constraint is created on the
> > child. Let me know your thoughts so I can avoid squashing patch 0006.
>
> I didn't read that patch and I don't know what level of complexity we're
> talking about, but the idea of creating a second constraint beside an
> existing one itches me.  I'm pretty certain most users would rather not
> end up with redundant constraints that only differ in enforceability or
> whatever other properties.  I failed to realize that this was happening
> when adding FKs on partitioned tables, and I now think it was a mistake.
> (As I said in some previous thread, I'd rather have this kind of
> situation raise an error so that the user can do something about it,
> rather than silently moving ahead with a worse solution like creating a
> redundant constraint.)
>

Okay, in the attached version, I’ve added an error in
tryAttachPartitionForeignKey() if the enforceability is different.
Please have a look at the 0005 patch and let me know if it looks good.
The rest of the patches remain unchanged.

I haven’t squashed the patches because, if we decide to keep the error
and avoid further complexity, we can simply discard the 0006 patch.

Regards,
Amul