Re: NOT ENFORCED constraint feature

On Tue, Feb 11, 2025 at 9:09 PM Isaac Morland <isaac(dot)morland(at)gmail(dot)com> wrote:
>
> On Tue, 11 Feb 2025 at 08:36, Álvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> 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.
>
>
> 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