Allow NOT NULL constraints to be added as NOT VALID

This allows them to be added without scanning the table, and validating
them afterwards without holding access exclusive lock on the table after
any violating rows have been deleted or fixed.

Doing ALTER TABLE ... SET NOT NULL for a column that has an invalid
not-null constraint validates that constraint.  ALTER TABLE .. VALIDATE
CONSTRAINT is also supported.  There are various checks on whether an
invalid constraint is allowed in a child table when the parent table has
a valid constraint; this should match what we do for enforced/not
enforced constraints.

pg_attribute.attnotnull is now only an indicator for whether a not-null
constraint exists for the column; whether it's valid or invalid must be
queried in pg_constraint.  Applications can continue to query
pg_attribute.attnotnull as before, but now it's possible that NULL rows
are present in the column even when that's set to true.

For backend internal purposes, we cache the nullability status in
CompactAttribute->attnullability that each tuple descriptor carries
(replacing CompactAttribute.attnotnull, which was a mirror of
Form_pg_attribute.attnotnull).  During the initial tuple descriptor
creation, based on the pg_attribute scan, we set this to UNRESTRICTED if
pg_attribute.attnotnull is false, or to UNKNOWN if it's true; then we
update the latter to VALID or INVALID depending on the pg_constraint
scan.  This flag is also copied when tupledescs are copied.

Comparing tuple descs for equality must also compare the
CompactAttribute.attnullability flag and return false in case of a
mismatch.

pg_dump deals with these constraints by storing the OIDs of invalid
not-null constraints in a separate array, and running a query to obtain
their properties.  The regular table creation SQL omits them entirely.
They are then dealt with in the same way as "separate" CHECK
constraints, and dumped after the data has been loaded.  Because no
additional pg_dump infrastructure was required, we don't bump its
version number.

I decided not to bump catversion either, because the old catalog state
works perfectly in the new world.  (Trying to run with new catalog state
and the old server version would likely run into issues, however.)

System catalogs do not support invalid not-null constraints (because
commit 14e87ffa5c54 didn't allow them to have pg_constraint rows
anyway.)

Author: Rushabh Lathia <rushabh.lathia@gmail.com>
Author: Jian He <jian.universality@gmail.com>
Reviewed-by: Álvaro Herrera <alvherre@alvh.no-ip.org>
Tested-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Discussion: https://postgr.es/m/CAGPqQf0KitkNack4F5CFkFi-9Dqvp29Ro=EpcWt=4_hs-Rt+bQ@mail.gmail.com

4 files changed, 504 insertions, 0 deletions

diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 362f38856d2..8a44321034b 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -1202,6 +1202,75 @@ alter table atacc1 alter test_a drop not null, alter test_b drop not null;
 alter table atacc1 add constraint atacc1_constr_b_valid check(test_b is not null);
 alter table atacc1 alter test_b set not null, alter test_a set not null;
 drop table atacc1;
+-- not null not valid with partitions
+CREATE TABLE atnnparted (id int, col1 int) PARTITION BY LIST (id);
+ALTER TABLE atnnparted ADD CONSTRAINT dummy_constr NOT NULL id NOT VALID;
+CREATE TABLE atnnpart1 (col1 int, id int);
+ALTER TABLE atnnpart1 ADD CONSTRAINT another_constr NOT NULL id;
+ALTER TABLE atnnpart1 ADD PRIMARY KEY (id);
+ALTER TABLE atnnparted ATTACH PARTITION atnnpart1 FOR VALUES IN ('1');
+\d+ atnnpart*
+                                 Table "public.atnnpart1"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ col1   | integer |           |          |         | plain   |              | 
+ id     | integer |           | not null |         | plain   |              | 
+Partition of: atnnparted FOR VALUES IN (1)
+Partition constraint: ((id IS NOT NULL) AND (id = 1))
+Indexes:
+    "atnnpart1_pkey" PRIMARY KEY, btree (id)
+Not-null constraints:
+    "another_constr" NOT NULL "id" (inherited)
+
+                 Index "public.atnnpart1_pkey"
+ Column |  Type   | Key? | Definition | Storage | Stats target 
+--------+---------+------+------------+---------+--------------
+ id     | integer | yes  | id         | plain   | 
+primary key, btree, for table "public.atnnpart1"
+
+                          Partitioned table "public.atnnparted"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ id     | integer |           | not null |         | plain   |              | 
+ col1   | integer |           |          |         | plain   |              | 
+Partition key: LIST (id)
+Not-null constraints:
+    "dummy_constr" NOT NULL "id" NOT VALID
+Partitions: atnnpart1 FOR VALUES IN (1)
+
+BEGIN;
+ALTER TABLE atnnparted VALIDATE CONSTRAINT dummy_constr;
+\d+ atnnpart*
+                                 Table "public.atnnpart1"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ col1   | integer |           |          |         | plain   |              | 
+ id     | integer |           | not null |         | plain   |              | 
+Partition of: atnnparted FOR VALUES IN (1)
+Partition constraint: ((id IS NOT NULL) AND (id = 1))
+Indexes:
+    "atnnpart1_pkey" PRIMARY KEY, btree (id)
+Not-null constraints:
+    "another_constr" NOT NULL "id" (inherited)
+
+                 Index "public.atnnpart1_pkey"
+ Column |  Type   | Key? | Definition | Storage | Stats target 
+--------+---------+------+------------+---------+--------------
+ id     | integer | yes  | id         | plain   | 
+primary key, btree, for table "public.atnnpart1"
+
+                          Partitioned table "public.atnnparted"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ id     | integer |           | not null |         | plain   |              | 
+ col1   | integer |           |          |         | plain   |              | 
+Partition key: LIST (id)
+Not-null constraints:
+    "dummy_constr" NOT NULL "id"
+Partitions: atnnpart1 FOR VALUES IN (1)
+
+ROLLBACK;
+-- leave a table in this state for the pg_upgrade test
 -- test inheritance
 create table parent (a int);
 create table child (b varchar(255)) inherits (parent);
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index a719d2f74e9..1346134e23c 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -1229,6 +1229,13 @@ alter table cnn_uq add unique using index cnn_uq_idx;
 Indexes:
     "cnn_uq_idx" UNIQUE CONSTRAINT, btree (a)
 
