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

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.

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

--
Best Wishes,
Ashutosh Bapat