Re: NOT ENFORCED constraint feature

On Wed, Jan 29, 2025 at 6:18 PM Amul Sul <sulamul(at)gmail(dot)com> wrote:
>
> On Tue, Jan 28, 2025 at 9:47 PM Peter Eisentraut <peter(at)eisentraut(dot)org> wrote:
> >
> > > In 0006, this change in the test output should be improved:
> > >
> > > -- XXX: error message is misleading here
> > > ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key ENFORCED;
> > > -ERROR: ALTER CONSTRAINT statement constraints cannot be marked ENFORCED
> > > -LINE 1: ...TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key ENFORCED;
> > > - ^
> > > +ERROR: constraint "unique_tbl_i_key" of relation "unique_tbl" is not a
> > > foreign key constraint
> > >
> > > Maybe this should be along the lines of "ALTER CONSTRAINT ... ENFORCED
> > > is not supported for %s constraints" or something like that.
> > >
> >
> > Ok, let me see what can be done here.
>
> I tried to improve the error message by adding the following details
> for this case in the attached version:
>
> +ERROR: cannot alter enforceability of constraint "unique_tbl_i_key"
> of relation "unique_tbl"
> +DETAIL: Enforceability can only be altered for foreign key constraints.
>
> > On 28.01.25 11:58, Amul Sul wrote:
> > >> This behavior is not correct:
> > >>
> > >> +-- Changing it back to ENFORCED will leave the constraint in the NOT
> > >> VALID state
> > >> +ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_ftest1_fkey ENFORCED;
> > >> +-- Which needs to be explicitly validated.
> > >> +ALTER TABLE FKTABLE VALIDATE CONSTRAINT fktable_ftest1_fkey;
> > >>
> > >> Setting the constraint to enforced should enforce it immediately. This
> > >> SQL statement is covered by the SQL standard. Also, I think it's a
> > >> better user experience if you don't require two steps.
> > >>
> > > Let me clarify: the constraint will be enforced for new inserts and
> > > updates, but it won't be validated against existing data, so those
> > > will remain marked as invalid.
> >
> > Yes, I understand, but that is the not the correct behavior of this
> > command per SQL standard.

If the constraint is VALID and later marked as NOT ENFORCED, changing
it to ENFORCED should also keep it VALID. But if the constraint is NOT
VALID and later marked as NOT ENFORCED, what is expected behaviour
while changing it to ENFORCED? Should it be kept NOT VALID or it
should turn into VALID? I think a user would expect it to be NOT
VALID. When we didn't support the ENFORCED/NOT ENFORCED option, we had
NOT VALID + ENFORCED behaviour.

Now the problem I see is when we set NOT ENFORCED, the constraint is
also set to NOT VALID, which is arguable. When a user sets a
constraint as NOT ENFORCED, it's their responsibility to make sure
that the data still fits the constraint. In that sense the constraint
is VALID and we shouldn't convert it to NOT VALID. We should validate
all the data when changing a NOT ENFORCED, VALID constraint to
ENFORCED, VALID so that the VALID status is reliable.

--
Best Wishes,
Ashutosh Bapat