public inbox for [email protected]  

help / color / mirror / Atom feed

From: Amul Sul <[email protected]>
To: Ashutosh Bapat <[email protected]>
Cc: Álvaro Herrera <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: Isaac Morland <[email protected]>
Cc: jian he <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Cc: Joel Jacobson <[email protected]>
Cc: Suraj Kharage <[email protected]>
Subject: Re: NOT ENFORCED constraint feature
Date: Mon, 17 Feb 2025 09:35:56 +0530
Message-ID: <CAAJ_b94Px7mCerHiVxadKgKazOvS-yqV1rg660Z2Rp5Z+wndqw@mail.gmail.com> (raw)
In-Reply-To: <CAExHW5shGBm3CRTjcPbLU4SA0e4S1DJnwu7c_BVvi7Mjjgmo-w@mail.gmail.com>
References: <CAExHW5uNhnBdgncoFFq2hjf3ykAMtebbRjGxr3SxTf+Nb0uO9Q@mail.gmail.com>
	<[email protected]>
	<CAExHW5shGBm3CRTjcPbLU4SA0e4S1DJnwu7c_BVvi7Mjjgmo-w@mail.gmail.com>

On Fri, Feb 14, 2025 at 8:41 PM Ashutosh Bapat
<[email protected]> wrote:
>
> On Thu, Feb 13, 2025 at 5:27 PM Álvaro Herrera <[email protected]> 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

view thread (4+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: NOT ENFORCED constraint feature
  In-Reply-To: <CAAJ_b94Px7mCerHiVxadKgKazOvS-yqV1rg660Z2Rp5Z+wndqw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox