Re: NOT ENFORCED constraint feature

Peter Eisentraut <peter@eisentraut.org>

From: Peter Eisentraut <peter@eisentraut.org>

To: Amul Sul <sulamul@gmail.com>

Cc: Álvaro Herrera <alvherre@alvh.no-ip.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-04-02T12:32:31Z

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

On 28.03.25 14:27, Amul Sul wrote:
> On Fri, Mar 28, 2025 at 3:34 PM Ashutosh Bapat
> <ashutosh.bapat.oss@gmail.com> wrote:
>>
>> On Thu, Mar 27, 2025 at 6:25 PM Amul Sul <sulamul@gmail.com> wrote:
>>
>>>
>>> I am not sure how to make such tests stable enough since the trigger
>>> name involves OIDs. In count check, I tried adjusting the join
>>> condition to ensure that I get the exact same type of constraint
>>> w.r.t. trigger relation and the constraint.
>>
>> There are tests which mask variable parts of EXPLAIN output. Can we
>> use similar trick to mask OIDs from the trigger names?
> 
> Okay, tried it in the attached version. Please check if it looks good.

I have committed version 21 of the patches (without 0006).

The patch you posted failed the regression test foreign_key because in 
the output of the queries that list the triggers, the conname output did 
not match the expected output.  I committed it so that the test output 
matches the code behavior.  But please double-check that that's what you 
intended.

Also, something we hadn't looked at before, I think, I made 
get_relation_foreign_keys() in src/backend/optimizer/util/plancat.c 
ignore not-enforced foreign keys.  That means, not-enforced foreign keys 
will not be used for cost estimation.  This is, I think, what we want, 
as we discussed earlier.  If we ever want an alternative mode where 
not-enforced constraints are considered for cost-estimation, then we 
could quite easily tweak this.