Horton

Re: NOT ENFORCED constraint feature

amul sul <sulamul@gmail.com>

View thread
From: Amul Sul <sulamul@gmail.com>
To: Peter Eisentraut <peter@eisentraut.org>
Cc: jian he <jian.universality@gmail.com>, Alvaro Herrera <alvherre@alvh.no-ip.org>, pgsql-hackers <pgsql-hackers@postgresql.org>, Joel Jacobson <joel@compiler.org>
Date: 2025-01-29T12:47:09Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add support for NOT ENFORCED in foreign key constraints

  2. Expand test a bit

  3. refactor: Pass relation OID instead of Relation to createForeignKeyCheckTriggers()

  4. refactor: Split ATExecAlterConstraintInternal()

  5. refactor: Move some code that updates pg_constraint to a separate function

  6. Move RemoveInheritedConstraint() call slightly earlier

  7. refactor: Split tryAttachPartitionForeignKey()

  8. refactor: re-add ATExecAlterChildConstr()

  9. Add ATAlterConstraint struct for ALTER .. CONSTRAINT

  10. refactor: split ATExecAlterConstrRecurse()

  11. Add support for NOT ENFORCED in CHECK constraints

Attachments

On Tue, Jan 28, 2025 at 9:47 PM Peter Eisentraut <peter@eisentraut.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.

I haven't worked on this yet due to a lack of clarity, and it requires
further discussion. The behavior will remain the same as the previous
version. The attached set is the rebased version on top of the latest
master head and includes the aforementioned error message
improvements.

Regards,
Amul