Re: NOT ENFORCED constraint feature

On Mon, Feb 3, 2025 at 1:20 PM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
>
> On 2025-Feb-03, Ashutosh Bapat wrote:
>
> > VALID, NOT ENFORCED changed to VALID, ENFORCED - data validation
> > required, constraint is enforced
>
> There's no such thing as a VALID NOT ENFORCED constraint. It just
> cannot exist.

The document in the patch says
```
If the
constraint is <literal>NOT ENFORCED</literal>, the database system will
not check the constraint. It is then up to the application code to
ensure that the constraints are satisfied. The database system might
still assume that the data actually satisfies the constraint for
optimization decisions where this does not affect the correctness of the
result.
```
If a constraint is NOT VALID, NOT ENFORCED it can't be used for
optimization. Constraints which are VALID, NOT ENFORCED can be used
for optimizatin. That's a correct state if the application is
faithfully making sure that the constraint is satisfied, as suggested
in our documentation. Otherwise, I don't see how NOT ENFORCED
constraints would be useful.

>
> > NOT VALID, NOT ENFORCED changed to NOT_VALID, ENFORCED - no data
> > validation required, constraint is enforced on the new tuples/changes
>
> This may make sense, but it needs special nonstandard syntax. If you
> start with a NOT VALID NOT ENFORCED constraint (which is the only way to
> have a NOT ENFORCED constraint) and apply ALTER TABLE ALTER CONSTRAINT
> ENFORCE, you will end up with a VALID ENFORCED constraint, therefore
> validation must be run.
>
> If you wanted to add a nonstandard command
> ALTER TABLE ALTER CONSTRAINT ENFORCE NO VALIDATE

Which state transition needs it? ALTER TABLE ALTER CONSTRAINT ENFORCE
is enough to change NOT VALID, NOT ENFORCED constraint to NOT VALID,
ENFORCED constraint; it does not need NO VALIDATE.

--
Best Wishes,
Ashutosh Bapat