Re: NOT ENFORCED constraint feature - Mailing list pgsql-hackers

On Mon, Feb 3, 2025 at 10:49 AM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
>
> On Mon, Feb 3, 2025 at 9:57 AM Amul Sul <sulamul@gmail.com> wrote:
> >
> > On Fri, Jan 31, 2025 at 7:10 PM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> > >
> > > On 2025-Jan-31, Ashutosh Bapat wrote:
> > >
> > > > But if the constraint is NOT VALID and later marked as NOT ENFORCED,
> > > > what is expected behaviour while changing it to ENFORCED?
> > >
> > > I think what you want is a different mode that would be ENFORCED NOT
> > > VALID, which would be an extension of the standard, because the standard
> > > does not support the concept of NOT VALID.  So while I think what you
> > > want is nice, I'm not sure that this patch necessarily must implement
> > > it.
> > >
>
> This way allows VALID/NOT VALID and ENFORCED/NOT ENFORCED states to
> work together and also implement behaviour specified by the standard
> (ref. Peter's email). If there's some other way to implement the
> behaviour, that's fine too.
>
> >
> > Here is my understanding behind this feature implementation -- I am
> > not claiming to be 100% correct, I am confident I am not entirely
> > wrong either. Let me explain with an example: imagine a user adds a
> > VALID constraint to a table that already has data, and the user is
> > completely sure that all the data complies with the constraint. Even
> > in this case, the system still runs a validation check. This is
> > expected behavior because the system can't just take the user's word
> > for it -- it needs to explicitly confirm that the data is valid
> > through validation.
> >
> > Now, with a NOT ENFORCED constraint, it's almost like the constraint
> > doesn't exist, because no checks are being performed and there is no
> > visible effect for the user, even though the constraint is technically
> > still there. So when the constraint is switched to ENFORCED, we should
> > be careful not to automatically mark it as validated (regardless of
> > its previous validate status) unless the data is actually checked
> > against the constraint -- treat as adding a new VALID constraint. Even
> > if the user is absolutely sure the data complies, we should still run
> > the validation to ensure reliability.
> >
> > In response to Ashutosh’s point about the VALID/NOT ENFORCED scenario:
> > if a constraint is initially VALID, then marked as NOT ENFORCED, and
> > later switched back to ENFORCED -- IMO, it shouldn't automatically be
> > considered VALID.
>
> I am suggesting that when a constraint is changed from NOT ENFORCED to
> ENFORCED, if it's marked VALID - we run validation checks.
>

Ok.

> Here's how I see the state conversions happening.
>
> NOT VALID, NOT ENFORCED changed to NOT_VALID, ENFORCED - no data
> validation required, constraint is enforced on the new tuples/changes
> NOT VALID, ENFORCED changed to NOT VALID, NOT ENFORCED - no data
> validation, constraint isn't enforced anymore
> VALID, NOT ENFORCED changed to VALID, ENFORCED - data validation
> required, constraint is enforced
> VALID, ENFORCED changed to VALID, NOT ENFORCED - no data validation
> required, constrain isn't enforced anymore, we rely on user to enforce
> the constraint on their side
>

Understood, thanks for the detailed explanation. This is what I had
implemented in the v4 patch, and I agree with this. If we decide to go
with this, I can revert the behavior to the v4 patch set.

Regards,
Amul