Feedback-ID: ia2694551:Fastmail
Date: Thu, 13 Feb 2025 12:57:03 +0100
From: =?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@alvh.no-ip.org>
To: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Cc: Peter Eisentraut <peter@eisentraut.org>, 
	Isaac Morland <isaac.morland@gmail.com>, Amul Sul <sulamul@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>
Subject: Re: NOT ENFORCED constraint feature
Message-ID: <202502131157.4oqj3eha7yum@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <CAExHW5uNhnBdgncoFFq2hjf3ykAMtebbRjGxr3SxTf+Nb0uO9Q@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/202502131157.4oqj3eha7yum%40alvherre.pgsql>
Precedence: bulk

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?

> I think, what you intend to say is clearer with 4 state system {NE, E}
> * {NV, V} = {(NE, NV), (NE, V), (E, NV), (E, V)} where (NE, V) is
> unreachable. Let's name them S1, S2, S3, S4 respectively.
[...]
> Notice that there are no edges to and from S2.

So why list it as a possible state?

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"Las mujeres son como hondas:  mientras más resistencia tienen,
 más lejos puedes llegar con ellas"  (Jonas Nightingale, Leap of Faith)