Re: NOT ENFORCED constraint feature

On Fri, Feb 14, 2025 at 8:41 PM Ashutosh Bapat
<ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
>
> On Thu, Feb 13, 2025 at 5:27 PM Álvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
> >
> > On 2025-Feb-13, Ashutosh Bapat wrote:
> >
> > > > So considering that, I think a three-state system makes more sense.
> > > > Something like:
> > > >
> > > > 1) NOT ENFORCED -- no data is checked
> > > > 2) NOT VALID -- existing data is unchecked, new data is checked
> > > > 3) ENFORCED -- all data is checked
> > > >
> > > > Transitions:
> > > >
> > > > (1) - [ ALTER TABLE ... ALTER CONSTRAINT ... NOT VALID ] -> (2)
> > >
> > > Per your notation, this means the the constraint is not enforced but
> > > new data is checked - that seems a contradiction, how would we check
> > > the data when the constraint is not being enforced. Or do you suggest
> > > that we convert a NOT ENFORCED constraint to ENFORCED as a result of
> > > converting it to NOT VALID?
> >
> > I agree this one is a little weird. For this I would have the command
> > be
> > ALTER TABLE ... ALTER CONSTRAINT ... ENFORCED NOT VALID
> > this way it's explicit that what we want is flip the ENFORCED bit while
> > leaving NOT VALID as-is.
> >
> > > > (2) - [ ALTER TABLE ... VALIDATE CONSTRAINT ... ] -> (3)
> > >
> > > As a result of this a not enforced constraint would turn into an
> > > enforced constraint. The user might have intended to just validate the
> > > data but not enforce it to avoid paying price for the checks on new
> > > data.
> >
> > I'm not sure there's a use case for validating existing data without
> > starting to enforce the constraint. The data can become invalid
> > immediately after you've run the command, so why bother?
>
> Validating whole table at a time is cheaper than doing it for every
> row as it appears. So the ability to validate data in batches at
> regular intervals instead of validating every row has some
> attractiveness, esp in huge data/analytics cases. And we could
> implement it without much cost. But I don't have a concrete usecase.
>

Well, I’m not sure if it’s worth validating data in batches when we
don’t maintain their state, as this would lead to revalidating the
same data in the next validation along with newly inserted records.

Also, based on the current implementation, we can perform CHECK
constraint validation, but not FOREIGN KEY constraint validation,
since the necessary triggers for referential integrity checks haven’t
been created for NOT ENFORCED. While we can create those triggers,
it’s unclear whether we want to keep them around if they aren’t being
used for NOT ENFORCED constraints.

Regards,
Amul