+-- can't create a primary key on a noinherit not-null
+create table cnn_pk (a int not null no inherit);
+alter table cnn_pk add primary key (a);
+ERROR:  cannot create primary key on column "a"
+DETAIL:  The constraint "cnn_pk_a_not_null" on column "a", marked NO INHERIT, is incompatible with a primary key.
+HINT:  You will need to make it inheritable using ALTER TABLE ... ALTER CONSTRAINT ... INHERIT.
+drop table cnn_pk;
 -- Ensure partitions are scanned for null values when adding a PK
 create table cnn2_parted(a int) partition by list (a);
 create table cnn_part1 partition of cnn2_parted for values in (1, null);
@@ -1355,6 +1362,251 @@ Not-null constraints:
     "ann" NOT NULL "a"
     "bnn" NOT NULL "b"
 
+-- NOT NULL NOT VALID
+PREPARE get_nnconstraint_info(regclass[]) AS
+SELECT conrelid::regclass as tabname, conname, convalidated, conislocal, coninhcount
+FROM  pg_constraint
+WHERE conrelid = ANY($1)
+ORDER BY conrelid::regclass::text, conname;
+CREATE TABLE notnull_tbl1 (a int, b int);
+INSERT INTO notnull_tbl1 VALUES (NULL, 1), (300, 3);
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a; -- error
+ERROR:  column "a" of relation "notnull_tbl1" contains null values
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a NOT VALID; -- ok
+-- even an invalid not-null forbids new nulls
+INSERT INTO notnull_tbl1 VALUES (NULL, 4);
+ERROR:  null value in column "a" of relation "notnull_tbl1" violates not-null constraint
+DETAIL:  Failing row contains (null, 4).
+\d+ notnull_tbl1
+                               Table "public.notnull_tbl1"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a      | integer |           | not null |         | plain   |              | 
+ b      | integer |           |          |         | plain   |              | 
+Not-null constraints:
+    "nn" NOT NULL "a" NOT VALID
+
+-- If we have an invalid constraint, we can't have another
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn1 NOT NULL a NOT VALID NO INHERIT;
+ERROR:  cannot change NO INHERIT status of NOT NULL constraint "nn" on relation "notnull_tbl1"
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a;
+ERROR:  incompatible NOT VALID constraint "nn" on relation "notnull_tbl1"
+HINT:  You will need to use ALTER TABLE ... VALIDATE CONSTRAINT to validate it.
+-- cannot add primary key on a column with an invalid not-null
+ALTER TABLE notnull_tbl1 ADD PRIMARY KEY (a);
+ERROR:  cannot create primary key on column "a"
+DETAIL:  The constraint "nn" on column "a", marked NOT VALID, is incompatible with a primary key.
+HINT:  You will need to validate it using ALTER TABLE ... VALIDATE CONSTRAINT.
+-- ALTER column SET NOT NULL validates an invalid constraint (but this fails
+-- because of rows with null values)
+ALTER TABLE notnull_tbl1 ALTER a SET NOT NULL;
+ERROR:  column "a" of relation "notnull_tbl1" contains null values
+\d+ notnull_tbl1
+                               Table "public.notnull_tbl1"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a      | integer |           | not null |         | plain   |              | 
+ b      | integer |           |          |         | plain   |              | 
+Not-null constraints:
+    "nn" NOT NULL "a" NOT VALID
+
+-- Creating a derived table using LIKE gets the constraint, but it's valid
+CREATE TABLE notnull_tbl1_copy (LIKE notnull_tbl1);
+EXECUTE get_nnconstraint_info('{notnull_tbl1_copy}');
+      tabname      | conname | convalidated | conislocal | coninhcount 
+-------------------+---------+--------------+------------+-------------
+ notnull_tbl1_copy | nn      | t            | t          |           0
+(1 row)
+
+-- An inheritance child table gets the constraint, but it's valid
+CREATE TABLE notnull_tbl1_child (a int, b int) INHERITS (notnull_tbl1);
+NOTICE:  merging column "a" with inherited definition
+NOTICE:  merging column "b" with inherited definition
+EXECUTE get_nnconstraint_info('{notnull_tbl1_child, notnull_tbl1}');
+      tabname       | conname | convalidated | conislocal | coninhcount 
+--------------------+---------+--------------+------------+-------------
+ notnull_tbl1       | nn      | f            | t          |           0
+ notnull_tbl1_child | nn      | t            | f          |           1
+(2 rows)
+
+-- Also try inheritance added after table creation
+CREATE TABLE notnull_tbl1_child2 (c int, b int, a int);
+ALTER TABLE notnull_tbl1_child2 INHERIT notnull_tbl1;	-- nope
+ERROR:  column "a" in child table "notnull_tbl1_child2" must be marked NOT NULL
+ALTER TABLE notnull_tbl1_child2 ADD NOT NULL a NOT VALID;
+ALTER TABLE notnull_tbl1_child2 INHERIT notnull_tbl1;
+EXECUTE get_nnconstraint_info('{notnull_tbl1_child2}');
+       tabname       |            conname             | convalidated | conislocal | coninhcount 
+---------------------+--------------------------------+--------------+------------+-------------
+ notnull_tbl1_child2 | notnull_tbl1_child2_a_not_null | f            | t          |           1
+(1 row)
+
+--table rewrite won't validate invalid constraint
+ALTER TABLE notnull_tbl1 ADD column d float8 default random();
+-- VALIDATE CONSTRAINT scans the table
+ALTER TABLE notnull_tbl1 VALIDATE CONSTRAINT nn; -- error, nulls exist
+ERROR:  column "a" of relation "notnull_tbl1" contains null values
+UPDATE notnull_tbl1 SET a = 100 WHERE b = 1;
+ALTER TABLE notnull_tbl1 VALIDATE CONSTRAINT nn; -- now ok
+EXECUTE get_nnconstraint_info('{notnull_tbl1}');
+   tabname    | conname | convalidated | conislocal | coninhcount 
+--------------+---------+--------------+------------+-------------
+ notnull_tbl1 | nn      | t            | t          |           0
+(1 row)
+
+--- now we can add primary key
+ALTER TABLE notnull_tbl1 ADD PRIMARY KEY (a);
+DROP TABLE notnull_tbl1, notnull_tbl1_child, notnull_tbl1_child2;
+-- dropping an invalid constraint is possible
+CREATE TABLE notnull_tbl1 (a int, b int);
+ALTER TABLE notnull_tbl1 ADD NOT NULL a NOT VALID,
+	ADD NOT NULL b NOT VALID;
+ALTER TABLE notnull_tbl1 ALTER a DROP NOT NULL;
+ALTER TABLE notnull_tbl1 DROP CONSTRAINT notnull_tbl1_b_not_null;
+DROP TABLE notnull_tbl1;
+-- ALTER .. NO INHERIT works for invalid constraints
+CREATE TABLE notnull_tbl1 (a int);
+CREATE TABLE notnull_tbl1_chld () INHERITS (notnull_tbl1);
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nntbl1_a NOT NULL a NOT VALID;
+ALTER TABLE notnull_tbl1 ALTER CONSTRAINT nntbl1_a NO INHERIT;
+-- DROP CONSTRAINT recurses correctly on invalid constraints
+ALTER TABLE notnull_tbl1 ALTER CONSTRAINT nntbl1_a INHERIT;
+ALTER TABLE notnull_tbl1 DROP CONSTRAINT nntbl1_a;
+DROP TABLE notnull_tbl1, notnull_tbl1_chld;
+-- if a parent has a valid not null constraint then a child table cannot
+-- have an invalid one
+CREATE TABLE notnull_tbl1 (a int);
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn_parent NOT NULL a not valid;
+CREATE TABLE notnull_chld0 (a int, CONSTRAINT nn_chld0 NOT NULL a);
+ALTER TABLE notnull_tbl1 INHERIT notnull_chld0; --error
+ERROR:  constraint "nn_parent" conflicts with NOT VALID constraint on child table "notnull_tbl1"
+ALTER TABLE notnull_chld0 DROP CONSTRAINT nn_chld0;
+ALTER TABLE notnull_chld0 ADD CONSTRAINT nn_chld0 NOT NULL a not valid;
+ALTER TABLE notnull_tbl1 INHERIT notnull_chld0; --now ok
+-- parents and child not-null will all be validated.
+ALTER TABLE notnull_tbl1 VALIDATE CONSTRAINT nn_parent;
+EXECUTE get_nnconstraint_info('{notnull_tbl1, notnull_chld0}');
+    tabname    |  conname  | convalidated | conislocal | coninhcount 
+---------------+-----------+--------------+------------+-------------
+ notnull_chld0 | nn_chld0  | f            | t          |           0
+ notnull_tbl1  | nn_parent | t            | t          |           1
+(2 rows)
+
+DROP TABLE notnull_tbl1, notnull_chld0;
+-- Test invalid not null on inheritance table.
+CREATE TABLE notnull_inhparent (i int);
+CREATE TABLE notnull_inhchild (i int) INHERITS (notnull_inhparent);
+NOTICE:  merging column "i" with inherited definition
+CREATE TABLE notnull_inhgrand () INHERITS (notnull_inhparent, notnull_inhchild);
+NOTICE:  merging multiple inherited definitions of column "i"
+ALTER TABLE notnull_inhparent ADD CONSTRAINT nn NOT NULL i NOT VALID;
+ALTER TABLE notnull_inhchild ADD CONSTRAINT nn1 NOT NULL i; -- error
+ERROR:  incompatible NOT VALID constraint "nn" on relation "notnull_inhchild"
+HINT:  You will need to use ALTER TABLE ... VALIDATE CONSTRAINT to validate it.
+EXECUTE get_nnconstraint_info('{notnull_inhparent, notnull_inhchild, notnull_inhgrand}');
+      tabname      | conname | convalidated | conislocal | coninhcount 
+-------------------+---------+--------------+------------+-------------
+ notnull_inhchild  | nn      | f            | f          |           1
+ notnull_inhgrand  | nn      | f            | f          |           2
+ notnull_inhparent | nn      | f            | t          |           0
+(3 rows)
+
+ALTER TABLE notnull_inhparent ALTER i SET NOT NULL; -- ok
+EXECUTE get_nnconstraint_info('{notnull_inhparent, notnull_inhchild, notnull_inhgrand}');
+      tabname      | conname | convalidated | conislocal | coninhcount 
+-------------------+---------+--------------+------------+-------------
+ notnull_inhchild  | nn      | t            | f          |           1
+ notnull_inhgrand  | nn      | t            | f          |           2
+ notnull_inhparent | nn      | t            | t          |           0
+(3 rows)
+
+DROP TABLE notnull_inhparent, notnull_inhchild, notnull_inhgrand;
+-- Verify NOT NULL VALID/NOT VALID with partition table.
+DROP TABLE notnull_tbl1;
+ERROR:  table "notnull_tbl1" does not exist
+CREATE TABLE notnull_tbl1 (a int, b int) PARTITION BY LIST (a);
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT notnull_con NOT NULL a NOT VALID; --ok
+CREATE TABLE notnull_tbl1_1 PARTITION OF notnull_tbl1 FOR VALUES IN (1,2);
+CREATE TABLE notnull_tbl1_2(a int, CONSTRAINT nn2 NOT NULL a, b int);
+ALTER TABLE notnull_tbl1 ATTACH PARTITION notnull_tbl1_2 FOR VALUES IN (3,4);
+CREATE TABLE notnull_tbl1_3(a int, b int);
+INSERT INTO notnull_tbl1_3 values(NULL,1);
+ALTER TABLE notnull_tbl1_3 add CONSTRAINT nn3 NOT NULL a NOT VALID;
+ALTER TABLE notnull_tbl1 ATTACH PARTITION notnull_tbl1_3 FOR VALUES IN (NULL,5);
+EXECUTE get_nnconstraint_info('{notnull_tbl1, notnull_tbl1_1, notnull_tbl1_2, notnull_tbl1_3}');
+    tabname     |   conname   | convalidated | conislocal | coninhcount 
+----------------+-------------+--------------+------------+-------------
+ notnull_tbl1   | notnull_con | f            | t          |           0
+ notnull_tbl1_1 | notnull_con | t            | f          |           1
+ notnull_tbl1_2 | nn2         | t            | f          |           1
+ notnull_tbl1_3 | nn3         | f            | f          |           1
+(4 rows)
+
+ALTER TABLE notnull_tbl1 ALTER COLUMN a SET NOT NULL; --error, notnull_tbl1_3 have null values
+ERROR:  column "a" of relation "notnull_tbl1_3" contains null values
+ALTER TABLE notnull_tbl1_3 VALIDATE CONSTRAINT nn3; --error
+ERROR:  column "a" of relation "notnull_tbl1_3" contains null values
+TRUNCATE notnull_tbl1;
+ALTER TABLE notnull_tbl1 ALTER COLUMN a SET NOT NULL; --OK
+EXECUTE get_nnconstraint_info('{notnull_tbl1, notnull_tbl1_1, notnull_tbl1_2, notnull_tbl1_3}');
+    tabname     |   conname   | convalidated | conislocal | coninhcount 
+----------------+-------------+--------------+------------+-------------
+ notnull_tbl1   | notnull_con | t            | t          |           0
+ notnull_tbl1_1 | notnull_con | t            | f          |           1
+ notnull_tbl1_2 | nn2         | t            | f          |           1
+ notnull_tbl1_3 | nn3         | t            | f          |           1
+(4 rows)
+
+DROP TABLE notnull_tbl1;
+-- partitioned table have not-null, then the partitions can not be NOT NULL NOT VALID.
+CREATE TABLE pp_nn (a int, b int, NOT NULL a) PARTITION BY LIST (a);
+CREATE TABLE pp_nn_1(a int, b int);
+ALTER TABLE pp_nn_1 ADD CONSTRAINT nn1 NOT NULL a NOT VALID;
+ALTER TABLE pp_nn ATTACH PARTITION pp_nn_1 FOR VALUES IN (NULL,5); --error
+ERROR:  constraint "nn1" conflicts with NOT VALID constraint on child table "pp_nn_1"
+ALTER TABLE pp_nn_1 VALIDATE CONSTRAINT nn1;
+ALTER TABLE pp_nn ATTACH PARTITION pp_nn_1 FOR VALUES IN (NULL,5); --ok
+DROP TABLE pp_nn;
+-- Create table with NOT NULL INVALID constraint, for pg_upgrade.
+CREATE TABLE notnull_tbl1_upg (a int, b int);
+INSERT INTO notnull_tbl1_upg VALUES (NULL, 1), (NULL, 2), (300, 3);
+ALTER TABLE notnull_tbl1_upg ADD CONSTRAINT nn NOT NULL a NOT VALID;
+-- Inherit test for pg_upgrade
+CREATE TABLE notnull_parent_upg (a int);
+CREATE TABLE notnull_child_upg () INHERITS (notnull_parent_upg);
+ALTER TABLE notnull_child_upg ADD CONSTRAINT nn NOT NULL a;
+ALTER TABLE notnull_parent_upg ADD CONSTRAINT nn NOT NULL a NOT VALID;
+SELECT conrelid::regclass, contype, convalidated, conislocal
+FROM pg_catalog.pg_constraint
+WHERE conrelid in ('notnull_parent_upg'::regclass, 'notnull_child_upg'::regclass)
+ORDER BY 1;
+      conrelid      | contype | convalidated | conislocal 
+--------------------+---------+--------------+------------
+ notnull_parent_upg | n       | f            | t
+ notnull_child_upg  | n       | t            | t
+(2 rows)
+
+-- Partition table test, for pg_upgrade
+CREATE TABLE notnull_part1_upg (a int, b int) PARTITION BY LIST (a);
+ALTER TABLE notnull_part1_upg ADD CONSTRAINT notnull_con NOT NULL a NOT VALID; --ok
+CREATE TABLE notnull_part1_1_upg PARTITION OF notnull_part1_upg FOR VALUES IN (1,2);
+CREATE TABLE notnull_part1_2_upg (a int, CONSTRAINT nn2 NOT NULL a, b int);
+ALTER TABLE notnull_part1_upg ATTACH PARTITION notnull_part1_2_upg FOR VALUES IN (3,4);
+CREATE TABLE notnull_part1_3_upg (a int, b int);
+INSERT INTO notnull_part1_3_upg values(NULL,1);
+ALTER TABLE notnull_part1_3_upg add CONSTRAINT nn3 NOT NULL a NOT VALID;
+ALTER TABLE notnull_part1_upg ATTACH PARTITION notnull_part1_3_upg FOR VALUES IN (NULL,5);
+EXECUTE get_nnconstraint_info('{notnull_part1_upg, notnull_part1_1_upg, notnull_part1_2_upg, notnull_part1_3_upg}');
+       tabname       |   conname   | convalidated | conislocal | coninhcount 
+---------------------+-------------+--------------+------------+-------------
+ notnull_part1_1_upg | notnull_con | t            | f          |           1
+ notnull_part1_2_upg | nn2         | t            | f          |           1
+ notnull_part1_3_upg | nn3         | f            | f          |           1
+ notnull_part1_upg   | notnull_con | f            | t          |           0
+(4 rows)
+
+DEALLOCATE get_nnconstraint_info;
+-- end NOT NULL NOT VALID
 -- Comments
 -- Setup a low-level role to enforce non-superuser checks.
 CREATE ROLE regress_constraint_comments;
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 84e93ef575e..8432e8e3d54 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -910,6 +910,20 @@ alter table atacc1 add constraint atacc1_constr_b_valid check(test_b is not null
 alter table atacc1 alter test_b set not null, alter test_a set not null;
 drop table atacc1;
 
+-- not null not valid with partitions
+CREATE TABLE atnnparted (id int, col1 int) PARTITION BY LIST (id);
+ALTER TABLE atnnparted ADD CONSTRAINT dummy_constr NOT NULL id NOT VALID;
+CREATE TABLE atnnpart1 (col1 int, id int);
+ALTER TABLE atnnpart1 ADD CONSTRAINT another_constr NOT NULL id;
+ALTER TABLE atnnpart1 ADD PRIMARY KEY (id);
+ALTER TABLE atnnparted ATTACH PARTITION atnnpart1 FOR VALUES IN ('1');
+\d+ atnnpart*
+BEGIN;
+ALTER TABLE atnnparted VALIDATE CONSTRAINT dummy_constr;
+\d+ atnnpart*
+ROLLBACK;
+-- leave a table in this state for the pg_upgrade test
+
 -- test inheritance
 create table parent (a int);
 create table child (b varchar(255)) inherits (parent);
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 21ce4177de4..0f2fe3e6e2d 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -761,6 +761,11 @@ create unique index cnn_uq_idx on cnn_uq (a);
 alter table cnn_uq add unique using index cnn_uq_idx;
 \d+ cnn_uq
 
+-- can't create a primary key on a noinherit not-null
+create table cnn_pk (a int not null no inherit);
+alter table cnn_pk add primary key (a);
+drop table cnn_pk;
+
 -- Ensure partitions are scanned for null values when adding a PK
 create table cnn2_parted(a int) partition by list (a);
 create table cnn_part1 partition of cnn2_parted for values in (1, null);
@@ -801,6 +806,170 @@ ALTER TABLE ONLY notnull_tbl6 DROP CONSTRAINT ann;
 ALTER TABLE ONLY notnull_tbl6 ALTER b DROP NOT NULL;
 \d+ notnull_tbl6_1
 
+
+-- NOT NULL NOT VALID
+PREPARE get_nnconstraint_info(regclass[]) AS
+SELECT conrelid::regclass as tabname, conname, convalidated, conislocal, coninhcount
+FROM  pg_constraint
+WHERE conrelid = ANY($1)
+ORDER BY conrelid::regclass::text, conname;
+
+CREATE TABLE notnull_tbl1 (a int, b int);
+INSERT INTO notnull_tbl1 VALUES (NULL, 1), (300, 3);
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a; -- error
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a NOT VALID; -- ok
+-- even an invalid not-null forbids new nulls
+INSERT INTO notnull_tbl1 VALUES (NULL, 4);
+\d+ notnull_tbl1
+
+-- If we have an invalid constraint, we can't have another
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn1 NOT NULL a NOT VALID NO INHERIT;
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a;
+
+-- cannot add primary key on a column with an invalid not-null
+ALTER TABLE notnull_tbl1 ADD PRIMARY KEY (a);
+
+-- ALTER column SET NOT NULL validates an invalid constraint (but this fails
+-- because of rows with null values)
+ALTER TABLE notnull_tbl1 ALTER a SET NOT NULL;
+\d+ notnull_tbl1
+
+-- Creating a derived table using LIKE gets the constraint, but it's valid
+CREATE TABLE notnull_tbl1_copy (LIKE notnull_tbl1);
+EXECUTE get_nnconstraint_info('{notnull_tbl1_copy}');
+
+-- An inheritance child table gets the constraint, but it's valid
+CREATE TABLE notnull_tbl1_child (a int, b int) INHERITS (notnull_tbl1);
+EXECUTE get_nnconstraint_info('{notnull_tbl1_child, notnull_tbl1}');
+
+-- Also try inheritance added after table creation
+CREATE TABLE notnull_tbl1_child2 (c int, b int, a int);
+ALTER TABLE notnull_tbl1_child2 INHERIT notnull_tbl1;	-- nope
+ALTER TABLE notnull_tbl1_child2 ADD NOT NULL a NOT VALID;
+ALTER TABLE notnull_tbl1_child2 INHERIT notnull_tbl1;
+EXECUTE get_nnconstraint_info('{notnull_tbl1_child2}');
+
+--table rewrite won't validate invalid constraint
+ALTER TABLE notnull_tbl1 ADD column d float8 default random();
+
+-- VALIDATE CONSTRAINT scans the table
+ALTER TABLE notnull_tbl1 VALIDATE CONSTRAINT nn; -- error, nulls exist
+UPDATE notnull_tbl1 SET a = 100 WHERE b = 1;
+ALTER TABLE notnull_tbl1 VALIDATE CONSTRAINT nn; -- now ok
+EXECUTE get_nnconstraint_info('{notnull_tbl1}');
+
+--- now we can add primary key
+ALTER TABLE notnull_tbl1 ADD PRIMARY KEY (a);
+DROP TABLE notnull_tbl1, notnull_tbl1_child, notnull_tbl1_child2;
+
+-- dropping an invalid constraint is possible
+CREATE TABLE notnull_tbl1 (a int, b int);
+ALTER TABLE notnull_tbl1 ADD NOT NULL a NOT VALID,
+	ADD NOT NULL b NOT VALID;
+ALTER TABLE notnull_tbl1 ALTER a DROP NOT NULL;
+ALTER TABLE notnull_tbl1 DROP CONSTRAINT notnull_tbl1_b_not_null;
+DROP TABLE notnull_tbl1;
+
+-- ALTER .. NO INHERIT works for invalid constraints
+CREATE TABLE notnull_tbl1 (a int);
+CREATE TABLE notnull_tbl1_chld () INHERITS (notnull_tbl1);
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nntbl1_a NOT NULL a NOT VALID;
+ALTER TABLE notnull_tbl1 ALTER CONSTRAINT nntbl1_a NO INHERIT;
+
+-- DROP CONSTRAINT recurses correctly on invalid constraints
+ALTER TABLE notnull_tbl1 ALTER CONSTRAINT nntbl1_a INHERIT;
+ALTER TABLE notnull_tbl1 DROP CONSTRAINT nntbl1_a;
+DROP TABLE notnull_tbl1, notnull_tbl1_chld;
+
+-- if a parent has a valid not null constraint then a child table cannot
+-- have an invalid one
+CREATE TABLE notnull_tbl1 (a int);
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn_parent NOT NULL a not valid;
+CREATE TABLE notnull_chld0 (a int, CONSTRAINT nn_chld0 NOT NULL a);
+ALTER TABLE notnull_tbl1 INHERIT notnull_chld0; --error
+
+ALTER TABLE notnull_chld0 DROP CONSTRAINT nn_chld0;
+ALTER TABLE notnull_chld0 ADD CONSTRAINT nn_chld0 NOT NULL a not valid;
+ALTER TABLE notnull_tbl1 INHERIT notnull_chld0; --now ok
+
+-- parents and child not-null will all be validated.
+ALTER TABLE notnull_tbl1 VALIDATE CONSTRAINT nn_parent;
+EXECUTE get_nnconstraint_info('{notnull_tbl1, notnull_chld0}');
+DROP TABLE notnull_tbl1, notnull_chld0;
+
+-- Test invalid not null on inheritance table.
+CREATE TABLE notnull_inhparent (i int);
+CREATE TABLE notnull_inhchild (i int) INHERITS (notnull_inhparent);
+CREATE TABLE notnull_inhgrand () INHERITS (notnull_inhparent, notnull_inhchild);
+ALTER TABLE notnull_inhparent ADD CONSTRAINT nn NOT NULL i NOT VALID;
+ALTER TABLE notnull_inhchild ADD CONSTRAINT nn1 NOT NULL i; -- error
+EXECUTE get_nnconstraint_info('{notnull_inhparent, notnull_inhchild, notnull_inhgrand}');
+ALTER TABLE notnull_inhparent ALTER i SET NOT NULL; -- ok
+EXECUTE get_nnconstraint_info('{notnull_inhparent, notnull_inhchild, notnull_inhgrand}');
+DROP TABLE notnull_inhparent, notnull_inhchild, notnull_inhgrand;
+
+-- Verify NOT NULL VALID/NOT VALID with partition table.
+DROP TABLE notnull_tbl1;
+CREATE TABLE notnull_tbl1 (a int, b int) PARTITION BY LIST (a);
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT notnull_con NOT NULL a NOT VALID; --ok
+CREATE TABLE notnull_tbl1_1 PARTITION OF notnull_tbl1 FOR VALUES IN (1,2);
+CREATE TABLE notnull_tbl1_2(a int, CONSTRAINT nn2 NOT NULL a, b int);
+ALTER TABLE notnull_tbl1 ATTACH PARTITION notnull_tbl1_2 FOR VALUES IN (3,4);
+
+CREATE TABLE notnull_tbl1_3(a int, b int);
+INSERT INTO notnull_tbl1_3 values(NULL,1);
+ALTER TABLE notnull_tbl1_3 add CONSTRAINT nn3 NOT NULL a NOT VALID;
+ALTER TABLE notnull_tbl1 ATTACH PARTITION notnull_tbl1_3 FOR VALUES IN (NULL,5);
+
+EXECUTE get_nnconstraint_info('{notnull_tbl1, notnull_tbl1_1, notnull_tbl1_2, notnull_tbl1_3}');
+ALTER TABLE notnull_tbl1 ALTER COLUMN a SET NOT NULL; --error, notnull_tbl1_3 have null values
+ALTER TABLE notnull_tbl1_3 VALIDATE CONSTRAINT nn3; --error
+
+TRUNCATE notnull_tbl1;
+ALTER TABLE notnull_tbl1 ALTER COLUMN a SET NOT NULL; --OK
+
+EXECUTE get_nnconstraint_info('{notnull_tbl1, notnull_tbl1_1, notnull_tbl1_2, notnull_tbl1_3}');
+DROP TABLE notnull_tbl1;
+
+-- partitioned table have not-null, then the partitions can not be NOT NULL NOT VALID.
+CREATE TABLE pp_nn (a int, b int, NOT NULL a) PARTITION BY LIST (a);
+CREATE TABLE pp_nn_1(a int, b int);
+ALTER TABLE pp_nn_1 ADD CONSTRAINT nn1 NOT NULL a NOT VALID;
+ALTER TABLE pp_nn ATTACH PARTITION pp_nn_1 FOR VALUES IN (NULL,5); --error
+ALTER TABLE pp_nn_1 VALIDATE CONSTRAINT nn1;
+ALTER TABLE pp_nn ATTACH PARTITION pp_nn_1 FOR VALUES IN (NULL,5); --ok
+DROP TABLE pp_nn;
+
+-- Create table with NOT NULL INVALID constraint, for pg_upgrade.
+CREATE TABLE notnull_tbl1_upg (a int, b int);
+INSERT INTO notnull_tbl1_upg VALUES (NULL, 1), (NULL, 2), (300, 3);
+ALTER TABLE notnull_tbl1_upg ADD CONSTRAINT nn NOT NULL a NOT VALID;
+-- Inherit test for pg_upgrade
+CREATE TABLE notnull_parent_upg (a int);
+CREATE TABLE notnull_child_upg () INHERITS (notnull_parent_upg);
+ALTER TABLE notnull_child_upg ADD CONSTRAINT nn NOT NULL a;
+ALTER TABLE notnull_parent_upg ADD CONSTRAINT nn NOT NULL a NOT VALID;
+SELECT conrelid::regclass, contype, convalidated, conislocal
+FROM pg_catalog.pg_constraint
+WHERE conrelid in ('notnull_parent_upg'::regclass, 'notnull_child_upg'::regclass)
+ORDER BY 1;
+
+-- Partition table test, for pg_upgrade
+CREATE TABLE notnull_part1_upg (a int, b int) PARTITION BY LIST (a);
+ALTER TABLE notnull_part1_upg ADD CONSTRAINT notnull_con NOT NULL a NOT VALID; --ok
+CREATE TABLE notnull_part1_1_upg PARTITION OF notnull_part1_upg FOR VALUES IN (1,2);
+CREATE TABLE notnull_part1_2_upg (a int, CONSTRAINT nn2 NOT NULL a, b int);
+ALTER TABLE notnull_part1_upg ATTACH PARTITION notnull_part1_2_upg FOR VALUES IN (3,4);
+CREATE TABLE notnull_part1_3_upg (a int, b int);
+INSERT INTO notnull_part1_3_upg values(NULL,1);
+ALTER TABLE notnull_part1_3_upg add CONSTRAINT nn3 NOT NULL a NOT VALID;
+ALTER TABLE notnull_part1_upg ATTACH PARTITION notnull_part1_3_upg FOR VALUES IN (NULL,5);
+EXECUTE get_nnconstraint_info('{notnull_part1_upg, notnull_part1_1_upg, notnull_part1_2_upg, notnull_part1_3_upg}');
+DEALLOCATE get_nnconstraint_info;
+
+-- end NOT NULL NOT VALID
+
+
 -- Comments
 -- Setup a low-level role to enforce non-superuser checks.
 CREATE ROLE regress_constraint_comments;