0002-Support-NOT-VALID-and-VALIDATE-CONSTRAINT-for-named-.patch

From eb8c8197743a30c558cb208d40d82b66f9c8fc58 Mon Sep 17 00:00:00 2001
From: Rushabh Lathia <rushabh.lathia@enterprisedb.com>
Date: Mon, 10 Feb 2025 16:22:30 +0530
Subject: [PATCH 2/3] Support NOT VALID and VALIDATE CONSTRAINT for named NOT
 NULL constraints.

---
 src/backend/commands/tablecmds.c          | 209 ++++++++++++++++++++++++++++--
 src/backend/executor/execMain.c           |   3 +-
 src/backend/parser/gram.y                 |   4 +-
 src/bin/psql/describe.c                   |  10 +-
 src/include/catalog/pg_attribute.h        |   1 +
 src/test/regress/expected/constraints.out | 110 ++++++++++++++++
 src/test/regress/sql/constraints.sql      |  59 +++++++++
 7 files changed, 380 insertions(+), 16 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 8fa71ed..a889b6b 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -408,6 +408,9 @@ static void QueueFKConstraintValidation(List **wqueue, Relation conrel, Relation
 static void QueueCheckConstraintValidation(List **wqueue, Relation conrel, Relation rel,
 										   char *constrName, HeapTuple contuple,
 										   bool recurse, bool recursing, LOCKMODE lockmode);
+static void QueueNNConstraintValidation(List **wqueue, Relation conrel, Relation rel,
+										char *constrName, HeapTuple contuple,
+										bool recurse, bool recursing, LOCKMODE lockmode);
 static int	transformColumnNameList(Oid relId, List *colList,
 									int16 *attnums, Oid *atttypids, Oid *attcollids);
 static int	transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
@@ -471,7 +474,7 @@ static void add_column_collation_dependency(Oid relid, int32 attnum, Oid collid)
 static ObjectAddress ATExecDropNotNull(Relation rel, const char *colName, bool recurse,
 									   LOCKMODE lockmode);
 static void set_attnotnull(List **wqueue, Relation rel, AttrNumber attnum,
-						   LOCKMODE lockmode);
+						   bool skip_validation, LOCKMODE lockmode);
 static ObjectAddress ATExecSetNotNull(List **wqueue, Relation rel,
 									  char *constrname, char *colName,
 									  bool recurse, bool recursing,
@@ -700,6 +703,8 @@ static List *GetParentedForeignKeyRefs(Relation partition);
 static void ATDetachCheckNoForeignKeyRefs(Relation partition);
 static char GetAttributeCompression(Oid atttypid, const char *compression);
 static char GetAttributeStorage(Oid atttypid, const char *storagemode);
+static bool check_for_invalid_notnull(Oid relid, const char *attname);
+static char *getNNConnameForAttnum(Oid relid, AttrNumber  attnum);
 
 
 /* ----------------------------------------------------------------
@@ -1317,7 +1322,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
 	nncols = AddRelationNotNullConstraints(rel, stmt->nnconstraints,
 										   old_notnulls);
 	foreach_int(attrnum, nncols)
-		set_attnotnull(NULL, rel, attrnum, NoLock);
+		set_attnotnull(NULL, rel, attrnum, false, NoLock);
 
 	ObjectAddressSet(address, RelationRelationId, relationId);
 
@@ -6189,7 +6194,8 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
 		{
 			Form_pg_attribute attr = TupleDescAttr(newTupDesc, i);
 
-			if (attr->attnotnull == ATTRIBUTE_NOTNULL_TRUE &&
+			if ((attr->attnotnull == ATTRIBUTE_NOTNULL_TRUE ||
+				 attr->attnotnull == ATTRIBUTE_NOTNULL_INVALID) &&
 				!attr->attisdropped)
 				notnull_attrs = lappend_int(notnull_attrs, i);
 		}
@@ -7710,7 +7716,7 @@ ATExecDropNotNull(Relation rel, const char *colName, bool recurse,
  */
 static void
 set_attnotnull(List **wqueue, Relation rel, AttrNumber attnum,
-			   LOCKMODE lockmode)
+			   bool skip_validation, LOCKMODE lockmode)
 {
 	Form_pg_attribute attr;
 
@@ -7738,14 +7744,19 @@ set_attnotnull(List **wqueue, Relation rel, AttrNumber attnum,
 
 		attr = (Form_pg_attribute) GETSTRUCT(tuple);
 		Assert(attr->attnotnull == ATTRIBUTE_NOTNULL_FALSE);
-		attr->attnotnull = ATTRIBUTE_NOTNULL_TRUE;
+
+		if (skip_validation)
+			attr->attnotnull = ATTRIBUTE_NOTNULL_INVALID;
+		else
+			attr->attnotnull = ATTRIBUTE_NOTNULL_TRUE;
 		CatalogTupleUpdate(attr_rel, &tuple->t_self, tuple);
 
 		/*
 		 * If the nullness isn't already proven by validated constraints, have
 		 * ALTER TABLE phase 3 test for it.
 		 */
-		if (wqueue && !NotNullImpliedByRelConstraints(rel, attr))
+		if (!skip_validation &&
+			wqueue && !NotNullImpliedByRelConstraints(rel, attr))
 		{
 			AlteredTableInfo *tab;
 
@@ -7914,7 +7925,7 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
 							  RelationGetRelid(rel), attnum);
 
 	/* Mark pg_attribute.attnotnull for the column */
-	set_attnotnull(wqueue, rel, attnum, lockmode);
+	set_attnotnull(wqueue, rel, attnum, constraint->skip_validation, lockmode);
 
 	/*
 	 * Recurse to propagate the constraint to children that don't have one.
@@ -9360,6 +9371,16 @@ ATPrepAddPrimaryKey(List **wqueue, Relation rel, AlterTableCmd *cmd,
 	{
 		AlterTableCmd *newcmd;
 		Constraint *nnconstr;
+		String *colname = lfirst(lc);
+
+		/*
+		 * Throw an error if relation key column has invalid not null
+		 * constraint.
+		 */
+		if (check_for_invalid_notnull(RelationGetRelid(rel), colname->sval))
+			ereport(ERROR,
+					errmsg("column \"%s\" of table \"%s\" is marked as NOT VALID NOT NULL constrint",
+						   colname->sval, RelationGetRelationName(rel)));
 
 		nnconstr = makeNotNullConstraint(lfirst(lc));
 
@@ -9373,6 +9394,30 @@ ATPrepAddPrimaryKey(List **wqueue, Relation rel, AlterTableCmd *cmd,
 }
 
 /*
+ * This function checks for any invalid NOT NULL constraint on the given
+ * relation and attribute name. It returns true if found, false otherwise.
+ */
+static bool
+check_for_invalid_notnull(Oid relid, const char *attname)
+{
+	HeapTuple	tuple;
+	bool		retval = false;
+
+	tuple = SearchSysCache2(ATTNAME,
+							ObjectIdGetDatum(relid),
+							CStringGetDatum(attname));
+	if (!HeapTupleIsValid(tuple))
+		return false;
+	if (!((Form_pg_attribute) GETSTRUCT(tuple))->attisdropped &&
+		((Form_pg_attribute) GETSTRUCT(tuple))->attnotnull == ATTRIBUTE_NOTNULL_INVALID)
+		retval = true;
+
+	ReleaseSysCache(tuple);
+
+	return retval;
+}
+
+/*
  * ALTER TABLE ADD INDEX
  *
  * There is no such command in the grammar, but parse_utilcmd.c converts
@@ -9739,7 +9784,7 @@ ATAddCheckNNConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
 		 * phase 3 to verify existing rows, if needed.
 		 */
 		if (constr->contype == CONSTR_NOTNULL)
-			set_attnotnull(wqueue, rel, ccon->attnum, lockmode);
+			set_attnotnull(wqueue, rel, ccon->attnum, ccon->skip_validation, lockmode);
 
 		ObjectAddressSet(address, ConstraintRelationId, ccon->conoid);
 	}
@@ -12169,7 +12214,8 @@ ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName,
 
 	con = (Form_pg_constraint) GETSTRUCT(tuple);
 	if (con->contype != CONSTRAINT_FOREIGN &&
-		con->contype != CONSTRAINT_CHECK)
+		con->contype != CONSTRAINT_CHECK &&
+		con->contype != CONSTRAINT_NOTNULL)
 		ereport(ERROR,
 				(errcode(ERRCODE_WRONG_OBJECT_TYPE),
 				 errmsg("constraint \"%s\" of relation \"%s\" is not a foreign key or check constraint",
@@ -12191,6 +12237,11 @@ ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName,
 			QueueCheckConstraintValidation(wqueue, conrel, rel, constrName,
 										   tuple, recurse, recursing, lockmode);
 		}
+		else if (con->contype == CONSTRAINT_NOTNULL)
+		{
+			QueueNNConstraintValidation(wqueue, conrel, rel, constrName,
+										tuple, recurse, recursing, lockmode);
+		}
 
 		ObjectAddressSet(address, ConstraintRelationId, con->oid);
 	}
@@ -12408,6 +12459,146 @@ QueueCheckConstraintValidation(List **wqueue, Relation conrel, Relation rel,
 }
 
 /*
+ * QueueNNConstraintValidation
+ *
+ * Add an entry to the wqueue to validate the given notnull constraint in Phase 3
+ * and update the convalidated field in the pg_constraint catalog for the
+ * specified relation and all its inheriting children.
+ */
+static void
+QueueNNConstraintValidation(List **wqueue, Relation conrel, Relation rel,
+						  char *constrName, HeapTuple contuple,
+						  bool recurse, bool recursing, LOCKMODE lockmode)
+{
+	Form_pg_constraint con;
+	AlteredTableInfo *tab;
+	HeapTuple	copyTuple;
+	Form_pg_constraint copy_con;
+
+	List	   *children = NIL;
+	ListCell   *child;
+	AttrNumber  attnum;
+
+	con = (Form_pg_constraint) GETSTRUCT(contuple);
+	Assert(con->contype == CONSTRAINT_NOTNULL);
+
+	attnum = extractNotNullColumn(contuple);
+
+	/*
+	 * If we're recursing, the parent has already done this, so skip it. Also,
+	 * if the constraint is a NO INHERIT constraint, we shouldn't try to look
+	 * for it in the children.
+	 */
+	if (!recursing && !con->connoinherit)
+		children = find_all_inheritors(RelationGetRelid(rel),
+									   lockmode, NULL);
+
+	/*
+	 * For CHECK constraints, we must ensure that we only mark the constraint
+	 * as validated on the parent if it's already validated on the children.
+	 *
+	 * We recurse before validating on the parent, to reduce risk of
+	 * deadlocks.
+	 */
+	foreach(child, children)
+	{
+		Oid			childoid = lfirst_oid(child);
+		Relation	childrel;
+		char	   *conname;
+
+		if (childoid == RelationGetRelid(rel))
+			continue;
+
+		/*
+		 * If we are told not to recurse, there had better not be any child
+		 * tables, because we can't mark the constraint on the parent valid
+		 * unless it is valid for all child tables.
+		 */
+		if (!recurse)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+					 errmsg("constraint must be validated on child tables too")));
+
+		/* find_all_inheritors already got lock */
+		childrel = table_open(childoid, NoLock);
+		conname = getNNConnameForAttnum(childoid, attnum);
+		if (conname == NULL)
+			continue;
+
+		ATExecValidateConstraint(wqueue, childrel, conname,
+								 false, true, lockmode);
+		table_close(childrel, NoLock);
+	}
+
+
+	tab = ATGetQueueEntry(wqueue, rel);
+	tab->verify_new_notnull = true;
+
+	/*
+	 * Invalidate relcache so that others see the new validated constraint.
+	 */
+	CacheInvalidateRelcache(rel);
+
+	/*
+	 * Now update the catalog, while we have the door open.
+	 */
+	copyTuple = heap_copytuple(contuple);
+	copy_con = (Form_pg_constraint) GETSTRUCT(copyTuple);
+	copy_con->convalidated = true;
+	CatalogTupleUpdate(conrel, &copyTuple->t_self, copyTuple);
+
+	InvokeObjectPostAlterHook(ConstraintRelationId, con->oid, 0);
+
+	heap_freetuple(copyTuple);
+}
+
+/*
+ * Function returns the invalid not null constrint name for the given
+ * relation and attnumber.
+ */
+static char *
+getNNConnameForAttnum(Oid relid, AttrNumber  attnum)
+{
+	Relation	constrRel;
+	HeapTuple	htup;
+	SysScanDesc conscan;
+	ScanKeyData skey;
+	char	   *conname = NULL;
+
+	constrRel = table_open(ConstraintRelationId, AccessShareLock);
+	ScanKeyInit(&skey,
+				Anum_pg_constraint_conrelid,
+				BTEqualStrategyNumber, F_OIDEQ,
+				ObjectIdGetDatum(relid));
+	conscan = systable_beginscan(constrRel, ConstraintRelidTypidNameIndexId, true,
+								 NULL, 1, &skey);
+
+	while (HeapTupleIsValid(htup = systable_getnext(conscan)))
+	{
+		Form_pg_constraint conForm = (Form_pg_constraint) GETSTRUCT(htup);
+		AttrNumber	colnum;
+
+		if (conForm->contype != CONSTRAINT_NOTNULL)
+			continue;
+		if (conForm->convalidated == true)
+			continue;
+
+		colnum = extractNotNullColumn(htup);
+
+		if (colnum != attnum)
+			continue;
+
+		conname = pstrdup(NameStr(conForm->conname));
+		break;
+	}
+
+	systable_endscan(conscan);
+	table_close(constrRel, AccessShareLock);
+
+	return conname;
+}
+
+/*
  * transformColumnNameList - transform list of column names
  *
  * Lookup each name and return its attnum and, optionally, type and collation
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 06688ff..d2cf422 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -1966,7 +1966,8 @@ ExecConstraints(ResultRelInfo *resultRelInfo,
 		{
 			Form_pg_attribute att = TupleDescAttr(tupdesc, attrChk - 1);
 
-			if (att->attnotnull == ATTRIBUTE_NOTNULL_TRUE &&
+			if ((att->attnotnull == ATTRIBUTE_NOTNULL_TRUE ||
+				 att->attnotnull == ATTRIBUTE_NOTNULL_INVALID) &&
 				slot_attisnull(slot, attrChk))
 			{
 				char	   *val_desc;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index d388762..bfbf752 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4196,9 +4196,9 @@ ConstraintElem:
 					n->keys = list_make1(makeString($3));
 					/* no NOT VALID support yet */
 					processCASbits($4, @4, "NOT NULL",
-								   NULL, NULL, NULL, NULL,
+								   NULL, NULL, NULL, &n->skip_validation,
 								   &n->is_no_inherit, yyscanner);
