Horton

Re: NOT ENFORCED constraint feature

Peter Eisentraut <peter@eisentraut.org>

View thread
From: Peter Eisentraut <peter@eisentraut.org>
To: Álvaro Herrera <alvherre@alvh.no-ip.org>, Isaac Morland <isaac.morland@gmail.com>
Cc: Amul Sul <sulamul@gmail.com>, 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-12T12:41:22Z
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 →

  1. Add support for NOT ENFORCED in foreign key constraints

  2. Expand test a bit

  3. refactor: Pass relation OID instead of Relation to createForeignKeyCheckTriggers()

  4. refactor: Split ATExecAlterConstraintInternal()

  5. refactor: Move some code that updates pg_constraint to a separate function

  6. Move RemoveInheritedConstraint() call slightly earlier

  7. refactor: Split tryAttachPartitionForeignKey()

  8. refactor: re-add ATExecAlterChildConstr()

  9. Add ATAlterConstraint struct for ALTER .. CONSTRAINT

  10. refactor: split ATExecAlterConstrRecurse()

  11. Add support for NOT ENFORCED in CHECK constraints

On 11.02.25 14:36, Álvaro Herrera wrote:
> On 2025-Feb-10, Isaac Morland wrote:
> 
>> 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.
> 
> I kinda agree with you and would prefer that things were that way as
> well.  But look at the discussion starting at
> https://postgr.es/m/CAExHW5tV23Sw+Nznv0KpdNg_t7LrXY1WM9atiC=eKKSsKHSnuQ@mail.gmail.com
> whereby it was apparently established that if you have a
> NOT VALID NOT ENFORCED
> constraint, and you make it enforced, then you should somehow end up
> with a NOT VALID ENFORCED constraint, which says to me that we need to
> store the fact that the constraint was NOT VALID to start with; and
> correspondingly if it's VALID NOT ENFORCED and you enforce it, then it
> ends up VALID ENFORCED.  If we take this view of the world (with which,
> I repeat, I disagree) then we must keep track of whether the constraint
> was valid or not valid to start with.  And this means that we need to
> keep convalidated=true _regardless_ of whether conenforced is false.
> So in this view of the world there aren't three states but four.
> 
> I would prefer there to be three states as well, but apparently I'm
> outvoted on this.

Just to make this a bit more confusing, here is another interpretation 
of the state NOT ENFORCED VALID (they call it DISABLE VALIDATE):

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/constraint.html#GUID-1055EA97-BA6F-4764-A15F-1024FD5B6DFE__I1002349

"""
DISABLE VALIDATE disables the constraint and drops the index on the 
constraint, but keeps the constraint valid. This feature is most useful 
in data warehousing situations, because it lets you load large amounts 
of data while also saving space by not having an index. This setting 
lets you load data from a nonpartitioned table into a partitioned table 
using the exchange_partition_subpart clause of the ALTER TABLE statement 
or using SQL*Loader. All other modifications to the table (inserts, 
updates, and deletes) by other SQL statements are disallowed.
"""