Horton

Re: NOT ENFORCED constraint feature

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

View thread
From: Álvaro Herrera <alvherre@alvh.no-ip.org>
To: Isaac Morland <isaac.morland@gmail.com>
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-11T13:36:05Z
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 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.

> 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.

Well, yes.  You have kinda the same issue with any other system catalog
column that's a 'char', I guess.  Maybe this is more of a problem here
because it's more user-visible than most other catalogs, not sure.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/