-					n->initially_valid = true;
+					n->initially_valid = !n->skip_validation;
 					$$ = (Node *) n;
 				}
 			| UNIQUE opt_unique_null_treatment '(' columnList opt_without_overlaps ')' opt_c_include opt_definition OptConsTableSpace
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 3b7ba66..583b5ac 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2107,7 +2107,7 @@ describeOneTableDetails(const char *schemaname,
 			printTableAddCell(&cont, PQgetvalue(res, i, attcoll_col), false, false);
 
 			printTableAddCell(&cont,
-							  strcmp(PQgetvalue(res, i, attnotnull_col), "t") == 0 ? "not null" : "",
+							  strcmp(PQgetvalue(res, i, attnotnull_col), "f") == 0 ? "" : "not null",
 							  false, false);
 
 			identity = PQgetvalue(res, i, attidentity_col);
@@ -3114,7 +3114,7 @@ describeOneTableDetails(const char *schemaname,
 		{
 			printfPQExpBuffer(&buf,
 							  "SELECT c.conname, a.attname, c.connoinherit,\n"
-							  "  c.conislocal, c.coninhcount <> 0\n"
+							  "  c.conislocal, c.coninhcount <> 0, c.convalidated \n"
 							  "FROM pg_catalog.pg_constraint c JOIN\n"
 							  "  pg_catalog.pg_attribute a ON\n"
 							  "    (a.attrelid = c.conrelid AND a.attnum = c.conkey[1])\n"
@@ -3138,13 +3138,15 @@ describeOneTableDetails(const char *schemaname,
 				bool		islocal = PQgetvalue(result, i, 3)[0] == 't';
 				bool		inherited = PQgetvalue(result, i, 4)[0] == 't';
 
-				printfPQExpBuffer(&buf, "    \"%s\" NOT NULL \"%s\"%s",
+				printfPQExpBuffer(&buf, "    \"%s\" NOT NULL \"%s\"%s%s",
 								  PQgetvalue(result, i, 0),
 								  PQgetvalue(result, i, 1),
 								  PQgetvalue(result, i, 2)[0] == 't' ?
 								  " NO INHERIT" :
 								  islocal && inherited ? _(" (local, inherited)") :
-								  inherited ? _(" (inherited)") : "");
+								  inherited ? _(" (inherited)") : "",
+								  PQgetvalue(result, i, 5)[0] == 'f' ?
+								  " NOT VALID " : "");
 
 				printTableAddFooter(&cont, buf.data);
 			}
diff --git a/src/include/catalog/pg_attribute.h b/src/include/catalog/pg_attribute.h
index b51a267..a01a0fa 100644
--- a/src/include/catalog/pg_attribute.h
+++ b/src/include/catalog/pg_attribute.h
@@ -229,6 +229,7 @@ MAKE_SYSCACHE(ATTNUM, pg_attribute_relid_attnum_index, 128);
 
 #define		  ATTRIBUTE_NOTNULL_TRUE		't'
 #define		  ATTRIBUTE_NOTNULL_FALSE		'f'
+#define		  ATTRIBUTE_NOTNULL_INVALID		'i'
 
 #endif							/* EXPOSE_TO_CLIENT_CODE */
 
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index 692a69f..a16422b 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -897,6 +897,116 @@ Not-null constraints:
     "foobar" NOT NULL "a"
 
 DROP TABLE notnull_tbl1;
+-- verify NOT NULL VALID/NOT VALID
+CREATE TABLE notnull_tbl1 (a INTEGER, b INTEGER);
+INSERT INTO notnull_tbl1 VALUES (NULL, 1);
+INSERT INTO notnull_tbl1 VALUES (NULL, 2);
+INSERT INTO notnull_tbl1 VALUES (300, 3);
+-- Below statement should throw an error
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a;
+ERROR:  column "a" of relation "notnull_tbl1" contains null values
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a NOT VALID;
+\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 
+
+-- Try to insert new record with NULL, should throw an error
+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).
+-- SELECT NULL values for COLUMN a, should return 2 records.
+SELECT * FROM notnull_tbl1 WHERE a is NULL;
+ a | b 
+---+---
+   | 1
+   | 2
+(2 rows)
+
+-- UPDATE the one of the NULL values
+UPDATE notnull_tbl1 SET a = 100 WHERE b = 1;
+-- DELETE the record (NULL, 2)
+DELETE FROM notnull_tbl1 WHERE b = 2;
+SELECT * FROM notnull_tbl1;
+  a  | b 
+-----+---
+ 300 | 3
+ 100 | 1
+(2 rows)
+
+-- Try to add primary key on table column marked as NOT VALID NOT NULL
+-- constraint. This should throw an error.
+ALTER TABLE notnull_tbl1 add primary key (a);
+ERROR:  column "a" of table "notnull_tbl1" is marked as NOT VALID NOT NULL constrint
+-- INHERITS table having NOT VALID NOT NULL constraints.
+CREATE TABLE notnull_tbl1_child(a INTEGER, b INTEGER) INHERITS(notnull_tbl1);
+NOTICE:  merging column "a" with inherited definition
+NOTICE:  merging column "b" with inherited definition
+-- Child table NOT NULL constraints should be valid.
+SELECT conname, convalidated FROM pg_catalog.pg_constraint WHERE conrelid = 'notnull_tbl1_child'::regclass;
+ conname | convalidated 
+---------+--------------
+ nn      | t
+(1 row)
+
+DROP TABLE notnull_tbl1_child;
+ALTER TABLE notnull_tbl1 validate constraint nn;
+\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"
+
+DROP TABLE notnull_tbl1;
+-- Test the different Not null constrint name for parent and child table
+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 not valid;
+ALTER TABLE notnull_chld INHERIT notnull_tbl1;
+-- This statement should validate not null constrint for parent as well as
+-- child.
+ALTER TABLE notnull_tbl1 VALIDATE CONSTRAINT nn_parent;
+SELECT conname, convalidated FROM pg_catalog.pg_constraint WHERE conrelid
+in ('notnull_tbl1'::regclass, 'notnull_chld'::regclass);
+  conname  | convalidated 
+-----------+--------------
+ nn_parent | t
+ nn_child  | t
+(2 rows)
+
+DROP TABLE notnull_tbl1 CASCADE;
+NOTICE:  drop cascades to table notnull_chld
+--Create table with NOT NULL INVALID constrint, for pg_upgrade.
+CREATE TABLE notnull_tbl1_upg (a INTEGER, b INTEGER);
+INSERT INTO notnull_tbl1_upg VALUES (NULL, 1);
+INSERT INTO notnull_tbl1_upg VALUES (NULL, 2);
+INSERT INTO notnull_tbl1_upg VALUES (300, 3);
+ALTER TABLE notnull_tbl1_upg ADD CONSTRAINT nn NOT NULL a NOT VALID;
+-- Verify NOT NULL VALID/NOT VALID with partition table.
+CREATE TABLE notnull_tbl1 (a INTEGER, b INTEGER) PARTITION BY RANGE (a);
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT notnull_con NOT NULL a NOT VALID;
+CREATE TABLE notnull_tbl1_1 PARTITION OF notnull_tbl1 FOR VALUES FROM (0) TO (10);
+CREATE TABLE notnull_tbl1_2 PARTITION OF notnull_tbl1 FOR VALUES FROM (20) TO (30);
+-- Parent table NOT NULL constraints will be market as validated false, where
+-- for child table it will be true
+SELECT conrelid::regclass, conname, convalidated FROM pg_catalog.pg_constraint WHERE
+conrelid IN ('notnull_tbl1'::regclass, 'notnull_tbl1_1'::regclass, 'notnull_tbl1_2'::regclass);
+    conrelid    |   conname   | convalidated 
+----------------+-------------+--------------
+ notnull_tbl1   | notnull_con | f
+ notnull_tbl1_1 | notnull_con | t
+ notnull_tbl1_2 | notnull_con | t
+(3 rows)
+
+DROP TABLE notnull_tbl1, notnull_tbl1_1, notnull_tbl1_2;
 -- Verify that constraint names and NO INHERIT are properly considered when
 -- multiple constraint are specified, either explicitly or via SERIAL/PK/etc,
 -- and that conflicting cases are rejected.  Mind that table constraints
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index d6742f8..b62d8c6 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -641,6 +641,65 @@ ALTER TABLE notnull_tbl1 ADD CONSTRAINT foobar NOT NULL a;
 \d+ notnull_tbl1
 DROP TABLE notnull_tbl1;
 
+-- verify NOT NULL VALID/NOT VALID
+CREATE TABLE notnull_tbl1 (a INTEGER, b INTEGER);
+INSERT INTO notnull_tbl1 VALUES (NULL, 1);
+INSERT INTO notnull_tbl1 VALUES (NULL, 2);
+INSERT INTO notnull_tbl1 VALUES (300, 3);
+-- Below statement should throw an error
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a;
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a NOT VALID;
+\d+ notnull_tbl1
+-- Try to insert new record with NULL, should throw an error
+INSERT INTO notnull_tbl1 VALUES (NULL, 4);
+-- SELECT NULL values for COLUMN a, should return 2 records.
+SELECT * FROM notnull_tbl1 WHERE a is NULL;
+-- UPDATE the one of the NULL values
+UPDATE notnull_tbl1 SET a = 100 WHERE b = 1;
+-- DELETE the record (NULL, 2)
+DELETE FROM notnull_tbl1 WHERE b = 2;
+SELECT * FROM notnull_tbl1;
+-- Try to add primary key on table column marked as NOT VALID NOT NULL
+-- constraint. This should throw an error.
+ALTER TABLE notnull_tbl1 add primary key (a);
+-- INHERITS table having NOT VALID NOT NULL constraints.
+CREATE TABLE notnull_tbl1_child(a INTEGER, b INTEGER) INHERITS(notnull_tbl1);
+-- Child table NOT NULL constraints should be valid.
+SELECT conname, convalidated FROM pg_catalog.pg_constraint WHERE conrelid = 'notnull_tbl1_child'::regclass;
+DROP TABLE notnull_tbl1_child;
+ALTER TABLE notnull_tbl1 validate constraint nn;
+\d+ notnull_tbl1
+DROP TABLE notnull_tbl1;
+-- Test the different Not null constrint name for parent and child table
+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 not valid;
+ALTER TABLE notnull_chld INHERIT notnull_tbl1;
+-- This statement should validate not null constrint for parent as well as
+-- child.
+ALTER TABLE notnull_tbl1 VALIDATE CONSTRAINT nn_parent;
+SELECT conname, convalidated FROM pg_catalog.pg_constraint WHERE conrelid
+in ('notnull_tbl1'::regclass, 'notnull_chld'::regclass);
+DROP TABLE notnull_tbl1 CASCADE;
+--Create table with NOT NULL INVALID constrint, for pg_upgrade.
+CREATE TABLE notnull_tbl1_upg (a INTEGER, b INTEGER);
+INSERT INTO notnull_tbl1_upg VALUES (NULL, 1);
+INSERT INTO notnull_tbl1_upg VALUES (NULL, 2);
+INSERT INTO notnull_tbl1_upg VALUES (300, 3);
+ALTER TABLE notnull_tbl1_upg ADD CONSTRAINT nn NOT NULL a NOT VALID;
+
+-- Verify NOT NULL VALID/NOT VALID with partition table.
+CREATE TABLE notnull_tbl1 (a INTEGER, b INTEGER) PARTITION BY RANGE (a);
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT notnull_con NOT NULL a NOT VALID;
+CREATE TABLE notnull_tbl1_1 PARTITION OF notnull_tbl1 FOR VALUES FROM (0) TO (10);
+CREATE TABLE notnull_tbl1_2 PARTITION OF notnull_tbl1 FOR VALUES FROM (20) TO (30);
+-- Parent table NOT NULL constraints will be market as validated false, where
+-- for child table it will be true
+SELECT conrelid::regclass, conname, convalidated FROM pg_catalog.pg_constraint WHERE
+conrelid IN ('notnull_tbl1'::regclass, 'notnull_tbl1_1'::regclass, 'notnull_tbl1_2'::regclass);
+DROP TABLE notnull_tbl1, notnull_tbl1_1, notnull_tbl1_2;
+
 -- Verify that constraint names and NO INHERIT are properly considered when
 -- multiple constraint are specified, either explicitly or via SERIAL/PK/etc,
 -- and that conflicting cases are rejected.  Mind that table constraints
-- 
1.8.3.1