Subject: Re: NOT ENFORCED constraint feature

From: Isaac Morland <[email-masked]>
Date: Mon, 10 Feb 2025 14:30:52 -0500

On Mon, 10 Feb 2025 at 13:48, Álvaro Herrera <[email-masked]>
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.

---

From: Álvaro Herrera <[email-masked]>
Date: Tue, 11 Feb 2025 14:36:05 +0100

> 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=[email-masked]
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.

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

---

From: Isaac Morland <[email-masked]>
Date: Tue, 11 Feb 2025 10:39:35 -0500

> I would prefer there to be three states as well, but apparently I'm

Sounds like we agree. I think the problem is with the statement in the
linked discussion that “If the constraint is VALID and later marked as NOT
ENFORCED, changing it to ENFORCED should also keep it VALID.” This ignores
that if it is changed to NOT ENFORCED that should immediately change it to
NOT VALID if it is not already so.

Has anybody argued for how it makes any sense at all to have a constraint
that is VALID (and therefore will be assumed to be true by the planner),
yet NOT ENFORCED (and therefore may well not be true)? What next, a patch
to the planner so that it only treats as true constraints that are both
VALID and ENFORCED?

Re: the 3 or 4 values for the single character status, there is a similar
issue with relkind, where one can imagine writing "relkind IN ('r')" when
one meant "relkind IN ('r', 'v')" or something else; but on the other hand,
one can easily imagine actually wanting the first one of those. But here,
it's not at all clear to me when you would ever want to distinguish between
'u' and 'U', but it is clear to me that it would be natural to write "… =
'U'" when one actually needs to write "… IN ('u', 'U')", or perhaps "…
ILIKE 'u'" (not what I would want to see).

---

From: Ashutosh Bapat <[email-masked]>
Date: Wed, 12 Feb 2025 10:17:07 +0530

On Tue, Feb 11, 2025 at 9:09 PM Isaac Morland <[email-masked]> wrote:
>
> On Tue, 11 Feb 2025 at 08:36, Álvaro Herrera <[email-masked]> wrote:
>>
> Sounds like we agree. I think the problem is with the statement in the linked discussion that “If the constraint is VALID and later marked as NOT ENFORCED, changing it to ENFORCED should also keep it VALID.” This ignores that if it is changed to NOT ENFORCED that should immediately change it to NOT VALID if it is not already so.
>
> Has anybody argued for how it makes any sense at all to have a constraint that is VALID (and therefore will be assumed to be true by the planner), yet NOT ENFORCED (and therefore may well not be true)? What next, a patch to the planner so that it only treats as true constraints that are both VALID and ENFORCED?

I have been asking a different question: What's the use of
not-enforced constraints if we don't allow VALID, NOT ENFORCED state
for them? OTOH, consider an application which "knows" that the
constraint is valid for the data (either because of checks at
application level, or because the data was replicated from some other
system where the cosntraints were applied). It's a natural ask to use
the constraints for, say optimization, but don't take unnecessary
overhead of validating them. VALID, NOT ENFORCED state helps in such a
scenario. Of course an application can misuse it (just like stable
marking on a function), but well ... they will be penalised for their
misuse.

-- 
Best Wishes,
Ashutosh Bapat

---

From: Álvaro Herrera <[email-masked]>
Date: Wed, 12 Feb 2025 12:13:52 +0100

On 2025-Feb-12, Ashutosh Bapat wrote:

> for them?

That's a question for the SQL standards committee.  They may serve
schema documentation purposes, for example.
https://www.postgresql.eu/events/pgconfeu2024/schedule/session/5677-exploring-postgres-databases-with-graphs/

> OTOH, consider an application which "knows" that the constraint is
> valid for the data (either because of checks at application level, or
> because the data was replicated from some other system where the
> cosntraints were applied). It's a natural ask to use the constraints
> for, say optimization, but don't take unnecessary overhead of
> validating them. VALID, NOT ENFORCED state helps in such a scenario.
> Of course an application can misuse it (just like stable marking on a
> function), but well ... they will be penalised for their misuse.

I disagree that we should see a VALID NOT ENFORCED constraint as one
that can be used for query optimization purposes.  This is only going to
bring users pain, because it's far too easy to misuse and they will get
wrong query results, possibly without knowing for who knows how long.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"El número de instalaciones de UNIX se ha elevado a 10,
y se espera que este número aumente" (UPM, 1972)

---

From: Peter Eisentraut <[email-masked]>
Date: Wed, 12 Feb 2025 13:41:22 +0100

> I would prefer there to be three states as well, but apparently I'm

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