Re: NOT ENFORCED constraint feature

Isaac Morland <isaac.morland@gmail.com>

From: Isaac Morland <isaac.morland@gmail.com>

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

Cc: Amul Sul <sulamul@gmail.com>, 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-10T19:30:52Z

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 Mon, 10 Feb 2025 at 13:48, Álvaro Herrera <alvherre@alvh.no-ip.org>
wrote:

I think this proposed state of affairs is problematic.  Current queries
> that assume that pg_constraint.convalidated means that a constraint is
> validated would be broken.  My suggestion at this point is that instead of
> adding a separate boolean column to pg_constraint we should be replacing
> `bool convalidated` with `char convalidity`, with new defines for all the
> possible states we require: enforced-and-valid ("V"alid),
> enforced-not-validated ("i"nvalid), not-enforced-and-not-valid (terribly
> "I"nvalid or maybe "U"nenforced),
> not-enforced-but-was-valid-before-turning-unenforced ("u"nenforced).
> Breaking user queries would make all apps reassess what do they actually
> want to know about the constraint without assumptions of how enforcement
> worked in existing Postgres releases.
>

I'm having a lot of trouble understanding the operational distinction
between your 'u' and 'U'. If it's not enforced, it cannot be assumed to be
valid, regardless of whether it was valid in the past. I'm not sure what I
think of a single character vs. 2 booleans, but there are only 3 sensible
states either way: valid enforced, invalid enforced, and invalid unenforced.

Additionally, if there are officially 4 status possibilities then all code
that looks for unenforced constraints has to look for both valid and
invalid unenforced constraints if we use a char; it's not as bad with 2
booleans because one can just check the "enforced" boolean.