Horton

Re: NOT ENFORCED constraint feature

amul sul <sulamul@gmail.com>

View thread
From: Amul Sul <sulamul@gmail.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>, Peter Eisentraut <peter@eisentraut.org>, jian he <jian.universality@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>, Joel Jacobson <joel@compiler.org>
Date: 2025-02-03T04:27:18Z
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

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.
>

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 had similar thoughts when working on a patch
before v5, but after further discussion, I now agree that it makes
more sense for the system to keep it as NOT VALID until the data has
been validated against the constraint. This is especially important
when a user adds the constraint, is confident the data complies, and
then needs to enforce it. Validating the data ensures the integrity
and consistency of the constraint.

In short, even if the user is 100% confident, skipping validation is
not an option. We need to make sure the constraint’s VALID status is
accurate and reliable before marking it as validated.

Regards,
Amul