Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

On Wed, Mar 12, 2025 at 3:52 PM Rushabh Lathia <rushabh(dot)lathia(at)gmail(dot)com> wrote:
>
>
> Hi Alvaro,
>
> Here are the latest patches, which includes the regression fix.
>

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.

We should add more scenarios for constraint inheritance. E.g.
#CREATE TABLE notnull_tbl1 (a int);
#ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn_parent not null a not valid;
#CREATE TABLE notnull_chld (a int);
#ALTER TABLE notnull_chld ADD CONSTRAINT nn_child not null a;
#ALTER TABLE notnull_chld INHERIT notnull_tbl1;
#SELECT conname, convalidated FROM pg_catalog.pg_constraint WHERE conrelid
in ('notnull_tbl1'::regclass, 'notnull_chld'::regclass);
conname | convalidated
-----------+--------------
nn_parent | f
nn_child | t
(2 rows)

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

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.

--
Best Wishes,
Ashutosh Bapat