Re: NOT ENFORCED constraint feature

Alvaro Herrera <alvherre@alvh.no-ip.org>

From: Alvaro Herrera <alvherre@alvh.no-ip.org>

To: Peter Eisentraut <peter@eisentraut.org>

Cc: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>, Amul Sul <sulamul@gmail.com>, jian he <jian.universality@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>, Joel Jacobson <joel@compiler.org>

Date: 2025-02-04T14:22:47Z

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 2025-Feb-04, Peter Eisentraut wrote:

> On 03.02.25 08:50, Alvaro Herrera wrote:
> > On 2025-Feb-03, Ashutosh Bapat wrote:
> > 
> > > VALID, NOT ENFORCED changed to VALID, ENFORCED - data validation
> > > required, constraint is enforced
> > There's no such thing as a VALID NOT ENFORCED constraint.  It just
> > cannot exist.
> 
> The way I interpret this is that the VALID flag is just recording what would
> happen if the constraint was enforced.  So you you take a [NOT] VALID
> ENFORCED constraint and switch it to NOT ENFORCED and back and you get back
> to where you started.

I think it is dangerous.  You can easily end up with undetected
violating rows in the table, and then you won't be able to dump/restore
it anymore.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"After a quick R of TFM, all I can say is HOLY CR** THAT IS COOL! PostgreSQL was
amazing when I first started using it at 7.2, and I'm continually astounded by
learning new features and techniques made available by the continuing work of
the development team."
Berend Tober, http://archives.postgresql.org/pgsql-hackers/2007-08/msg01009.php