Feedback-ID: ia2694551:Fastmail
Date: Wed, 12 Mar 2025 12:57:57 +0100
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Cc: Rushabh Lathia <rushabh.lathia@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Support NOT VALID / VALIDATE constraint options for named NOT
 NULL constraints
Message-ID: <202503121157.3zabg6m3anwp@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAExHW5uxvZj+_Qa=Oy4tv5FpZ5Z2w+JmV261EJ6sRab=qrNcLQ@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/202503121157.3zabg6m3anwp%40alvherre.pgsql>
Precedence: bulk

On 2025-Mar-12, Ashutosh Bapat wrote:

> The 002_pg_upgrade test passes with and without my patches now. But
> then the tests added here do not leave behind any parent-child table.
> Previously we have found problems in dumping and restoring constraints
> in an inheritance hierarchy. I think the test should leave behind all
> the combinations of parent and child NOT NULL constraints so that
> 0002_pg_upgrade can test those.

I agree.

> Is it expected that a child may have VALID constraint but parent has
> not valid constraint?

Sure.  Consider: if the parent has an unvalidated constraint, we cannot
make any assertions about the state of its children.  The children may
have additional constraints of their own -- in this case, a child can
have a validated constraint even though the parent has none, or only an
unvalidatec constraint.

But the opposite is not allowed: if you know something to be true about
a parent table (to wit: that no row in it is NULL), then this must
necessarily apply to its children as well.  Therefore, if there's a
valid constraint in the parent, then all children must have the same
constraint, and all such constraints must be known valid.

> Same case with partitioned table. We should leave partitioned table
> hierarchy behind for 002_pg_upgrade to test. And we need tests to test
> scenarios where a partitioned table has valid constraint but we try to
> change constraint on a partition to not valid and vice versa. I think
> we shouldn't allow such assymetry in partitioned table hierarchy and
> having a test would be better.

Agreed.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/