Horton

Re: NOT ENFORCED constraint feature

amul sul <sulamul@gmail.com>

View thread
From: Amul Sul <sulamul@gmail.com>
To: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Cc: Álvaro Herrera <alvherre@alvh.no-ip.org>, Peter Eisentraut <peter@eisentraut.org>, Isaac Morland <isaac.morland@gmail.com>, jian he <jian.universality@gmail.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>, Joel Jacobson <joel@compiler.org>, Suraj Kharage <suraj.kharage@enterprisedb.com>
Date: 2025-02-17T04:05:56Z
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, Feb 14, 2025 at 8:41 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
>
> On Thu, Feb 13, 2025 at 5:27 PM Álvaro Herrera <alvherre@alvh.no-ip.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