pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Amit Langote <amitlangote09@gmail.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: cataloguing NOT NULL constraints
Date: Fri, 9 Sep 2022 19:58:13 +0200
Message-ID: <20220909175813.vu5kd2m67mrrqkjv@alvherre.pgsql> (raw)
In-Reply-To: <20220831221910.uujmlltbaazplqmr@alvherre.pgsql>

There were a lot more problems in that submission than I at first
realized, and I had to rewrite a lot of code in order to fix them.  I
have fixed all the user-visible problems I found in this version, and
reviewed the tests results more carefully so I am now more confident
that behaviourally it's doing the right thing; but

1. the pg_upgrade test problem is still unaddressed,
2. I haven't verified that catalog contents is correct, especially
   regarding dependencies,
3. there are way too many XXX and FIXME comments sprinkled everywhere.

I'm sure a couple of these XXX comments can be left for later work, and
there's a few that should be dealt with by merely removing them; but the
others (and all FIXMEs) represent pending work.

Also, I'm not at all happy about having this new ConstraintNotNull
artificial node there; perhaps this can be solved by using a regular
Constraint with some new flag, or maybe it will even work without any
extra flags by the fact that the node appears where it appears.  Anyway,
requires investigation.  Also, the AT_SetAttNotNull continues to irk me.

test_ddl_deparse is also unhappy.  This is probably an easy fix;
apparently, ATExecDropConstraint has been doing things wrong forever.

Anyway, here's version 2 of this, with apologies for those who spent
time reviewing version 1 with all its brokenness.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"On the other flipper, one wrong move and we're Fatal Exceptions"
(T.U.X.: Term Unit X  - http://www.thelinuxreview.com/TUX/)

Attachments:

notnull-constraints-2.patch (text/x-diff, 179.0K) download
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 7bf35602b0..576a034455 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -8933,6 +8933,8 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1;
 --------+-------------------+-----------+----------+---------+--------------------
  c1     | integer           |           |          |         | (column_name 'c1')
  c2     | character varying |           | not null |         | (column_name 'c2')
+Check constraints:
+    "t1_c2_not_null" CHECK (c2 IS NOT NULL)
 Server: loopback
 FDW options: (schema_name 'import_source', table_name 't1')
 
@@ -9006,6 +9008,8 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2
 --------+-------------------+-----------+----------+---------+--------------------
  c1     | integer           |           |          |         | (column_name 'c1')
  c2     | character varying |           | not null |         | (column_name 'c2')
+Check constraints:
+    "t1_c2_not_null" CHECK (c2 IS NOT NULL)
 Server: loopback
 FDW options: (schema_name 'import_source', table_name 't1')
 
diff --git a/contrib/test_decoding/expected/ddl.out b/contrib/test_decoding/expected/ddl.out
index 9a28b5ddc5..09e135a9da 100644
--- a/contrib/test_decoding/expected/ddl.out
+++ b/contrib/test_decoding/expected/ddl.out
@@ -492,6 +492,9 @@ WITH (user_catalog_table = true)
  options  | text[]  |           |          |                                                  | extended |              | 
 Indexes:
     "replication_metadata_pkey" PRIMARY KEY, btree (id)
+Check constraints:
+    "replication_metadata_id_not_null" CHECK (id IS NOT NULL)
+    "replication_metadata_relation_not_null" CHECK (relation IS NOT NULL)
 Options: user_catalog_table=true
 
 INSERT INTO replication_metadata(relation, options)
@@ -506,6 +509,9 @@ ALTER TABLE replication_metadata RESET (user_catalog_table);
  options  | text[]  |           |          |                                                  | extended |              | 
 Indexes:
     "replication_metadata_pkey" PRIMARY KEY, btree (id)
+Check constraints:
+    "replication_metadata_id_not_null" CHECK (id IS NOT NULL)
+    "replication_metadata_relation_not_null" CHECK (relation IS NOT NULL)
 
 INSERT INTO replication_metadata(relation, options)
 VALUES ('bar', ARRAY['a', 'b']);
@@ -519,6 +525,9 @@ ALTER TABLE replication_metadata SET (user_catalog_table = true);
  options  | text[]  |           |          |                                                  | extended |              | 
 Indexes:
     "replication_metadata_pkey" PRIMARY KEY, btree (id)
+Check constraints:
+    "replication_metadata_id_not_null" CHECK (id IS NOT NULL)
+    "replication_metadata_relation_not_null" CHECK (relation IS NOT NULL)
 Options: user_catalog_table=true
 
 INSERT INTO replication_metadata(relation, options)
@@ -538,6 +547,9 @@ ALTER TABLE replication_metadata SET (user_catalog_table = false);
  rewritemeornot | integer |           |          |                                                  | plain    |              | 
 Indexes:
     "replication_metadata_pkey" PRIMARY KEY, btree (id)
+Check constraints:
+    "replication_metadata_id_not_null" CHECK (id IS NOT NULL)
+    "replication_metadata_relation_not_null" CHECK (relation IS NOT NULL)
 Options: user_catalog_table=false
 
 INSERT INTO replication_metadata(relation, options)
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 9b03579e6e..09fe18cf7a 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -101,6 +101,7 @@ static ObjectAddress AddNewRelationType(const char *typeName,
 										Oid new_array_type);
 static void RelationRemoveInheritance(Oid relid);
 static Oid	StoreRelCheck(Relation rel, const char *ccname, Node *expr,
+						  Oid parent_oid,
 						  bool is_validated, bool is_local, int inhcount,
 						  bool is_no_inherit, bool is_internal);
 static void StoreConstraints(Relation rel, List *cooked_constraints,
@@ -2061,7 +2062,7 @@ SetAttrMissing(Oid relid, char *attname, char *value)
  * The OID of the new constraint is returned.
  */
 static Oid
-StoreRelCheck(Relation rel, const char *ccname, Node *expr,
+StoreRelCheck(Relation rel, const char *ccname, Node *expr, Oid parent_oid,
 			  bool is_validated, bool is_local, int inhcount,
 			  bool is_no_inherit, bool is_internal)
 {
@@ -2129,7 +2130,7 @@ StoreRelCheck(Relation rel, const char *ccname, Node *expr,
 							  false,	/* Is Deferrable */
 							  false,	/* Is Deferred */
 							  is_validated,
-							  InvalidOid,	/* no parent constraint */
+							  parent_oid,
 							  RelationGetRelid(rel),	/* relation */
 							  attNos,	/* attrs in the constraint */
 							  keycount, /* # key attrs in the constraint */
@@ -2198,7 +2199,7 @@ StoreConstraints(Relation rel, List *cooked_constraints, bool is_internal)
 				break;
 			case CONSTR_CHECK:
 				con->conoid =
-					StoreRelCheck(rel, con->name, con->expr,
+					StoreRelCheck(rel, con->name, con->expr, con->parent_oid,
 								  !con->skip_validation, con->is_local,
 								  con->inhcount, con->is_no_inherit,
 								  is_internal);
@@ -2403,6 +2404,7 @@ AddRelationNewConstraints(Relation rel,
 			 * (We omit the duplicate constraint from the result, which is
 			 * what ATAddCheckConstraint wants.)
 			 */
+			/* XXX need to handle this case? */
 			if (MergeWithExistingConstraint(rel, ccname, expr,
 											allow_merge, is_local,
 											cdef->initially_valid,
@@ -2452,8 +2454,9 @@ AddRelationNewConstraints(Relation rel,
 		 * OK, store it.
 		 */
 		constrOid =
-			StoreRelCheck(rel, ccname, expr, cdef->initially_valid, is_local,
-						  is_local ? 0 : 1, cdef->is_no_inherit, is_internal);
+			StoreRelCheck(rel, ccname, expr, cdef->parent_oid, cdef->initially_valid,
+						  is_local, is_local ? 0 : 1, cdef->is_no_inherit,
+						  is_internal);
 
 		numchecks++;
 
@@ -2461,6 +2464,7 @@ AddRelationNewConstraints(Relation rel,
 		cooked->contype = CONSTR_CHECK;
 		cooked->conoid = constrOid;
 		cooked->name = ccname;
+		cooked->parent_oid = cdef->parent_oid;
 		cooked->attnum = 0;
 		cooked->expr = expr;
 		cooked->skip_validation = cdef->skip_validation;
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index bb65fb1e0a..283eb8ac68 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -26,6 +26,7 @@
 #include "catalog/pg_constraint.h"
 #include "catalog/pg_operator.h"
 #include "catalog/pg_type.h"
+#include "commands/constraint.h"
 #include "commands/defrem.h"
 #include "commands/tablecmds.h"
 #include "utils/array.h"
@@ -562,6 +563,124 @@ ChooseConstraintName(const char *name1, const char *name2,
 	return conname;
 }
 
+/*
+ * Find and return the pg_constraint tuple that implements a validated
+ * NOT NULL constraint for the given column of the given relation.
+ *
+ * If there's more than one such constraint and *multiple is not NULL,
+ * we set that true.
+ *
+ * XXX This would be much easier if we had pg_attribute.notnullconstr with the
+ * OID of the constraint that implements the NOT NULL constraint for that
+ * column.  I'm not sure it's worth the catalog bloat and de-normalization,
+ * however.
+ */
+HeapTuple
+findNotNullConstraintAttnum(Relation rel, AttrNumber attnum, bool *multiple)
+{
+	Relation	pg_constraint;
+	HeapTuple	conTup,
+				retval = NULL;
+	SysScanDesc	scan;
+	ScanKeyData	key;
+
+	if (multiple)
+		*multiple = false;
+
+	pg_constraint = table_open(ConstraintRelationId, AccessShareLock);
+	ScanKeyInit(&key,
+				Anum_pg_constraint_conrelid,
+				BTEqualStrategyNumber, F_OIDEQ,
+				ObjectIdGetDatum(RelationGetRelid(rel)));
+	scan = systable_beginscan(pg_constraint, ConstraintRelidTypidNameIndexId,
+							  true, NULL, 1, &key);
+
+	while (HeapTupleIsValid(conTup = systable_getnext(scan)))
+	{
+		Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(conTup);
+		AttrNumber	conkey;
+		AttrNumber	check_attnum;
+		ArrayType  *arr;
+		Datum		adatum;
+		bool		isnull;
+
+		/*
+		 * We're looking for a CHECK constraint that's marked validated, with
+		 * the column we're looking for as the sole element in conkey, and
+		 * from whose expression our NOT NULL extractor returns a column name.
+		 * (We verify only in an assertion that that column is in fact the one
+		 * we want, because that seems a redundant check.)
+		 */
+		if (con->contype != CONSTRAINT_CHECK)
+			continue;
+
+		if (!con->convalidated)
+			continue;
+
+		adatum = SysCacheGetAttr(CONSTROID, conTup,
+								 Anum_pg_constraint_conkey, &isnull);
+		if (isnull)
+			continue;
+		arr = DatumGetArrayTypeP(adatum);
+		if (ARR_NDIM(arr) != 1 ||
+			ARR_HASNULL(arr) ||
+			ARR_ELEMTYPE(arr) != INT2OID)
+			elog(ERROR, "conkey is not a 1-D smallint array");
+		if (ARR_DIMS(arr)[0] != 1)
+			goto nope;
+
+		memcpy(&conkey, ARR_DATA_PTR(arr), sizeof(int16));
+		if (conkey != attnum)
+			goto nope;
+
+		check_attnum = tryExtractNotNullFromCatalog(conTup);
+		if (check_attnum == InvalidAttrNumber)
+			goto nope;
+
+		/*
+		 * Surely tryExtractNotNullFromCatalog won't give us a mismatching
+		 * constraint.
+		 */
+		Assert(check_attnum == attnum);
+
+		/* Found it */
+		if (retval != NULL)
+		{
+			Assert(multiple);
+			*multiple = true;
+			break;
+		}
+
+		retval = heap_copytuple(conTup);
+		if (multiple == NULL)
+			break;
+
+nope:
+		pfree(arr);
+		continue;
+	}
+
+	systable_endscan(scan);
+	table_close(pg_constraint, AccessShareLock);
+
+	return retval;
+}
+
+/*
+ * Find and return the pg_constraint tuple that implements a validated
+ * NOT NULL constraint for the given column of the given relation.
+ *
+ * If there's more than one such constraint and *multiple is not NULL,
+ * we set that true.
+ */
+HeapTuple
+findNotNullConstraint(Relation rel, const char *colname, bool *multiple)
+{
+	AttrNumber	attnum = get_attnum(RelationGetRelid(rel), colname);
+
+	return findNotNullConstraintAttnum(rel, attnum, multiple);
+}
+
 /*
  * Delete a single constraint record.
  */
diff --git a/src/backend/commands/constraint.c b/src/backend/commands/constraint.c
index 721de178ca..affe2d04a3 100644
--- a/src/backend/commands/constraint.c
+++ b/src/backend/commands/constraint.c
@@ -17,11 +17,15 @@
 #include "access/heapam.h"
 #include "access/tableam.h"
 #include "catalog/index.h"
+#include "catalog/pg_constraint.h"
+#include "commands/constraint.h"
 #include "commands/trigger.h"
 #include "executor/executor.h"
+#include "nodes/makefuncs.h"
 #include "utils/builtins.h"
 #include "utils/rel.h"
 #include "utils/snapmgr.h"
+#include "utils/syscache.h"
 
 
 /*
@@ -203,3 +207,192 @@ unique_key_recheck(PG_FUNCTION_ARGS)
 
 	return PointerGetDatum(NULL);
 }
+
+/*
+ * Create and return a Constraint node representing a "col IS NOT NULL"
+ * expression using a ColumnRef within, for the given relation and column.
+ *
+ * If the constraint name is not provided, a standard one is generated.
+ * XXX provide a list of constraint names already reserved so we can ignore
+ * those.
+ *
+ * Note: this is a "raw" node that must undergo transformation.
+ */
+Constraint *
+makeCheckNotNullConstraint(Oid nspid, char *constraint_name,
+						   const char *relname, const char *colname,
+						   bool is_row, Oid parent_oid)
+{
+	Constraint *check;
+	ColumnRef  *colref;
+	Node	   *nullexpr;
+
+	colref = (ColumnRef *) makeNode(ColumnRef);
+	colref->fields = list_make1(makeString(pstrdup(colname)));
+
+	if (is_row)
+	{
+		A_Expr     *expr;
+		A_Const	   *constnull;
+
+		constnull = makeNode(A_Const);
+		constnull->isnull = true;
+
+		expr = makeSimpleA_Expr(AEXPR_DISTINCT, "=",
+								(Node *) colref, (Node *) constnull, -1);
+		nullexpr = (Node *) expr;
+	}
+	else
+	{
+		NullTest   *nulltest;
+
+		nulltest = makeNode(NullTest);
+		nulltest->argisrow = is_row;
+		nulltest->nulltesttype = IS_NOT_NULL;
+		nulltest->arg = (Expr *) colref;
+
+		nullexpr = (Node *) nulltest;
+	}
+
+	check = makeNode(Constraint);
+	check->contype = CONSTR_CHECK;
+	check->location = -1;
+	check->conname = constraint_name ? constraint_name :
+		ChooseConstraintName(relname, colname, "not_null", nspid, NIL);
+	check->parent_oid = parent_oid;
+	check->deferrable = false;
+	check->initdeferred = false;
+
+	check->is_no_inherit = false;
+	check->raw_expr = nullexpr;
+	check->cooked_expr = NULL;
+
+	check->skip_validation = false;
+	check->initially_valid = true;
+
+	return check;
+}
+
+/*
+ * Given the Node representation for a CHECK (col IS NOT NULL) constraint,
+ * return the column name that it is for.  If it doesn't represent a constraint
+ * of that shape, NULL is returned. 'rel' is the relation that the constraint is
+ * for.
+ */
+char *
+tryExtractNotNullFromNode(Node *node, Relation rel)
+{
+	if (node == NULL)
+		return NULL;
+
+	/* Whatever we got, we can look inside a Constraint node */
+	if (IsA(node, Constraint))
+	{
+		Constraint	*constraint = (Constraint *) node;
+
+		if (constraint->cooked_expr != NULL)
+			return tryExtractNotNullFromNode(stringToNode(constraint->cooked_expr), rel);
+		else
+			return tryExtractNotNullFromNode(constraint->raw_expr, rel);
+	}
+
+	if (IsA(node, NullTest))
+	{
+		NullTest *nulltest = (NullTest *) node;
+
+		if (nulltest->nulltesttype == IS_NOT_NULL &&
+			IsA(nulltest->arg, ColumnRef))
+		{
+			ColumnRef *colref = (ColumnRef *) nulltest->arg;
+
+			if (list_length(colref->fields) == 1)
+				return strVal(linitial(colref->fields));
+		}
+	}
+
+	/*
+	 * if no rel is passed, we can only check this much
+	 */
+	if (rel == NULL)
+		return NULL;
+
+	if (IsA(node, NullTest))
+	{
+		NullTest *nulltest = (NullTest *) node;
+
+		if (nulltest->nulltesttype == IS_NOT_NULL)
+		{
+			if (IsA(nulltest->arg, Var))
+			{
+				Var    *var = (Var *) nulltest->arg;
+
+				return NameStr(TupleDescAttr(RelationGetDescr(rel),
+											 var->varattno - 1)->attname);
+			}
+		}
+	}
+
+	/*
+	 * XXX Need to check a few more possible wordings of NOT NULL:
+	 *
+	 * - foo IS DISTINCT FROM NULL
+	 * - NOT (foo IS NULL)
+	 */
+
+	return NULL;
+}
+
+/*
+ * Given a pg_constraint tuple for a CHECK constraint, see if it is a
+ * CHECK (IS NOT NULL) constraint, and return the column number it is for if
+ * so.  Otherwise return InvalidAttrNumber.
+ */
+AttrNumber
+tryExtractNotNullFromCatalog(HeapTuple constrTup)
+{
+	Form_pg_constraint conForm = (Form_pg_constraint) GETSTRUCT(constrTup);
+	AttrNumber colnum = InvalidAttrNumber;
+	Datum   val;
+	bool    isnull;
+	char   *conbin;
+	Node   *node;
+
+	/* only tuples for CHECK constraints should be given */
+	Assert(conForm->contype == CONSTRAINT_CHECK);
+
+	val = SysCacheGetAttr(CONSTROID, constrTup, Anum_pg_constraint_conbin,
+						  &isnull);
+	if (isnull)
+		elog(ERROR, "null conbin for constraint %u", conForm->oid);
+
+	conbin = TextDatumGetCString(val);
+	node = (Node *) stringToNode(conbin);
+
+	/* We expect a NullTest with an single Var within. */
+	if (IsA(node, NullTest))
+	{
+		NullTest *nulltest = (NullTest *) node;
+
+		if (nulltest->nulltesttype == IS_NOT_NULL)
+		{
+			if (IsA(nulltest->arg, Var))
+			{
+				Var    *var = (Var *) nulltest->arg;
+
+				colnum = var->varattno;
+			}
+		}
+	}
+
+	/*
+	 * XXX Need to check a few more possible wordings of NOT NULL:
+	 *
+	 * - foo IS DISTINCT FROM NULL
+	 * - NOT (foo IS NULL)
+	 */
+
+	pfree(conbin);
+	pfree(node);
+
+	return colnum;
+}
diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c
index 152c29b551..89987c5821 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -104,6 +104,8 @@ create_ctas_internal(List *attrList, IntoClause *into)
 	create->inhRelations = NIL;
 	create->ofTypename = NULL;
 	create->constraints = NIL;
+	create->notnull_check = NIL;
+	create->notnull_bare = NIL;
 	create->options = into->options;
 	create->oncommit = into->onCommit;
 	create->tablespacename = into->tableSpaceName;
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index e3233a8f38..5e40806ae5 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -52,6 +52,7 @@
 #include "catalog/toasting.h"
 #include "commands/cluster.h"
 #include "commands/comment.h"
+#include "commands/constraint.h"
 #include "commands/defrem.h"
 #include "commands/event_trigger.h"
 #include "commands/policy.h"
@@ -67,7 +68,6 @@
 #include "miscadmin.h"
 #include "nodes/makefuncs.h"
 #include "nodes/nodeFuncs.h"
-#include "nodes/parsenodes.h"
 #include "optimizer/optimizer.h"
 #include "parser/parse_clause.h"
 #include "parser/parse_coerce.h"
@@ -349,7 +349,7 @@ static void truncate_check_activity(Relation rel);
 static void RangeVarCallbackForTruncate(const RangeVar *relation,
 										Oid relId, Oid oldRelId, void *arg);
 static List *MergeAttributes(List *schema, List *supers, char relpersistence,
-							 bool is_partition, List **supconstr);
+							 bool is_partition, List **supconstr, List **notnullcols);
 static bool MergeCheckConstraint(List *constraints, char *name, Node *expr);
 static void MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel);
 static void MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel);
@@ -430,14 +430,16 @@ static bool check_for_column_name_collision(Relation rel, const char *colname,
 											bool if_not_exists);
 static void add_column_datatype_dependency(Oid relid, int32 attnum, Oid typid);
 static void add_column_collation_dependency(Oid relid, int32 attnum, Oid collid);
-static void ATPrepDropNotNull(Relation rel, bool recurse, bool recursing);
-static ObjectAddress ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode);
+static ObjectAddress ATExecDropNotNull(Relation rel, const char *colName, bool recurse,
+									   LOCKMODE lockmode);
 static void ATPrepSetNotNull(List **wqueue, Relation rel,
 							 AlterTableCmd *cmd, bool recurse, bool recursing,
 							 LOCKMODE lockmode,
 							 AlterTableUtilityContext *context);
-static ObjectAddress ATExecSetNotNull(AlteredTableInfo *tab, Relation rel,
-									  const char *colName, LOCKMODE lockmode);
+static ObjectAddress ATExecSetNotNull(List **wqueue, AlteredTableInfo *tab,
+									  Relation rel, bool direct, bool attnotnull_only,
+									  char *constrname, const char *colName,
+									  LOCKMODE lockmode);
 static void ATExecCheckNotNull(AlteredTableInfo *tab, Relation rel,
 							   const char *colName, LOCKMODE lockmode);
 static bool NotNullImpliedByRelConstraints(Relation rel, Form_pg_attribute attr);
@@ -484,6 +486,17 @@ static ObjectAddress ATAddCheckConstraint(List **wqueue,
 										  Constraint *constr,
 										  bool recurse, bool recursing, bool is_readd,
 										  LOCKMODE lockmode);
+static ObjectAddress ATAddCheckConstraint_internal(List **wqueue,
+												   AlteredTableInfo *tab, Relation rel,
+												   Constraint *constr,
+												   bool recursing,
+												   bool check_it, bool is_readd,
+												   LOCKMODE lockmode);
+static void ATAddCheckConstraint_recurse(List **wqueue, List *children,
+										 Constraint *constr, Oid parent_constraint_oid,
+										 bool check_it, bool is_readd,
+										 List **already_done_rels,
+										 LOCKMODE lockmode);
 static ObjectAddress ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab,
 											   Relation rel, Constraint *fkconstraint,
 											   bool recurse, bool recursing,
@@ -853,19 +866,57 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
 
 	/*
 	 * Look up inheritance ancestors and generate relation schema, including
-	 * inherited attributes.  (Note that stmt->tableElts is destructively
-	 * modified by MergeAttributes.)
+	 * inherited attributes.  (Note that stmt->tableElts and ->notnull_check
+	 * are destructively modified by MergeAttributes.)
 	 */
 	stmt->tableElts =
 		MergeAttributes(stmt->tableElts, inheritOids,
 						stmt->relation->relpersistence,
 						stmt->partbound != NULL,
-						&old_constraints);
+						&old_constraints, &stmt->notnull_check);
+
+	/*
+	 * If there are any additional columns to be marked attnotnull, prepare to
+	 * do so.
+	 */
+	foreach(listptr, stmt->notnull_check)
+	{
+		ConstraintNotNull *nn = lfirst_node(ConstraintNotNull, listptr);
+		ListCell   *lc;
+
+		foreach(lc, stmt->tableElts)
+		{
+			ColumnDef	*thiscol = lfirst(lc);
+
+			if (strcmp(thiscol->colname, nn->column) == 0)
+			{
+				thiscol->is_not_null = true;
+				break;
+			}
+		}
+	}
+	foreach(listptr, stmt->notnull_bare)
+	{
+		char	   *colname = strVal(lfirst(listptr));
+		ListCell   *lc;
+
+		foreach(lc, stmt->tableElts)
+		{
+			ColumnDef	*thiscol = lfirst(lc);
+
+			if (strcmp(thiscol->colname, colname) == 0)
+			{
+				thiscol->is_not_null = true;
+				break;
+			}
+		}
+	}
 
 	/*
 	 * Create a tuple descriptor from the relation schema.  Note that this
-	 * deals with column names, types, and NOT NULL constraints, but not
-	 * default values or CHECK constraints; we handle those below.
+	 * deals with column names, types, and in-descriptor NOT NULL constraints,
+	 * but not default values or CHECK constraints (including the CHECK (foo
+	 * IS NOT NULL) part of not-null constraints); we handle those below.
 	 */
 	descriptor = BuildDescForRelation(stmt->tableElts);
 
@@ -1239,13 +1290,67 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
 	}
 
 	/*
-	 * Now add any newly specified CHECK constraints to the new relation. Same
-	 * as for defaults above, but these need to come after partitioning is set
-	 * up.
+	 * Now add any newly specified CHECK constraints to the new relation,
+	 * including manufactured CHECK constraints for columns declared NOT NULL
+	 * in various ways (straight NOT NULL, serial, identity, etc).  Same as for
+	 * defaults above, but these need to come after partitioning is set up.
 	 */
-	if (stmt->constraints)
-		AddRelationNewConstraints(rel, NIL, stmt->constraints,
+	if (stmt->constraints || stmt->notnull_check != NIL)
+	{
+		List	   *nncks = NIL;
+		Bitmapset  *seencols = NULL;
+
+		/*
+		 * First, walk all the explicitly declared constraints and mark
+		 * any columns that appear in a CHECK (foo IS NOT NULL) constraint
+		 * as seen.  This way, named constraints take precedence over
+		 * unnamed ones.
+		 */
+		foreach(listptr, stmt->constraints)
+		{
+			Constraint *c = lfirst(listptr);
+			char	   *colname;
+
+			if (c->contype != CONSTRAINT_CHECK)
+				continue;
+			colname = tryExtractNotNullFromNode((Node *) c, rel);
+			if (!colname)
+				continue;
+			seencols = bms_add_member(seencols,
+									  get_attnum(RelationGetRelid(rel), colname));
+		}
+
+		/*
+		 * Manufacture CHECK constraints for any columns marked NOT NULL that
+		 * we didn't already see above.
+		 */
+		foreach(listptr, stmt->notnull_check)
+		{
+			ConstraintNotNull *nn = lfirst_node(ConstraintNotNull, listptr);
+			Constraint *newcons;
+			AttrNumber	colnum;
+			bool		is_row = false;	/* FIXME */
+
+			/* Don't create duplicate constraints */
+			colnum = get_attnum(RelationGetRelid(rel), nn->column);
+			if (bms_is_member(colnum, seencols))
+				continue;
+
+			newcons = makeCheckNotNullConstraint(namespaceId,
+												 nn->conname,
+												 relname,
+												 nn->column,
+												 is_row,
+												 InvalidOid);
+			seencols = bms_add_member(seencols, colnum);
+			nncks = lappend(nncks, newcons);
+		}
+
+		/* And create all collected constraints */
+		AddRelationNewConstraints(rel, NIL,
+								  list_concat(nncks, stmt->constraints),
 								  true, true, false, queryString);
+	}
 
 	ObjectAddressSet(address, RelationRelationId, relationId);
 
@@ -2280,6 +2385,8 @@ storage_name(char c)
  * Output arguments:
  * 'supconstr' receives a list of constraints belonging to the parents,
  *		updated as necessary to be valid for the child.
+ * 'notnullcols' is appended additional columns that have to receive a
+ *		CHECK (IS NOT NULL) constraint.
  *
  * Return value:
  * Completed schema list.
@@ -2324,8 +2431,9 @@ storage_name(char c)
  *----------
  */
 static List *
-MergeAttributes(List *schema, List *supers, char relpersistence,
-				bool is_partition, List **supconstr)
+MergeAttributes(List *schema, List *supers,
+				char relpersistence, bool is_partition,
+				List **supconstr, List **notnullcols)
 {
 	List	   *inhSchema = NIL;
 	List	   *constraints = NIL;
@@ -2443,6 +2551,7 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
 		AttrNumber	parent_attno;
 		ListCell   *lc1;
 		ListCell   *lc2;
+		Bitmapset  *pkattrs;
 
 		/* caller already got lock */
 		relation = table_open(parent, NoLock);
@@ -2531,6 +2640,13 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
 		/* We can't process inherited defaults until newattmap is complete. */
 		inherited_defaults = cols_with_defaults = NIL;
 
+		/*
+		 * All columns that are part of the parent's primary key need to get a
+		 * NOT NULL constraint, if they don't have one already.
+		 */
+		pkattrs = RelationGetIndexAttrBitmap(relation,
+											 INDEX_ATTR_BITMAP_PRIMARY_KEY);
+
 		for (parent_attno = 1; parent_attno <= tupleDesc->natts;
 			 parent_attno++)
 		{
@@ -2615,6 +2731,23 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
 				}
 
 				def->inhcount++;
+
+				/*
+				 * In regular inheritance, columns in the parent's primary key
+				 * get an extra CHECK (NOT NULL) constraint.  Partitioning
+				 * doesn't need this, because the PK itself is going to be
+				 * cloned to the partition.
+				 */
+				if (!is_partition &&
+					bms_is_member(parent_attno - FirstLowInvalidHeapAttributeNumber,
+								  pkattrs))
+				{
+					ConstraintNotNull *nn = makeNode(ConstraintNotNull);
+
+					nn->column = def->colname;	/* XXX note no pstrdup */
+					nn->conname = NULL;
+					*notnullcols = lappend(*notnullcols, nn);
+				}
 				/* Merge of NOT NULL constraints = OR 'em together */
 				def->is_not_null |= attribute->attnotnull;
 				/* Default and other constraints are handled below */
@@ -2655,6 +2788,24 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
 					def->compression = NULL;
 				inhSchema = lappend(inhSchema, def);
 				newattmap->attnums[parent_attno - 1] = ++child_attno;
+
+				/*
+				 * In regular inheritance, columns in the parent's primary key
+				 * get an extra CHECK (NOT NULL) constraint.  Partitioning
+				 * doesn't need this, because the PK itself is going to be
+				 * cloned to the partition.
+				 */
+				if (!is_partition &&
+					bms_is_member(parent_attno -
+								  FirstLowInvalidHeapAttributeNumber,
+								  pkattrs))
+				{
+					ConstraintNotNull *nn = makeNode(ConstraintNotNull);
+
+					nn->column = def->colname;	/* XXX note no pstrdup */
+					nn->conname = NULL;
+					*notnullcols = lappend(*notnullcols, nn);
+				}
 			}
 
 			/*
@@ -2787,6 +2938,7 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
 					cooked = (CookedConstraint *) palloc(sizeof(CookedConstraint));
 					cooked->contype = CONSTR_CHECK;
 					cooked->conoid = InvalidOid;	/* until created */
+					cooked->parent_oid = check[i].ccoid;
 					cooked->name = pstrdup(name);
 					cooked->attnum = 0; /* not used for constraints */
 					cooked->expr = expr;
@@ -2993,8 +3145,7 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
 	/*
 	 * Now that we have the column definition list for a partition, we can
 	 * check whether the columns referenced in the column constraint specs
-	 * actually exist.  Also, we merge NOT NULL and defaults into each
-	 * corresponding column definition.
+	 * actually exist.
 	 */
 	if (is_partition)
 	{
@@ -3011,7 +3162,6 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
 				if (strcmp(coldef->colname, restdef->colname) == 0)
 				{
 					found = true;
-					coldef->is_not_null |= restdef->is_not_null;
 
 					/*
 					 * Override the parent's default value for this column
@@ -4262,6 +4412,7 @@ AlterTableGetLockLevel(List *cmds)
 			case AT_AddIndexConstraint:
 			case AT_ReplicaIdentity:
 			case AT_SetNotNull:
+			case AT_SetAttNotNull:
 			case AT_EnableRowSecurity:
 			case AT_DisableRowSecurity:
 			case AT_ForceRowSecurity:
@@ -4561,10 +4712,12 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
 			break;
 		case AT_DropNotNull:	/* ALTER COLUMN DROP NOT NULL */
 			ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_FOREIGN_TABLE);
-			ATPrepDropNotNull(rel, recurse, recursing);
-			ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode, context);
+			/* Set up recursion for phase 2; no other prep needed */
+			if (recurse)
+				cmd->recurse = true;
 			pass = AT_PASS_DROP;
 			break;
+		case AT_SetAttNotNull:		/* XXX ok to share implementation? */
 		case AT_SetNotNull:		/* ALTER COLUMN SET NOT NULL */
 			ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_FOREIGN_TABLE);
 			/* Need command-specific recursion decision */
@@ -4959,10 +5112,15 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
 			address = ATExecDropIdentity(rel, cmd->name, cmd->missing_ok, lockmode);
 			break;
 		case AT_DropNotNull:	/* ALTER COLUMN DROP NOT NULL */
-			address = ATExecDropNotNull(rel, cmd->name, lockmode);
+			address = ATExecDropNotNull(rel, cmd->name, cmd->recurse, lockmode);
 			break;
 		case AT_SetNotNull:		/* ALTER COLUMN SET NOT NULL */
-			address = ATExecSetNotNull(tab, rel, cmd->name, lockmode);
+			address = ATExecSetNotNull(wqueue, tab, rel, true, false, NULL,
+									   cmd->name, lockmode);
+			break;
+		case AT_SetAttNotNull:
+			address = ATExecSetNotNull(wqueue, tab, rel, true, true, NULL,
+									   cmd->name, lockmode);
 			break;
 		case AT_CheckNotNull:	/* check column is already marked NOT NULL */
 			ATExecCheckNotNull(tab, rel, cmd->name, lockmode);
@@ -5331,6 +5489,7 @@ ATParseTransformCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
 		switch (cmd2->subtype)
 		{
 			case AT_SetNotNull:
+			case AT_SetAttNotNull:
 				/* Need command-specific recursion decision */
 				ATPrepSetNotNull(wqueue, rel, cmd2,
 								 recurse, false,
@@ -5396,8 +5555,8 @@ ATParseTransformCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
 				newcmd = cmd2;
 			}
 			else
-				elog(ERROR, "ALTER TABLE scheduling failure: bogus item for pass %d",
-					 pass);
+				elog(ERROR, "ALTER TABLE scheduling failure: bogus item %s for pass %d, cmd %s",
+					 newcmd ? nodeToString(newcmd) : "(null)", pass, nodeToString(cmd));
 		}
 	}
 
@@ -6119,6 +6278,8 @@ alter_table_type_to_string(AlterTableType cmdtype)
 			return "ALTER COLUMN ... DROP NOT NULL";
 		case AT_SetNotNull:
 			return "ALTER COLUMN ... SET NOT NULL";
+		case AT_SetAttNotNull:
+			return NULL;		/* not real grammar */
 		case AT_DropExpression:
 			return "ALTER COLUMN ... DROP EXPRESSION";
 		case AT_CheckNotNull:
@@ -6682,8 +6843,7 @@ ATPrepAddColumn(List **wqueue, Relation rel, bool recurse, bool recursing,
  */
 static ObjectAddress
 ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
-				AlterTableCmd **cmd,
-				bool recurse, bool recursing,
+				AlterTableCmd **cmd, bool recurse, bool recursing,
 				LOCKMODE lockmode, int cur_pass,
 				AlterTableUtilityContext *context)
 {
@@ -7188,44 +7348,46 @@ add_column_collation_dependency(Oid relid, int32 attnum, Oid collid)
 	}
 }
 
-/*
- * ALTER TABLE ALTER COLUMN DROP NOT NULL
- */
-
-static void
-ATPrepDropNotNull(Relation rel, bool recurse, bool recursing)
-{
-	/*
-	 * If the parent is a partitioned table, like check constraints, we do not
-	 * support removing the NOT NULL while partitions exist.
-	 */
-	if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
-	{
-		PartitionDesc partdesc = RelationGetPartitionDesc(rel, true);
-
-		Assert(partdesc != NULL);
-		if (partdesc->nparts > 0 && !recurse && !recursing)
-			ereport(ERROR,
-					(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
-					 errmsg("cannot remove constraint from only the partitioned table when partitions exist"),
-					 errhint("Do not specify the ONLY keyword.")));
-	}
-}
-
 /*
  * Return the address of the modified column.  If the column was already
  * nullable, InvalidObjectAddress is returned.
+ *
+ * There are two ways in which NOT NULL constraints can be dropped: DROP NOT
+ * NULL and DROP CONSTRAINT.  For DROP NOT NULL, the algorithm is:
+ *
+ * 0. search for the relevant constraint.  If there's more than one, error
+ * 1. drop the constraint (by OID)
+ * 2. see if after the drop we can unmark (must be true, because of 1)
+ * 3. recurse on 2
+ *
+ * For DROP CONSTRAINT, the algorithm is:
+ * 0. look up constraint OID by name
+ * 1. drop the constraint (by OID)
+ * 2. see if after drop we can unmark (may not be true, it's ok if so)
+ * 3. recurse on 1
+ *
+ * Recursion:
+ * - for each children
+ *   * look up the constraint that is child of the given constraint
+ *   * drop the constraint
+ *   * see if after drop we can unmark (may not be true, it's OK if so)
+ *   * if it has children, recurse
  */
 static ObjectAddress
-ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode)
+ATExecDropConstraint_internal(Relation rel, HeapTuple constraintTup, DropBehavior behavior,
+							  bool recurse, bool recursing, bool missing_ok, LOCKMODE lockmode);
+
+static ObjectAddress
+ATExecDropNotNull(Relation rel, const char *colName, bool recurse,
+				  LOCKMODE lockmode)
 {
 	HeapTuple	tuple;
+	HeapTuple	conTup;
 	Form_pg_attribute attTup;
 	AttrNumber	attnum;
 	Relation	attr_rel;
-	List	   *indexoidlist;
-	ListCell   *indexoidscan;
 	ObjectAddress address;
+	bool		multiple;
 
 	/*
 	 * lookup the attribute
@@ -7241,6 +7403,13 @@ ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode)
 	attTup = (Form_pg_attribute) GETSTRUCT(tuple);
 	attnum = attTup->attnum;
 
+	/* If the column is already nullable there's nothing to do. */
+	if (!attTup->attnotnull)
+	{
+		table_close(attr_rel, RowExclusiveLock);
+		return InvalidObjectAddress;
+	}
+
 	/* Prevent them from altering a system attribute */
 	if (attnum <= 0)
 		ereport(ERROR,
@@ -7255,62 +7424,28 @@ ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode)
 						colName, RelationGetRelationName(rel))));
 
 	/*
-	 * Check that the attribute is not in a primary key or in an index used as
-	 * a replica identity.
-	 *
-	 * Note: we'll throw error even if the pkey index is not valid.
+	 * It's not OK to remove a constraint only for the partitioned table
+	 * itself and leave it in the partitions, so disallow that.  But for
+	 * legacy inheritance, it's not a problem.
 	 */
-
-	/* Loop over all indexes on the relation */
-	indexoidlist = RelationGetIndexList(rel);
-
-	foreach(indexoidscan, indexoidlist)
+	if (!recurse && rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
 	{
-		Oid			indexoid = lfirst_oid(indexoidscan);
-		HeapTuple	indexTuple;
-		Form_pg_index indexStruct;
-		int			i;
+		PartitionDesc	partdesc;
 
-		indexTuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexoid));
-		if (!HeapTupleIsValid(indexTuple))
-			elog(ERROR, "cache lookup failed for index %u", indexoid);
-		indexStruct = (Form_pg_index) GETSTRUCT(indexTuple);
+		partdesc = RelationGetPartitionDesc(rel, true);
 
-		/*
-		 * If the index is not a primary key or an index used as replica
-		 * identity, skip the check.
-		 */
-		if (indexStruct->indisprimary || indexStruct->indisreplident)
-		{
-			/*
-			 * Loop over each attribute in the primary key or the index used
-			 * as replica identity and see if it matches the to-be-altered
-			 * attribute.
-			 */
-			for (i = 0; i < indexStruct->indnkeyatts; i++)
-			{
-				if (indexStruct->indkey.values[i] == attnum)
-				{
-					if (indexStruct->indisprimary)
-						ereport(ERROR,
-								(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
-								 errmsg("column \"%s\" is in a primary key",
-										colName)));
-					else
-						ereport(ERROR,
-								(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
-								 errmsg("column \"%s\" is in index used as replica identity",
-										colName)));
-				}
-			}
-		}
-
-		ReleaseSysCache(indexTuple);
+		if (partdesc->nparts > 0)
+			ereport(ERROR,
+					errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+					errmsg("cannot remove constraint from only the partitioned table when partitions exist"),
+					errhint("Do not specify the ONLY keyword."));
 	}
 
-	list_free(indexoidlist);
-
-	/* If rel is partition, shouldn't drop NOT NULL if parent has the same */
+	/*
+	 * If rel is partition, shouldn't drop NOT NULL if parent has the same.
+	 * XXX is this consideration still valid?  Can we get rid of this by
+	 * changing the type of dependency between the two constraints instead?
+	 */
 	if (rel->rd_rel->relispartition)
 	{
 		Oid			parentId = get_partition_parent(RelationGetRelid(rel), false);
@@ -7328,22 +7463,46 @@ ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode)
 	}
 
 	/*
-	 * Okay, actually perform the catalog change ... if needed
+	 * Find the constraint that makes this column NOT NULL.  If there's more
+	 * than one, we cannot cope well, so give up.
 	 */
-	if (attTup->attnotnull)
+	conTup = findNotNullConstraint(rel, colName, &multiple);
+	if (conTup == NULL)
 	{
-		attTup->attnotnull = false;
+		Bitmapset	   *pkcols;
 
-		CatalogTupleUpdate(attr_rel, &tuple->t_self, tuple);
+		/*
+		 * There's no CHECK (IS NOT NULL) constraint, so throw an error.  If
+		 * the column is in a primary key, we can throw a specific error.
+		 * Otherwise, this is unexpected.
+		 */
+		pkcols = RelationGetIndexAttrBitmap(rel, INDEX_ATTR_BITMAP_PRIMARY_KEY);
+		if (bms_is_member(attnum - FirstLowInvalidHeapAttributeNumber,
+						  pkcols))
+			ereport(ERROR,
+					errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+					errmsg("column \"%s\" is in a primary key", colName));
 
-		ObjectAddressSubSet(address, RelationRelationId,
-							RelationGetRelid(rel), attnum);
+		/* this shouldn't happen */
+		elog(ERROR, "no NOT NULL constraint found to drop");
 	}
-	else
-		address = InvalidObjectAddress;
 
-	InvokeObjectPostAlterHook(RelationRelationId,
-							  RelationGetRelid(rel), attnum);
+	/*
+	 * If there are several pg_constraint rows to which this not-null constraint
+	 * can be attributed, raise an error about the ambiguity.  We don't want to
+	 * guess as to which one to drop; or worse, make this command work and have
+	 * the user expect that the column is nullable afterwards.
+	 */
+	if (multiple)
+		ereport(ERROR,
+				errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+				errmsg("cannot DROP NOT NULL when multiple possible constraints exist"),
+				errhint("Consider specifying which constraint to drop with ALTER TABLE .. DROP CONSTRAINT."));
+
+	address = ATExecDropConstraint_internal(rel, conTup, DROP_RESTRICT, recurse, false,
+											false, lockmode);
+
+	heap_freetuple(conTup);
 
 	table_close(attr_rel, RowExclusiveLock);
 
@@ -7422,10 +7581,19 @@ ATPrepSetNotNull(List **wqueue, Relation rel,
 /*
  * Return the address of the modified column.  If the column was already NOT
  * NULL, InvalidObjectAddress is returned.
+ *
+ * When ALTER TABLE/ALTER COLUMN/SET NOT NULL is called, 'direct' is true
+ * and we avoid creating a duplicate constraint.  However, if ALTER TABLE/
+ * ADD CONSTRAINT is called to create an IS NOT NULL constraint, we do not
+ * avoid a duplicate constraint.
+ *
+ * XXX maybe better to split things in small subroutines for SET NOT NULL
+ * and ADD CONSTRAINT to use, rather than this labyrinth.
  */
 static ObjectAddress
-ATExecSetNotNull(AlteredTableInfo *tab, Relation rel,
-				 const char *colName, LOCKMODE lockmode)
+ATExecSetNotNull(List **wqueue, AlteredTableInfo *tab, Relation rel,
+				 bool direct, bool attnotnull_only,
+				 char *constrname, const char *colName, LOCKMODE lockmode)
 {
 	HeapTuple	tuple;
 	AttrNumber	attnum;
@@ -7485,6 +7653,50 @@ ATExecSetNotNull(AlteredTableInfo *tab, Relation rel,
 	InvokeObjectPostAlterHook(RelationRelationId,
 							  RelationGetRelid(rel), attnum);
 
+	/*
+	 * We also add a new pg_constraint row.  Use ATAddCheckConstraint_internal
+	 * for that, letting it know that it doesn't need to test the constraint;
+	 * we already did that above, if necessary.  However, we don't do this for
+	 * system catalogs, because that creates relcache recursion issues.  Also
+	 * skip it if we already have one equivalent constraint.
+	 */
+	if (!attnotnull_only && !IsCatalogRelation(rel))
+	{
+		HeapTuple	constr;
+
+		/* See if there's one already, and skip this if so. */
+		constr = findNotNullConstraintAttnum(rel, attnum, NULL);
+		if (constr && direct)
+			heap_freetuple(constr);	/* nothing to do */
+		else
+		{
+			Constraint *newconstr;
+			ObjectAddress addr;
+			List	   *children;
+			List	   *already_done_rels;
+
+			newconstr = makeCheckNotNullConstraint(rel->rd_rel->relnamespace,
+												   constrname,
+												   NameStr(rel->rd_rel->relname),
+												   colName,
+												   false, /* XXX is_row */
+												   InvalidOid);
+
+			addr = ATAddCheckConstraint_internal(wqueue, tab, rel, newconstr,
+												 false, false, false, lockmode);
+			already_done_rels = list_make1_oid(RelationGetRelid(rel));
+
+			/* and recurse into children, if there are any */
+			children = find_inheritance_children(RelationGetRelid(rel), lockmode);
+			ATAddCheckConstraint_recurse(wqueue, children, newconstr,
+										 addr.objectId,
+										 /* XXX verify these bools */
+										 true, false,
+										 &already_done_rels,
+										 lockmode);
+		}
+	}
+
 	table_close(attr_rel, RowExclusiveLock);
 
 	return address;
@@ -8880,17 +9092,175 @@ static ObjectAddress
 ATAddCheckConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
 					 Constraint *constr, bool recurse, bool recursing,
 					 bool is_readd, LOCKMODE lockmode)
+{
+	char	   *colname;
+	ObjectAddress address;
+	List	   *children;
+	List	   *already_done_rels;
+
+	Assert(constr->contype == CONSTR_CHECK);
+
+	/* At the top level, permission check was done in ATPrepCmd */
+
+	/*
+	 * If the constraint we're adding is a validated CHECK (col IS NOT NULL),
+	 * route it through ATExecSetNotNull instead of handling it here.
+	 *
+	 * The reason for this is to get the attnotnull bit set for the column.
+	 */
+	if (constr->initially_valid)
+	{
+		colname = tryExtractNotNullFromNode((Node *) constr, rel);
+		if (colname != NULL)
+			return ATExecSetNotNull(wqueue, tab, rel, false, false,
+									constr->conname, colname, lockmode);
+	}
+
+	/* Not a single-column NOT NULL constraint -- do the regular dance */
+	address = ATAddCheckConstraint_internal(wqueue, tab, rel, constr,
+											recursing, true, is_readd,
+											lockmode);
+
+	/*
+	 * If adding a NO INHERIT constraint, no need to find our children.
+	 */
+	if (constr->is_no_inherit)
+		return address;
+
+	/* If the constraint was merged with some preexisting one, we're done.
+	 * We mustn't recurse to child tables in this case, because they've
+	 * already got the constraint, and visiting them again would leave to an
+	 * incorrect value for coninhcount.
+	 */
+	if (address.classId == InvalidOid)
+		return address;
+
+	/*
+	 * Propagate to children as appropriate.  Unlike most other ALTER
+	 * routines, we have to do this one level of recursion at a time, because
+	 * some children may already have a similar constraint with which this one
+	 * is merged.  If that happens, we need to stop recursing at that point.
+	 * So we can't use find_all_inheritors to do it in one pass.
+	 */
+	children = find_inheritance_children(RelationGetRelid(rel), lockmode);
+
+	/*
+	 * Check if ONLY was specified with ALTER TABLE.  If so, allow the
+	 * constraint creation only if there are no children currently.  Error out
+	 * otherwise.
+	 */
+	if (!recurse && children != NIL)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+				 errmsg("constraint must be added to child tables too")));
+
+	already_done_rels = list_make1_oid(RelationGetRelid(rel));
+	ATAddCheckConstraint_recurse(wqueue, children, constr, constr->parent_oid,
+								 true, is_readd,	/* XXX verify bools */
+								 &already_done_rels,
+								 lockmode);
+
+	return address;
+}
+
+/*
+ * Recursive subroutine for ATAddCheckConstraint and siblings.
+ *
+ * It applies ATAddCheckConstraint_internal to each relation in the given
+ * children list; and it recurses into any children that any of them might
+ * have.
+ *
+ * *already_done_rels is a list of relations which have already been visited
+ * by ATAddCheckConstraint_internal for this constraint (and child relations
+ * are added to the list).  This is used to avoid modifying tables twice in
+ * case of multiple inheritance.
+ */
+static void
+ATAddCheckConstraint_recurse(List **wqueue, List *children, Constraint *constr,
+							 Oid parent_constraint_oid,
+							 bool check_it, bool is_readd,
+							 List **already_done_rels,
+							 LOCKMODE lockmode)
+{
+	ListCell   *child;
+
+	foreach(child, children)
+	{
+		Oid			childrelid = lfirst_oid(child);
+		Relation	childrel;
+		AlteredTableInfo *childtab;
+		ObjectAddress addr;
+
+		/* Don't do it twice to the same rel */
+		if (list_member_oid(*already_done_rels, childrelid))
+			continue;
+
+		/* caller already got lock */
+		childrel = table_open(childrelid, NoLock);
+		CheckTableNotInUse(childrel, "ALTER TABLE");
+
+		ATSimplePermissions(AT_AddConstraint, childrel,
+							ATT_TABLE | ATT_FOREIGN_TABLE);
+
+		/* Find or create work queue entry for this table */
+		childtab = ATGetQueueEntry(wqueue, childrel);
+
+		/* Create the constraint on this relation */
+		constr->parent_oid = parent_constraint_oid;
+		addr = ATAddCheckConstraint_internal(wqueue, childtab, childrel,
+											 constr, true,
+											 true, /* XXX verify this */
+											 is_readd, lockmode);
+		*already_done_rels = lappend_oid(*already_done_rels, childrelid);
+
+		/* If this relation has children, recurse into them as well */
+		if (childrel->rd_rel->relhassubclass)
+		{
+			List *subchld = find_inheritance_children(childrelid, lockmode);
+
+			/*
+			 * Increment command counter, in case we visit the same table more
+			 * than once.  This is only possible with legacy inheritance, not
+			 * partitioning.
+			 */
+			if (childrel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)
+				CommandCounterIncrement();
+
+			/* XXX update parent constraint OID */
+			ATAddCheckConstraint_recurse(wqueue, subchld, constr,
+										 addr.objectId,
+										 check_it, is_readd,
+										 already_done_rels,
+										 lockmode);
+
+			list_free(subchld);
+		}
+
+		table_close(childrel, NoLock);
+	}
+}
+
+/*
+ * Workhorse for various situations that need to add some form of CHECK
+ * constraint to a single relation.
+ *
+ * This includes setting a column as NOT NULL as well as adding generic CHECK
+ * constraints, and also ALTER TABLE ADD COLUMN ... NOT NULL.
+ *
+ * Caller must do any permissions checking.
+ *
+ * This routine does not recurse; caller must do that as appropriate.
+ */
+static ObjectAddress
+ATAddCheckConstraint_internal(List **wqueue, AlteredTableInfo *tab,
+							  Relation rel, Constraint *constr,
+							  bool recursing, bool check_it, bool is_readd,
+							  LOCKMODE lockmode)
 {
 	List	   *newcons;
 	ListCell   *lcon;
-	List	   *children;
-	ListCell   *child;
 	ObjectAddress address = InvalidObjectAddress;
 
-	/* At top level, permission check was done in ATPrepCmd, else do it */
-	if (recursing)
-		ATSimplePermissions(AT_AddConstraint, rel, ATT_TABLE | ATT_FOREIGN_TABLE);
-
 	/*
 	 * Call AddRelationNewConstraints to do the work, making sure it works on
 	 * a copy of the Constraint so transformExpr can't modify the original. It
@@ -8908,6 +9278,13 @@ ATAddCheckConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
 										is_readd,	/* is_internal */
 										NULL);	/* queryString not available
 												 * here */
+	if (newcons != NIL)
+	{
+		/* XXX this'd be two lines shorter if CookedConstraint was Node */
+		CookedConstraint *cc = (CookedConstraint *) linitial(newcons);
+
+		ObjectAddressSet(address, ConstraintRelationId, cc->conoid);
+	}
 
 	/* we don't expect more than one constraint here */
 	Assert(list_length(newcons) <= 1);
@@ -8932,69 +9309,11 @@ ATAddCheckConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
 		/* Save the actually assigned name if it was defaulted */
 		if (constr->conname == NULL)
 			constr->conname = ccon->name;
-
-		ObjectAddressSet(address, ConstraintRelationId, ccon->conoid);
 	}
 
 	/* At this point we must have a locked-down name to use */
 	Assert(constr->conname != NULL);
 
-	/* Advance command counter in case same table is visited multiple times */
-	CommandCounterIncrement();
-
-	/*
-	 * If the constraint got merged with an existing constraint, we're done.
-	 * We mustn't recurse to child tables in this case, because they've
-	 * already got the constraint, and visiting them again would lead to an
-	 * incorrect value for coninhcount.
-	 */
-	if (newcons == NIL)
-		return address;
-
-	/*
-	 * If adding a NO INHERIT constraint, no need to find our children.
-	 */
-	if (constr->is_no_inherit)
-		return address;
-
-	/*
-	 * Propagate to children as appropriate.  Unlike most other ALTER
-	 * routines, we have to do this one level of recursion at a time; we can't
-	 * use find_all_inheritors to do it in one pass.
-	 */
-	children =
-		find_inheritance_children(RelationGetRelid(rel), lockmode);
-
-	/*
-	 * Check if ONLY was specified with ALTER TABLE.  If so, allow the
-	 * constraint creation only if there are no children currently.  Error out
-	 * otherwise.
-	 */
-	if (!recurse && children != NIL)
-		ereport(ERROR,
-				(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
-				 errmsg("constraint must be added to child tables too")));
-
-	foreach(child, children)
-	{
-		Oid			childrelid = lfirst_oid(child);
-		Relation	childrel;
-		AlteredTableInfo *childtab;
-
-		/* find_inheritance_children already got lock */
-		childrel = table_open(childrelid, NoLock);
-		CheckTableNotInUse(childrel, "ALTER TABLE");
-
-		/* Find or create work queue entry for this table */
-		childtab = ATGetQueueEntry(wqueue, childrel);
-
-		/* Recurse to child */
-		ATAddCheckConstraint(wqueue, childtab, childrel,
-							 constr, recurse, true, is_readd, lockmode);
-
-		table_close(childrel, NoLock);
-	}
-
 	return address;
 }
 
@@ -11051,6 +11370,7 @@ ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName,
 			List	   *children = NIL;
 			ListCell   *child;
 			NewConstraint *newcon;
+			AttrNumber	attnum;
 			bool		isnull;
 			Datum		val;
 			char	   *conbin;
@@ -11123,6 +11443,26 @@ ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName,
 			 * constraint.
 			 */
 			CacheInvalidateRelcache(rel);
+
+			/*
+			 * If we have validated a CHECK (IS NOT NULL) constraint, update
+			 * the column so that it's marked attnotnull as well.
+			 */
+			attnum = tryExtractNotNullFromCatalog(tuple);
+			if (attnum != InvalidAttrNumber)
+			{
+				Relation	pgatt;
+				HeapTuple	atttup;
+
+				pgatt = table_open(AttributeRelationId, RowExclusiveLock);
+				atttup = SearchSysCacheCopyAttNum(con->conrelid, attnum);
+				if (!((Form_pg_attribute) GETSTRUCT(atttup))->attnotnull)
+				{
+					((Form_pg_attribute) GETSTRUCT(atttup))->attnotnull = true;
+					CatalogTupleUpdate(pgatt, &atttup->t_self, atttup);
+				}
+				table_close(pgatt, RowExclusiveLock);
+			}
 		}
 
 		/*
@@ -11818,16 +12158,11 @@ ATExecDropConstraint(Relation rel, const char *constrName,
 					 bool recurse, bool recursing,
 					 bool missing_ok, LOCKMODE lockmode)
 {
-	List	   *children;
-	ListCell   *child;
 	Relation	conrel;
-	Form_pg_constraint con;
 	SysScanDesc scan;
 	ScanKeyData skey[3];
 	HeapTuple	tuple;
 	bool		found = false;
-	bool		is_no_inherit_constraint = false;
-	char		contype;
 
 	/* At top level, permission check was done in ATPrepCmd, else do it */
 	if (recursing)
@@ -11856,47 +12191,8 @@ ATExecDropConstraint(Relation rel, const char *constrName,
 	/* There can be at most one matching row */
 	if (HeapTupleIsValid(tuple = systable_getnext(scan)))
 	{
-		ObjectAddress conobj;
-
-		con = (Form_pg_constraint) GETSTRUCT(tuple);
-
-		/* Don't drop inherited constraints */
-		if (con->coninhcount > 0 && !recursing)
-			ereport(ERROR,
-					(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
-					 errmsg("cannot drop inherited constraint \"%s\" of relation \"%s\"",
-							constrName, RelationGetRelationName(rel))));
-
-		is_no_inherit_constraint = con->connoinherit;
-		contype = con->contype;
-
-		/*
-		 * If it's a foreign-key constraint, we'd better lock the referenced
-		 * table and check that that's not in use, just as we've already done
-		 * for the constrained table (else we might, eg, be dropping a trigger
-		 * that has unfired events).  But we can/must skip that in the
-		 * self-referential case.
-		 */
-		if (contype == CONSTRAINT_FOREIGN &&
-			con->confrelid != RelationGetRelid(rel))
-		{
-			Relation	frel;
-
-			/* Must match lock taken by RemoveTriggerById: */
-			frel = table_open(con->confrelid, AccessExclusiveLock);
-			CheckTableNotInUse(frel, "ALTER TABLE");
-			table_close(frel, NoLock);
-		}
-
-		/*
-		 * Perform the actual constraint deletion
-		 */
-		conobj.classId = ConstraintRelationId;
-		conobj.objectId = con->oid;
-		conobj.objectSubId = 0;
-
-		performDeletion(&conobj, behavior, 0);
-
+		ATExecDropConstraint_internal(rel, tuple, behavior, recurse, recursing,
+									  missing_ok, lockmode);
 		found = true;
 	}
 
@@ -11905,31 +12201,224 @@ ATExecDropConstraint(Relation rel, const char *constrName,
 	if (!found)
 	{
 		if (!missing_ok)
-		{
 			ereport(ERROR,
-					(errcode(ERRCODE_UNDEFINED_OBJECT),
-					 errmsg("constraint \"%s\" of relation \"%s\" does not exist",
-							constrName, RelationGetRelationName(rel))));
-		}
+					errcode(ERRCODE_UNDEFINED_OBJECT),
+					errmsg("constraint \"%s\" of relation \"%s\" does not exist",
+						   constrName, RelationGetRelationName(rel)));
 		else
-		{
 			ereport(NOTICE,
-					(errmsg("constraint \"%s\" of relation \"%s\" does not exist, skipping",
-							constrName, RelationGetRelationName(rel))));
-			table_close(conrel, RowExclusiveLock);
-			return;
+					errmsg("constraint \"%s\" of relation \"%s\" does not exist, skipping",
+						   constrName, RelationGetRelationName(rel)));
+	}
+
+	table_close(conrel, RowExclusiveLock);
+}
+
+/*
+ * Remove a constraint, using a pg_constraint tuple XXX complete description
+ *
+ * Implementation for ALTER TABLE DROP CONSTRAINT and ALTER TABLE ALTER COLUMN
+ * DROP NOT NULL.
+ *
+ * Returns the address of the constraint being removed.
+ */
+static ObjectAddress
+ATExecDropConstraint_internal(Relation rel, HeapTuple constraintTup, DropBehavior behavior,
+							  bool recurse, bool recursing, bool missing_ok, LOCKMODE lockmode)
+{
+	Relation	conrel;
+	Form_pg_constraint con;
+	ObjectAddress conobj;
+	List	   *children;
+	ListCell   *child;
+	bool		is_no_inherit_constraint = false;
+	bool		dropping_pk = false;
+	char	   *constrName;
+	List	   *unconstrained_cols = NIL;
+
+	conrel = table_open(ConstraintRelationId, RowExclusiveLock);
+
+	con = (Form_pg_constraint) GETSTRUCT(constraintTup);
+	constrName = NameStr(con->conname);
+
+	/* Don't drop inherited constraints */
+	if (con->coninhcount > 0 && !recursing)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+				 errmsg("cannot drop inherited constraint \"%s\" of relation \"%s\"",
+						constrName, RelationGetRelationName(rel))));
+
+	/*
+	 * See if we have a CHECK (IS NOT NULL) constraint or a PRIMARY KEY.  If
+	 * so, we have more checks and actions below, so obtain the list of
+	 * columns that are constrained by the constraint being dropped.
+	 */
+	if (con->contype == CONSTRAINT_CHECK)
+	{
+		AttrNumber	colnum = tryExtractNotNullFromCatalog(constraintTup);
+
+		if (colnum != InvalidAttrNumber)
+			unconstrained_cols = list_make1_int(colnum);
+	}
+	else if (con->contype == CONSTRAINT_PRIMARY)
+	{
+		Datum	adatum;
+		ArrayType *arr;
+		int		numkeys;
+		bool	isNull;
+		int16  *attnums;
+
+		dropping_pk = true;
+
+		adatum = heap_getattr(constraintTup, Anum_pg_constraint_conkey,
+							  RelationGetDescr(conrel), &isNull);
+		if (isNull)
+			elog(ERROR, "null conkey for constraint %u", con->oid);
+		arr = DatumGetArrayTypeP(adatum);	/* ensure not toasted */
+		numkeys = ARR_DIMS(arr)[0];
+		if (ARR_NDIM(arr) != 1 ||
+			numkeys < 0 ||
+			ARR_HASNULL(arr) ||
+			ARR_ELEMTYPE(arr) != INT2OID)
+			elog(ERROR, "conkey is not a 1-D smallint array");
+		attnums = (int16 *) ARR_DATA_PTR(arr);
+
+		for (int i = 0; i < numkeys; i++)
+			unconstrained_cols = lappend_int(unconstrained_cols, attnums[i]);
+	}
+
+	is_no_inherit_constraint = con->connoinherit;
+
+	/*
+	 * If it's a foreign-key constraint, we'd better lock the referenced
+	 * table and check that that's not in use, just as we've already done
+	 * for the constrained table (else we might, eg, be dropping a trigger
+	 * that has unfired events).  But we can/must skip that in the
+	 * self-referential case.
+	 */
+	if (con->contype == CONSTRAINT_FOREIGN &&
+		con->confrelid != RelationGetRelid(rel))
+	{
+		Relation	frel;
+
+		/* Must match lock taken by RemoveTriggerById: */
+		frel = table_open(con->confrelid, AccessExclusiveLock);
+		CheckTableNotInUse(frel, "ALTER TABLE");
+		table_close(frel, NoLock);
+	}
+
+	/*
+	 * Perform the actual constraint deletion
+	 */
+	ObjectAddressSet(conobj, ConstraintRelationId, con->oid);
+	performDeletion(&conobj, behavior, 0);
+
+	/*
+	 * If this was a CHECK (col IS NOT NULL) or the primary key, the
+	 * constrained columns must have had pg_attribute.attnotnull set.  See if
+	 * we need to reset it, and do so.
+	 */
+	if (unconstrained_cols)
+	{
+		Relation	attrel;
+		Bitmapset  *pkcols;
+		Bitmapset *ircols;
+		ListCell   *lc;
+
+		/* Make the above deletion visible */
+		CommandCounterIncrement();
+
+		attrel = table_open(AttributeRelationId, RowExclusiveLock);
+
+		/*
+		 * We want to test columns for their presence in the primary key, but
+		 * only if we're not dropping it.
+		 */
+		pkcols = dropping_pk ? NULL :
+			RelationGetIndexAttrBitmap(rel,
+									   INDEX_ATTR_BITMAP_PRIMARY_KEY);
+		ircols = RelationGetIndexAttrBitmap(rel, INDEX_ATTR_BITMAP_IDENTITY_KEY);
+
+		foreach(lc, unconstrained_cols)
+		{
+			AttrNumber	attnum = lfirst_int(lc);
+			HeapTuple	atttup;
+			HeapTuple	contup;
+			Form_pg_attribute attForm;
+
+			/*
+			 * Obtain pg_attribute tuple and verify conditions on it.  We use
+			 * a copy we can scribble on.
+			 */
+			atttup = SearchSysCacheCopyAttNum(RelationGetRelid(rel), attnum);
+			if (!HeapTupleIsValid(atttup))
+				elog(ERROR, "cache lookup failed for column %d", attnum);
+			attForm = (Form_pg_attribute) GETSTRUCT(atttup);
+
+			/*
+			 * Since the above deletion has been made visible, we can now
+			 * search for any remaining constraints on this column (or these
+			 * columns, in the case we're dropping a multicol primary key.)
+			 *
+			 * Then, verify whether any further NOT NULL constraints exist,
+			 * and reset attnotnull if none.  However, if this is a generated
+			 * identity column, abort the whole thing with a specific error
+			 * message, because the constraint is required in that case.
+			 *
+			 * Do not reset attnotnull if we still have a primary key and
+			 * the column in question is part of it.
+			 *
+			 * XXX reword this comment
+			 */
+			contup = findNotNullConstraintAttnum(rel, attnum, NULL);
+			if (contup ||
+				bms_is_member(attnum - FirstLowInvalidHeapAttributeNumber,
+							  pkcols))
+				continue;
+
+			/*
+			 * It's not valid to drop the last NOT NULL constraint for a
+			 * GENERATED AS IDENTITY column.
+			 */
+			if (attForm->attidentity)
+				ereport(ERROR,
+						errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+						errmsg("column \"%s\" of relation \"%s\" is an identity column",
+							   get_attname(RelationGetRelid(rel), attnum,
+										   false),
+							   RelationGetRelationName(rel)));
+
+			/*
+			 * It's not valid to drop the last NOT NULL constraint for the
+			 * replica identity either.  XXX make exception for FULL?
+			 */
+			if (bms_is_member(lfirst_int(lc) - FirstLowInvalidHeapAttributeNumber, ircols))
+				ereport(ERROR,
+						errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+						errmsg("column \"%s\" is in index used as replica identity",
+							   get_attname(RelationGetRelid(rel), lfirst_int(lc), false)));
+
+			/* Reset attnotnull */
+			if (attForm->attnotnull)
+			{
+				attForm->attnotnull = false;
+				CatalogTupleUpdate(attrel, &atttup->t_self, atttup);
+			}
+
+			/* XXX free the catalog tuples? */
 		}
+		table_close(attrel, RowExclusiveLock);
 	}
 
 	/*
 	 * For partitioned tables, non-CHECK inherited constraints are dropped via
 	 * the dependency mechanism, so we're done here.
 	 */
-	if (contype != CONSTRAINT_CHECK &&
+	if (con->contype != CONSTRAINT_CHECK &&
 		rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
 	{
 		table_close(conrel, RowExclusiveLock);
-		return;
+		return InvalidObjectAddress;	/* XXX address */
 	}
 
 	/*
@@ -11958,7 +12447,11 @@ ATExecDropConstraint(Relation rel, const char *constrName,
 	{
 		Oid			childrelid = lfirst_oid(child);
 		Relation	childrel;
+		HeapTuple	tuple;
 		HeapTuple	copy_tuple;
+		SysScanDesc	scan;
+		ScanKeyData skey[3];
+		/* XXX refactor this bit */
 
 		/* find_inheritance_children already got lock */
 		childrel = table_open(childrelid, NoLock);
@@ -12010,6 +12503,7 @@ ATExecDropConstraint(Relation rel, const char *constrName,
 			if (con->coninhcount == 1 && !con->conislocal)
 			{
 				/* Time to delete this child constraint, too */
+				/* XXX should recurse using ATExecDropConstraint_internal */
 				ATExecDropConstraint(childrel, constrName, behavior,
 									 true, true,
 									 false, lockmode);
@@ -12046,6 +12540,8 @@ ATExecDropConstraint(Relation rel, const char *constrName,
 	}
 
 	table_close(conrel, RowExclusiveLock);
+
+	return conobj;
 }
 
 /*
@@ -13366,10 +13862,11 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
 											 NIL,
 											 con->conname);
 				}
-				else if (cmd->subtype == AT_SetNotNull)
+				else if (cmd->subtype == AT_SetNotNull ||
+						 cmd->subtype == AT_SetAttNotNull)
 				{
 					/*
-					 * The parser will create AT_SetNotNull subcommands for
+					 * The parser will create AT_SetAttNotNull subcommands for
 					 * columns of PRIMARY KEY indexes/constraints, but we need
 					 * not do anything with them here, because the columns'
 					 * NOT NULL marks will already have been propagated into
@@ -15157,6 +15654,7 @@ MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel)
 	SysScanDesc parent_scan;
 	ScanKeyData parent_key;
 	HeapTuple	parent_tuple;
+	Oid			parent_relid = RelationGetRelid(parent_rel);
 	bool		child_is_partition = false;
 
 	catalog_relation = table_open(ConstraintRelationId, RowExclusiveLock);
@@ -15170,13 +15668,14 @@ MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel)
 	ScanKeyInit(&parent_key,
 				Anum_pg_constraint_conrelid,
 				BTEqualStrategyNumber, F_OIDEQ,
-				ObjectIdGetDatum(RelationGetRelid(parent_rel)));
+				ObjectIdGetDatum(parent_relid));
 	parent_scan = systable_beginscan(catalog_relation, ConstraintRelidTypidNameIndexId,
 									 true, NULL, 1, &parent_key);
 
 	while (HeapTupleIsValid(parent_tuple = systable_getnext(parent_scan)))
 	{
 		Form_pg_constraint parent_con = (Form_pg_constraint) GETSTRUCT(parent_tuple);
+		AttrNumber	parent_attnum;
 		SysScanDesc child_scan;
 		ScanKeyData child_key;
 		HeapTuple	child_tuple;
@@ -15189,6 +15688,81 @@ MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel)
 		if (parent_con->connoinherit)
 			continue;
 
+		/*
+		 * If the constraint is a NOT NULL one, verify that the child has any
+		 * NOT NULL constraint on the same column.
+		 */
+		parent_attnum = tryExtractNotNullFromCatalog(parent_tuple);
+		if (parent_attnum != InvalidAttrNumber)
+		{
+			AttrNumber	child_attnum;
+			Form_pg_attribute att;
+			char	   *colname;
+
+			/* XXX do we want to use an attrmap instead? */
+			colname = get_attname(parent_relid, parent_attnum, false);
+			child_attnum = get_attnum(RelationGetRelid(child_rel), colname);
+
+			if (child_attnum == InvalidAttrNumber)
+				elog(ERROR, "cache lookup failure for attribute «%s» of relation %u",
+					 get_attname(RelationGetRelid(parent_rel), parent_attnum, false),
+					 RelationGetRelid(parent_rel));
+
+			att = TupleDescAttr(RelationGetDescr(child_rel), child_attnum - 1);
+			if (att->attnotnull)
+			{
+				HeapTuple	conTup;
+				Form_pg_constraint childCon;
+
+				/*
+				 * OK, the column is marked NOT NULL, so search for the
+				 * corresponding pg_constraint row and mark it as a child of
+				 * this one.
+				 *
+				 * FIXME -- actually I think this fails if the column is
+				 * attnotnull because of a PK.
+				 */
+				conTup = findNotNullConstraintAttnum(child_rel, child_attnum, NULL);
+				if (conTup == NULL)		/* shouldn't happen */
+					elog(ERROR, "could not find CHECK (IS NOT NULL) constraint for column \"%s\"",
+						 colname);
+				childCon = (Form_pg_constraint) GETSTRUCT(conTup);
+
+				/*
+				 * If this is being done to a partitioned table, mark this
+				 * constraint as parent of the child's.  If not, increment
+				 * coninhcount.
+				 * XXX by doing this, we'll probably end with no constraint in
+				 * the partition if we ATTACH then DETACH.  Undesirable?
+				 * Reconsider this.
+				 */
+				if (child_is_partition)
+					ConstraintSetParentConstraint(childCon->oid,
+												  parent_con->oid,
+												  RelationGetRelid(child_rel));
+				else
+				{
+					HeapTuple	child_copy;
+
+					child_copy = heap_copytuple(conTup);
+					childCon = (Form_pg_constraint) GETSTRUCT(child_copy);
+					childCon->coninhcount++;
+
+					CatalogTupleUpdate(catalog_relation, &child_copy->t_self, child_copy);
+					heap_freetuple(child_copy);
+				}
+
+				/* All done */
+				heap_freetuple(conTup);
+				continue;
+			}
+
+			ereport(ERROR,
+					errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+					errmsg("column \"%s\" in child table must be marked NOT NULL",
+						   colname));
+		}
+
 		/* Search for a child constraint matching this one */
 		ScanKeyInit(&child_key,
 					Anum_pg_constraint_conrelid,
@@ -15521,6 +16095,21 @@ RemoveInheritance(Relation child_rel, Relation parent_rel, bool expect_detached)
 		if (con->contype != CONSTRAINT_CHECK)
 			continue;
 
+		/*
+		 * CHECK (IS NOT NULL) constraints use 'conparentid'.
+		 */
+		if (con->conparentid != InvalidOid)
+		{
+			ConstraintSetParentConstraint(con->oid,
+										  InvalidOid,
+										  RelationGetRelid(child_rel));
+			continue;
+		}
+
+		/*
+		 * Other CHECK constraints use the old-fashioned way of just setting
+		 * conislocal/coninhconut.  XXX this should be changed sometime.
+		 */
 		match = false;
 		foreach(lc, connames)
 		{
diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c
index 33b64fd279..fbf1be8ce4 100644
--- a/src/backend/commands/typecmds.c
+++ b/src/backend/commands/typecmds.c
@@ -52,6 +52,7 @@
 #include "catalog/pg_proc.h"
 #include "catalog/pg_range.h"
 #include "catalog/pg_type.h"
+#include "commands/constraint.h"
 #include "commands/defrem.h"
 #include "commands/tablecmds.h"
 #include "commands/typecmds.h"
@@ -1099,6 +1100,7 @@ DefineDomain(CreateDomainStmt *stmt)
 	foreach(listptr, schema)
 	{
 		Constraint *constr = lfirst(listptr);
+		Constraint *newck;
 
 		/* it must be a Constraint, per check above */
 
@@ -1110,6 +1112,18 @@ DefineDomain(CreateDomainStmt *stmt)
 									constr, domainName, NULL);
 				break;
 
+			case CONSTR_NOTNULL:
+				newck = makeCheckNotNullConstraint(domainNamespace,
+												   constr->conname,
+												   domainName,
+												   "value",
+												   false,
+												   InvalidOid);
+				domainAddConstraint(address.objectId, domainNamespace,
+									basetypeoid, basetypeMod,
+									newck, domainName, NULL);
+				break;
+
 				/* Other constraint types were fully processed above */
 
 			default:
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 6d283006e3..78227379b2 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -39,6 +39,7 @@
 #include "catalog/pg_operator.h"
 #include "catalog/pg_statistic_ext.h"
 #include "catalog/pg_type.h"
+#include "commands/constraint.h"
 #include "commands/comment.h"
 #include "commands/defrem.h"
 #include "commands/sequence.h"
@@ -83,6 +84,9 @@ typedef struct
 	List	   *ckconstraints;	/* CHECK constraints */
 	List	   *fkconstraints;	/* FOREIGN KEY constraints */
 	List	   *ixconstraints;	/* index-creating constraints */
+	List	   *notnulls_check;	/* list of columns to get an additional CHECK
+								 * (IS NOT NULL) constraint */
+	List	   *notnulls_nock;	/* list of columns implicitly NOT NULL */
 	List	   *likeclauses;	/* LIKE clauses that need post-processing */
 	List	   *extstats;		/* cloned extended statistics */
 	List	   *blist;			/* "before list" of things to do before
@@ -244,6 +248,8 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
 	cxt.ckconstraints = NIL;
 	cxt.fkconstraints = NIL;
 	cxt.ixconstraints = NIL;
+	cxt.notnulls_check = NIL;
+	cxt.notnulls_nock = NIL;
 	cxt.likeclauses = NIL;
 	cxt.extstats = NIL;
 	cxt.blist = NIL;
@@ -348,6 +354,8 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
 	 */
 	stmt->tableElts = cxt.columns;
 	stmt->constraints = cxt.ckconstraints;
+	stmt->notnull_check = cxt.notnulls_check;
+	stmt->notnull_bare = cxt.notnulls_nock;
 
 	result = lappend(cxt.blist, stmt);
 	result = list_concat(result, cxt.alist);
@@ -530,8 +538,8 @@ static void
 transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
 {
 	bool		is_serial;
-	bool		saw_nullable;
 	bool		saw_default;
+	bool		saw_nullable;
 	bool		saw_identity;
 	bool		saw_generated;
 	ListCell   *clist;
@@ -595,6 +603,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
 		TypeCast   *castnode;
 		FuncCall   *funccallnode;
 		Constraint *constraint;
+		ConstraintNotNull *notnull;
 
 		generateSerialExtraStmts(cxt, column,
 								 column->typeName->typeOid, NIL,
@@ -631,10 +640,11 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
 		constraint->cooked_expr = NULL;
 		column->constraints = lappend(column->constraints, constraint);
 
-		constraint = makeNode(Constraint);
-		constraint->contype = CONSTR_NOTNULL;
-		constraint->location = -1;
-		column->constraints = lappend(column->constraints, constraint);
+		/* have a NOT NULL constraint added later */
+		notnull = makeNode(ConstraintNotNull);
+		notnull->conname = NULL;
+		notnull->column = column->colname;
+		cxt->notnulls_check = lappend(cxt->notnulls_check, notnull);
 	}
 
 	/* Process column constraints, if any... */
@@ -648,6 +658,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
 	foreach(clist, column->constraints)
 	{
 		Constraint *constraint = lfirst_node(Constraint, clist);
+		char	   *colname;
 
 		switch (constraint->contype)
 		{
@@ -664,6 +675,11 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
 				break;
 
 			case CONSTR_NOTNULL:
+				/*
+				 * For NOT NULL declarations, we need to mark the column as
+				 * not nullable, and set things up to have a CHECK constraint
+				 * created.
+				 */
 				if (saw_nullable && !column->is_not_null)
 					ereport(ERROR,
 							(errcode(ERRCODE_SYNTAX_ERROR),
@@ -671,8 +687,24 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
 									column->colname, cxt->relation->relname),
 							 parser_errposition(cxt->pstate,
 												constraint->location)));
-				column->is_not_null = true;
-				saw_nullable = true;
+
+				/*
+				 * If this is the first time we see this column being marked
+				 * not null, keep track to later add a CHECK constraint.
+				 */
+				if (!column->is_not_null)
+				{
+					ConstraintNotNull    *notnull;
+
+					column->is_not_null = true;
+
+					notnull = makeNode(ConstraintNotNull);
+					notnull->conname = constraint->conname;
+					notnull->column = column->colname;
+
+					cxt->notnulls_check = lappend(cxt->notnulls_check, notnull);
+				}
+
 				break;
 
 			case CONSTR_DEFAULT:
@@ -722,7 +754,6 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
 					column->identity = constraint->generated_when;
 					saw_identity = true;
 
-					/* An identity column is implicitly NOT NULL */
 					if (saw_nullable && !column->is_not_null)
 						ereport(ERROR,
 								(errcode(ERRCODE_SYNTAX_ERROR),
@@ -730,7 +761,18 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
 										column->colname, cxt->relation->relname),
 								 parser_errposition(cxt->pstate,
 													constraint->location)));
-					column->is_not_null = true;
+					if (!column->is_not_null)
+					{
+						ConstraintNotNull	*notnull;
+
+						column->is_not_null = true;
+
+						notnull = makeNode(ConstraintNotNull);
+						notnull->conname = NULL;
+						notnull->column = column->colname;
+						cxt->notnulls_check = lappend(cxt->notnulls_check,
+													  notnull);
+					}
 					saw_nullable = true;
 					break;
 				}
@@ -760,6 +802,40 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
 
 			case CONSTR_CHECK:
 				cxt->ckconstraints = lappend(cxt->ckconstraints, constraint);
+
+				/*
+				 * If there is a CHECK (foo IS NOT NULL) constraint
+				 * declaration, we check the column name used in the
+				 * constraint.  If it's the same name as the column being
+				 * defined, check there's no IS NULL already, and set
+				 * saw_isnotnull in the column definition to conflict with any
+				 * future one.  In any case, save the column name so that it
+				 * gets an attnotnull marker.  We only need to do this for
+				 * constraint that aren't NOT VALID, however.
+				 */
+				if (constraint->initially_valid)
+				{
+					colname = tryExtractNotNullFromNode((Node *) constraint, NULL);
+					if (colname != NULL)
+					{
+						cxt->notnulls_nock = lappend(cxt->notnulls_nock,
+													 makeString(pstrdup(colname)));
+
+						if (strcmp(colname, column->colname) == 0)
+						{
+							if (saw_nullable && !column->is_not_null)
+								ereport(errcode(ERRCODE_SYNTAX_ERROR),
+										errmsg("conflicting NULL/NOT NULL declarations for column \"%s\" of table \"%s\"",
+											   column->colname, cxt->relation->relname),
+										parser_errposition(cxt->pstate,
+														   constraint->location));
+
+							column->is_not_null = true;
+							saw_nullable = true;
+						}
+					}
+				}
+
 				break;
 
 			case CONSTR_PRIMARY:
@@ -875,6 +951,8 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
 static void
 transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
 {
+	char   *colname;
+
 	switch (constraint->contype)
 	{
 		case CONSTR_PRIMARY:
@@ -915,6 +993,13 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
 
 		case CONSTR_CHECK:
 			cxt->ckconstraints = lappend(cxt->ckconstraints, constraint);
+			if (constraint->initially_valid)
+			{
+				colname = tryExtractNotNullFromNode((Node *) constraint, cxt->rel);
+				if (colname != NULL)
+					cxt->notnulls_nock = lappend(cxt->notnulls_nock,
+												 makeString(pstrdup(colname)));
+			}
 			break;
 
 		case CONSTR_FOREIGN:
@@ -964,6 +1049,7 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
 	AclResult	aclresult;
 	char	   *comment;
 	ParseCallbackState pcbstate;
+	bool		process_notnull_constraints;
 
 	setup_parser_errposition_callback(&pcbstate, cxt->pstate,
 									  table_like_clause->relation->location);
@@ -1045,6 +1131,8 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
 		def->inhcount = 0;
 		def->is_local = true;
 		def->is_not_null = attribute->attnotnull;
+		if (attribute->attnotnull)
+			process_notnull_constraints = true;
 		def->is_from_type = false;
 		def->storage = 0;
 		def->raw_default = NULL;
@@ -1126,14 +1214,19 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
 	 * we don't yet know what column numbers the copied columns will have in
 	 * the finished table.  If any of those options are specified, add the
 	 * LIKE clause to cxt->likeclauses so that expandTableLikeClause will be
-	 * called after we do know that.  Also, remember the relation OID so that
+	 * called after we do know that; in addition, do that if there are any NOT
+	 * NULL constraints, because those must be propagated even if not
+	 * explicitly requested.
+	 *
+	 * In order for this to work, we remember the relation OID so that
 	 * expandTableLikeClause is certain to open the same table.
 	 */
-	if (table_like_clause->options &
+	if ((table_like_clause->options &
 		(CREATE_TABLE_LIKE_DEFAULTS |
 		 CREATE_TABLE_LIKE_GENERATED |
 		 CREATE_TABLE_LIKE_CONSTRAINTS |
-		 CREATE_TABLE_LIKE_INDEXES))
+		 CREATE_TABLE_LIKE_INDEXES)) ||
+		process_notnull_constraints)
 	{
 		table_like_clause->relationOid = RelationGetRelid(relation);
 		cxt->likeclauses = lappend(cxt->likeclauses, table_like_clause);
@@ -1312,8 +1405,7 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause)
 	 * Copy CHECK constraints if requested, being careful to adjust attribute
 	 * numbers so they match the child.
 	 */
-	if ((table_like_clause->options & CREATE_TABLE_LIKE_CONSTRAINTS) &&
-		constr != NULL)
+	if (constr != NULL)
 	{
 		int			ccnum;
 
@@ -1322,15 +1414,18 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause)
 			char	   *ccname = constr->check[ccnum].ccname;
 			char	   *ccbin = constr->check[ccnum].ccbin;
 			bool		ccnoinherit = constr->check[ccnum].ccnoinherit;
-			Node	   *ccbin_node;
+			Node	   *ccnode_parent;
+			Node	   *ccnode_newrel;
 			bool		found_whole_row;
+			char	   *colname;
 			Constraint *n;
 			AlterTableCmd *atsubcmd;
 
-			ccbin_node = map_variable_attnos(stringToNode(ccbin),
-											 1, 0,
-											 attmap,
-											 InvalidOid, &found_whole_row);
+			ccnode_parent = stringToNode(ccbin);
+			ccnode_newrel = map_variable_attnos(ccnode_parent,
+												1, 0,
+												attmap,
+												InvalidOid, &found_whole_row);
 
 			/*
 			 * We reject whole-row variables because the whole point of LIKE
@@ -1346,13 +1441,23 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause)
 								   ccname,
 								   RelationGetRelationName(relation))));
 
+			/*
+			 * NOT NULL constraints must be copied regardless of whether
+			 * INCLUDING CONSTRAINTS was given, per the SQL standard; if that
+			 * option was not given, skip other constraints.
+			 */
+			colname = tryExtractNotNullFromNode(ccnode_parent, relation);
+			if (!(table_like_clause->options & CREATE_TABLE_LIKE_CONSTRAINTS) &&
+				!colname)
+				continue;
+
 			n = makeNode(Constraint);
 			n->contype = CONSTR_CHECK;
 			n->conname = pstrdup(ccname);
 			n->location = -1;
 			n->is_no_inherit = ccnoinherit;
 			n->raw_expr = NULL;
-			n->cooked_expr = nodeToString(ccbin_node);
+			n->cooked_expr = nodeToString(ccnode_newrel);
 
 			/* We can skip validation, since the new table should be empty. */
 			n->skip_validation = true;
@@ -2069,10 +2174,12 @@ transformIndexConstraints(CreateStmtContext *cxt)
 	ListCell   *lc;
 
 	/*
-	 * Run through the constraints that need to generate an index. For PRIMARY
-	 * KEY, mark each column as NOT NULL and create an index. For UNIQUE or
-	 * EXCLUDE, create an index as for PRIMARY KEY, but do not insist on NOT
-	 * NULL.
+	 * Run through the constraints that need to generate an index, and do so.
+	 *
+	 * For PRIMARY KEY, in addition we set each column's attnotnull flag true.
+	 * We do not create a separate CHECK (IS NOT NULL) constraint, as that
+	 * would be redundant: the PRIMARY KEY constraint ktself fulfills that
+	 * role.  Other constraint types don't need any NOT NULL markings.
 	 */
 	foreach(lc, cxt->ixconstraints)
 	{
@@ -2146,9 +2253,7 @@ transformIndexConstraints(CreateStmtContext *cxt)
 	}
 
 	/*
-	 * Now append all the IndexStmts to cxt->alist.  If we generated an ALTER
-	 * TABLE SET NOT NULL statement to support a primary key, it's already in
-	 * cxt->alist.
+	 * Now append all the IndexStmts to cxt->alist.
 	 */
 	cxt->alist = list_concat(cxt->alist, finalindexlist);
 }
@@ -2156,18 +2261,15 @@ transformIndexConstraints(CreateStmtContext *cxt)
 /*
  * transformIndexConstraint
  *		Transform one UNIQUE, PRIMARY KEY, or EXCLUDE constraint for
- *		transformIndexConstraints.
+ *		transformIndexConstraints. We return an IndexStmt.
  *
- * We return an IndexStmt.  For a PRIMARY KEY constraint, we additionally
- * produce NOT NULL constraints, either by marking ColumnDefs in cxt->columns
- * as is_not_null or by adding an ALTER TABLE SET NOT NULL command to
- * cxt->alist.
+ * For a PRIMARY KEY constraint, we additionally force the columns to be
+ * marked as NOT NULL, without producing a CHECK (IS NOT NULL) constraint.
  */
 static IndexStmt *
 transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt)
 {
 	IndexStmt  *index;
-	List	   *notnullcmds = NIL;
 	ListCell   *lc;
 
 	index = makeNode(IndexStmt);
@@ -2449,14 +2551,14 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt)
 				 * column is defined in the new table.  For PRIMARY KEY, we
 				 * can apply the NOT NULL constraint cheaply here ... unless
 				 * the column is marked is_from_type, in which case marking it
-				 * here would be ineffective (see MergeAttributes).
+				 * here would be ineffective (see MergeAttributes).  Note that
+				 * this isn't effective in ALTER TABLE either, unless the
+				 * column is being added in the same command.
 				 */
 				if (constraint->contype == CONSTR_PRIMARY &&
 					!column->is_from_type)
-				{
-					column->is_not_null = true;
-					forced_not_null = true;
-				}
+					cxt->notnulls_nock = lappend(cxt->notnulls_nock,
+												 makeString(pstrdup(key)));
 			}
 			else if (SystemAttributeByName(key) != NULL)
 			{
@@ -2560,17 +2662,13 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt)
 			index->indexParams = lappend(index->indexParams, iparam);
 
 			/*
-			 * For a primary-key column, also create an item for ALTER TABLE
-			 * SET NOT NULL if we couldn't ensure it via is_not_null above.
+			 * For a primary-key column, also have it be marked attnotnull
+			 * later without creating a CHECK constraint for it (the
+			 * PRIMARY KEY fulfills that role already).
 			 */
 			if (constraint->contype == CONSTR_PRIMARY && !forced_not_null)
-			{
-				AlterTableCmd *notnullcmd = makeNode(AlterTableCmd);
-
-				notnullcmd->subtype = AT_SetNotNull;
-				notnullcmd->name = pstrdup(key);
-				notnullcmds = lappend(notnullcmds, notnullcmd);
-			}
+				cxt->notnulls_nock = lappend(cxt->notnulls_nock,
+											 makeString(pstrdup(key)));
 		}
 	}
 
@@ -2672,22 +2770,6 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt)
 		index->indexIncludingParams = lappend(index->indexIncludingParams, iparam);
 	}
 
-	/*
-	 * If we found anything that requires run-time SET NOT NULL, build a full
-	 * ALTER TABLE command for that and add it to cxt->alist.
-	 */
-	if (notnullcmds)
-	{
-		AlterTableStmt *alterstmt = makeNode(AlterTableStmt);
-
-		alterstmt->relation = copyObject(cxt->relation);
-		alterstmt->cmds = notnullcmds;
-		alterstmt->objtype = OBJECT_TABLE;
-		alterstmt->missing_ok = false;
-
-		cxt->alist = lappend(cxt->alist, alterstmt);
-	}
-
 	return index;
 }
 
@@ -3345,6 +3427,8 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt,
 	cxt.ckconstraints = NIL;
 	cxt.fkconstraints = NIL;
 	cxt.ixconstraints = NIL;
+	cxt.notnulls_check = NIL;
+	cxt.notnulls_nock = NIL;
 	cxt.likeclauses = NIL;
 	cxt.extstats = NIL;
 	cxt.blist = NIL;
@@ -3564,6 +3648,27 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt,
 		}
 	}
 
+	/*
+	 * Add CHECK constraints to match NOT NULL declarations from various
+	 * sources.
+	 *
+	 * Cannot do it at this point: may not know is_row yet.
+	 */
+	foreach(l, cxt.notnulls_check)
+	{
+		ConstraintNotNull *notnull = lfirst(l);
+		Constraint *newconstr;
+		bool		is_row = false; /* FIXME */
+
+		newconstr = makeCheckNotNullConstraint(RelationGetNamespace(rel),
+											   notnull->conname,
+											   RelationGetRelationName(rel),
+											   notnull->column,
+											   is_row,
+											   InvalidOid);
+		cxt.ckconstraints = lappend(cxt.ckconstraints, newconstr);
+	}
+
 	/*
 	 * Transfer anything we already have in cxt.alist into save_alist, to keep
 	 * it separate from the output of transformIndexConstraints.
@@ -3576,6 +3681,15 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt,
 	transformFKConstraints(&cxt, skipValidation, true);
 	transformCheckConstraints(&cxt, false);
 
+	/* have attnotnull set for columns that need it */
+	foreach(l, cxt.notnulls_nock)
+	{
+		newcmd = makeNode(AlterTableCmd);
+		newcmd->subtype = AT_SetAttNotNull;
+		newcmd->name = strVal(lfirst(l));
+		newcmds = lappend(newcmds, newcmd);
+	}
+
 	/*
 	 * Push any index-creation commands into the ALTER, so that they can be
 	 * scheduled nicely by tablecmds.c.  Note that tablecmds.c assumes that
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 00dc0f2403..865ca880f6 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -4550,6 +4550,7 @@ CheckConstraintFetch(Relation relation)
 			break;
 		}
 
+		check[found].ccoid = conform->oid;
 		check[found].ccvalid = conform->convalidated;
 		check[found].ccnoinherit = conform->connoinherit;
 		check[found].ccname = MemoryContextStrdup(CacheMemoryContext,
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 67b6d9079e..1aade47987 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -8175,7 +8175,6 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
 						 "a.attstattarget,\n"
 						 "a.attstorage,\n"
 						 "t.typstorage,\n"
-						 "a.attnotnull,\n"
 						 "a.atthasdef,\n"
 						 "a.attisdropped,\n"
 						 "a.attlen,\n"
@@ -8192,6 +8191,17 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
 						 "ORDER BY option_name"
 						 "), E',\n    ') AS attfdwoptions,\n");
 
+	/*
+	 * Versions 16 and up have pg_constraint rows for NOT NULL constraints, so
+	 * we don't need to handle them separately here.
+	 */
+	if (fout->remoteVersion < 160000)
+		appendPQExpBufferStr(q,
+							 "a.attnotnull,\n");
+	else
+		appendPQExpBufferStr(q,
+							 "false as attnotnull,\n");
+
 	if (fout->remoteVersion >= 140000)
 		appendPQExpBufferStr(q,
 							 "a.attcompression AS attcompression,\n");
@@ -10866,7 +10876,12 @@ dumpDomain(Archive *fout, const TypeInfo *tyinfo)
 		appendPQExpBufferStr(query,
 							 "PREPARE dumpDomain(pg_catalog.oid) AS\n");
 
-		appendPQExpBufferStr(query, "SELECT t.typnotnull, "
+		appendPQExpBufferStr(query, "SELECT ");
+		if (fout->remoteVersion >= 160000)
+			appendPQExpBufferStr(query, "false as typnotnull, ");
+		else
+			appendPQExpBufferStr(query, "t.typnotnull, ");
+		appendPQExpBufferStr(query,
 							 "pg_catalog.format_type(t.typbasetype, t.typtypmod) AS typdefn, "
 							 "pg_catalog.pg_get_expr(t.typdefaultbin, 'pg_catalog.pg_type'::pg_catalog.regclass) AS typdefaultbin, "
 							 "t.typdefault, "
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 2873b662fb..b2f79faecf 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -2629,11 +2629,12 @@ my %tests = (
 					   ) WITH (autovacuum_enabled = false, fillfactor=80);',
 		regexp => qr/^
 			\QCREATE TABLE dump_test.test_table (\E\n
-			\s+\Qcol1 integer NOT NULL,\E\n
+			\s+\Qcol1 integer,\E\n
 			\s+\Qcol2 text,\E\n
 			\s+\Qcol3 text,\E\n
 			\s+\Qcol4 text,\E\n
-			\s+\QCONSTRAINT test_table_col1_check CHECK ((col1 <= 1000))\E\n
+			\s+\QCONSTRAINT test_table_col1_check CHECK ((col1 <= 1000)),\E\n
+			\s+\QCONSTRAINT test_table_col1_not_null CHECK ((col1 IS NOT NULL))\E\n
 			\Q)\E\n
 			\QWITH (autovacuum_enabled='false', fillfactor='80');\E\n/xm,
 		like => {
@@ -2655,7 +2656,7 @@ my %tests = (
 					   );',
 		regexp => qr/^
 			\QCREATE TABLE dump_test.fk_reference_test_table (\E
-			\n\s+\Qcol1 integer NOT NULL\E
+			\n\s+\Qcol1 integer\E
 			\n\);
 			/xm,
 		like =>
@@ -2713,10 +2714,12 @@ my %tests = (
 			\Q-- Name: measurement;\E.*\n
 			\Q--\E\n\n
 			\QCREATE TABLE dump_test.measurement (\E\n
-			\s+\Qcity_id integer NOT NULL,\E\n
-			\s+\Qlogdate date NOT NULL,\E\n
+			\s+\Qcity_id integer,\E\n
+			\s+\Qlogdate date,\E\n
 			\s+\Qpeaktemp integer,\E\n
 			\s+\Qunitsales integer,\E\n
+			\s+\QCONSTRAINT measurement_city_id_not_null CHECK ((city_id IS NOT NULL)),\E\n
+			\s+\QCONSTRAINT measurement_logdate_not_null CHECK ((logdate IS NOT NULL)),\E\n
 			\s+\QCONSTRAINT measurement_peaktemp_check CHECK ((peaktemp >= '-460'::integer))\E\n
 			\)\n
 			\QPARTITION BY RANGE (logdate);\E\n
@@ -2739,10 +2742,12 @@ my %tests = (
 						FOR VALUES FROM (\'2006-02-01\') TO (\'2006-03-01\');',
 		regexp => qr/^
 			\QCREATE TABLE dump_test_second_schema.measurement_y2006m2 (\E\n
-			\s+\Qcity_id integer DEFAULT nextval('dump_test.measurement_city_id_seq'::regclass) NOT NULL,\E\n
-			\s+\Qlogdate date NOT NULL,\E\n
+			\s+\Qcity_id integer DEFAULT nextval('dump_test.measurement_city_id_seq'::regclass),\E\n
+			\s+\Qlogdate date,\E\n
 			\s+\Qpeaktemp integer,\E\n
 			\s+\Qunitsales integer DEFAULT 0,\E\n
+			\s+\QCONSTRAINT measurement_city_id_not_null CHECK ((city_id IS NOT NULL)),\E\n
+			\s+\QCONSTRAINT measurement_logdate_not_null CHECK ((logdate IS NOT NULL)),\E\n
 			\s+\QCONSTRAINT measurement_peaktemp_check CHECK ((peaktemp >= '-460'::integer)),\E\n
 			\s+\QCONSTRAINT measurement_y2006m2_unitsales_check CHECK ((unitsales >= 0))\E\n
 			\);\n
@@ -2941,8 +2946,9 @@ my %tests = (
 					   );',
 		regexp => qr/^
 			\QCREATE TABLE dump_test.test_table_identity (\E\n
-			\s+\Qcol1 integer NOT NULL,\E\n
-			\s+\Qcol2 text\E\n
+			\s+\Qcol1 integer,\E\n
+			\s+\Qcol2 text,\E\n
+			\s+\QCONSTRAINT test_table_identity_col1_not_null CHECK ((col1 IS NOT NULL))\E\n
 			\);
 			.*
 			\QALTER TABLE dump_test.test_table_identity ALTER COLUMN col1 ADD GENERATED ALWAYS AS IDENTITY (\E\n
@@ -2967,7 +2973,7 @@ my %tests = (
 					   );',
 		regexp => qr/^
 			\QCREATE TABLE dump_test.test_table_generated (\E\n
-			\s+\Qcol1 integer NOT NULL,\E\n
+			\s+\Qcol1 integer,\E\n
 			\s+\Qcol2 integer GENERATED ALWAYS AS ((col1 * 2)) STORED\E\n
 			\);
 			/xms,
@@ -2982,6 +2988,7 @@ my %tests = (
 						 INHERITS (dump_test.test_table_generated);',
 		regexp => qr/^
 			\QCREATE TABLE dump_test.test_table_generated_child1 (\E\n
+			\s+\QCONSTRAINT test_table_generated_child1_col1_not_null CHECK ((col1 IS NOT NULL))\E\n
 			\)\n
 			\QINHERITS (dump_test.test_table_generated);\E\n
 			/xms,
@@ -3010,7 +3017,8 @@ my %tests = (
 		regexp => qr/^
 			\QCREATE TABLE dump_test.test_table_generated_child2 (\E\n
 			\s+\Qcol1 integer,\E\n
-			\s+\Qcol2 integer\E\n
+			\s+\Qcol2 integer,\E\n
+			\s+\QCONSTRAINT test_table_generated_child2_col1_not_null CHECK ((col1 IS NOT NULL))\E\n
 			\)\n
 			\QINHERITS (dump_test.test_table_generated);\E\n
 			/xms,
@@ -3052,8 +3060,9 @@ my %tests = (
 						 );',
 		regexp => qr/^
 		\QCREATE TABLE dump_test.test_inheritance_parent (\E\n
-		\s+\Qcol1 integer NOT NULL,\E\n
+		\s+\Qcol1 integer,\E\n
 		\s+\Qcol2 integer,\E\n
+		\s+\QCONSTRAINT test_inheritance_parent_col1_not_null CHECK ((col1 IS NOT NULL)),\E\n
 		\s+\QCONSTRAINT test_inheritance_parent_col2_check CHECK ((col2 >= 42))\E\n
 		\Q);\E\n
 		/xm,
@@ -3071,7 +3080,8 @@ my %tests = (
 		regexp => qr/^
 		\QCREATE TABLE dump_test.test_inheritance_child (\E\n
 		\s+\Qcol1 integer,\E\n
-		\s+\QCONSTRAINT test_inheritance_child CHECK ((col2 >= 142857))\E\n
+		\s+\QCONSTRAINT test_inheritance_child CHECK ((col2 >= 142857)),\E\n
+		\s+\QCONSTRAINT test_inheritance_child_col1_not_null CHECK ((col1 IS NOT NULL))\E\n
 		\)\n
 		\QINHERITS (dump_test.test_inheritance_parent);\E\n
 		/xm,
diff --git a/src/include/access/tupdesc.h b/src/include/access/tupdesc.h
index 28dd6de18b..2ff0006864 100644
--- a/src/include/access/tupdesc.h
+++ b/src/include/access/tupdesc.h
@@ -27,6 +27,7 @@ typedef struct AttrDefault
 
 typedef struct ConstrCheck
 {
+	Oid			ccoid;			/* pg_constraint OID */
 	char	   *ccname;
 	char	   *ccbin;			/* nodeToString representation of expr */
 	bool		ccvalid;
diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h
index 5774c46471..d110ba2b79 100644
--- a/src/include/catalog/heap.h
+++ b/src/include/catalog/heap.h
@@ -37,6 +37,7 @@ typedef struct CookedConstraint
 	ConstrType	contype;		/* CONSTR_DEFAULT or CONSTR_CHECK */
 	Oid			conoid;			/* constr OID if created, otherwise Invalid */
 	char	   *name;			/* name, or NULL if none */
+	Oid			parent_oid;		/* constr OID of parent, if any */
 	AttrNumber	attnum;			/* which attr (only for DEFAULT) */
 	Node	   *expr;			/* transformed default or check expr */
 	bool		skip_validation;	/* skip validation? (only for CHECK) */
diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h
index e7d967f137..7fe75816f9 100644
--- a/src/include/catalog/pg_constraint.h
+++ b/src/include/catalog/pg_constraint.h
@@ -237,9 +237,6 @@ extern Oid	CreateConstraintEntry(const char *constraintName,
 								  bool conNoInherit,
 								  bool is_internal);
 
-extern void RemoveConstraintById(Oid conId);
-extern void RenameConstraintById(Oid conId, const char *newname);
-
 extern bool ConstraintNameIsUsed(ConstraintCategory conCat, Oid objId,
 								 const char *conname);
 extern bool ConstraintNameExists(const char *conname, Oid namespaceid);
@@ -247,6 +244,14 @@ extern char *ChooseConstraintName(const char *name1, const char *name2,
 								  const char *label, Oid namespaceid,
 								  List *others);
 
+extern HeapTuple findNotNullConstraintAttnum(Relation rel, AttrNumber attnum,
+											 bool *report_multiple);
+extern HeapTuple findNotNullConstraint(Relation rel, const char *colname,
+									   bool *report_multiple);
+
+extern void RemoveConstraintById(Oid conId);
+extern void RenameConstraintById(Oid conId, const char *newname);
+
 extern void AlterConstraintNamespaces(Oid ownerId, Oid oldNspId,
 									  Oid newNspId, bool isType, ObjectAddresses *objsMoved);
 extern void ConstraintSetParentConstraint(Oid childConstrId,
diff --git a/src/include/commands/constraint.h b/src/include/commands/constraint.h
new file mode 100644
index 0000000000..b6720060d7
--- /dev/null
+++ b/src/include/commands/constraint.h
@@ -0,0 +1,30 @@
+/*-------------------------------------------------------------------------
+ *
+ * constraint.h
+ *   PostgreSQL CONSTRAINT support declarations
+ *
+ * Portions Copyright (c) 1996-2022, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * IDENTIFICATION
+ *   src/include/commands/constraint.h
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef CONSTRAINT_H
+#define CONSTRAINT_H
+
+#include "nodes/parsenodes.h"
+#include "utils/relcache.h"
+
+extern Constraint *makeCheckNotNullConstraint(Oid nspid,
+											  char *constraint_name,
+											  const char *relname,
+											  const char *colname,
+											  bool is_row,
+											  Oid parent_oid);
+
+extern char *tryExtractNotNullFromNode(Node *node, Relation rel);
+extern AttrNumber tryExtractNotNullFromCatalog(HeapTuple constrTup);
+
+#endif /* CONSTRAINT_H */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 6958306a7d..42940b97b7 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1955,6 +1955,7 @@ typedef enum AlterTableType
 	AT_CookedColumnDefault,		/* add a pre-cooked column default */
 	AT_DropNotNull,				/* alter column drop not null */
 	AT_SetNotNull,				/* alter column set not null */
+	AT_SetAttNotNull,			/* set not null, without CHECK constraint */
 	AT_DropExpression,			/* alter column drop expression */
 	AT_CheckNotNull,			/* check column is already marked not null */
 	AT_SetStatistics,			/* alter column set statistics */
@@ -2243,10 +2244,11 @@ typedef struct VariableShowStmt
  *		Create Table Statement
  *
  * NOTE: in the raw gram.y output, ColumnDef and Constraint nodes are
- * intermixed in tableElts, and constraints is NIL.  After parse analysis,
- * tableElts contains just ColumnDefs, and constraints contains just
- * Constraint nodes (in fact, only CONSTR_CHECK nodes, in the present
- * implementation).
+ * intermixed in tableElts, and constraints and notnullcols are NIL.  After
+ * parse analysis, tableElts contains just ColumnDefs, notnullcols has been
+ * filled with not-nullable column names from various sources, and constraints
+ * contains just Constraint nodes (in fact, only CONSTR_CHECK nodes, in the
+ * present implementation).
  * ----------------------
  */
 
@@ -2261,6 +2263,11 @@ typedef struct CreateStmt
 	PartitionSpec *partspec;	/* PARTITION BY clause */
 	TypeName   *ofTypename;		/* OF typename */
 	List	   *constraints;	/* constraints (list of Constraint nodes) */
+	List	   *notnull_check;	/* list of column names for which to add a
+								 * CHECK (IS NOT NULL) constraint for */
+	List	   *notnull_bare;	/* list of column names for which to set the
+								 * attnotnull flag without a CHECK
+								 * constraint */
 	List	   *options;		/* options from WITH clause */
 	OnCommitAction oncommit;	/* what do we do at COMMIT? */
 	char	   *tablespacename; /* table space to use, or NULL */
@@ -2342,6 +2349,7 @@ typedef struct Constraint
 	bool		deferrable;		/* DEFERRABLE? */
 	bool		initdeferred;	/* INITIALLY DEFERRED? */
 	int			location;		/* token location, or -1 if unknown */
+	Oid			parent_oid;		/* OID of parent constraint, if any */
 
 	/* Fields used for constraints with expressions (CHECK and DEFAULT): */
 	bool		is_no_inherit;	/* is constraint non-inheritable? */
@@ -2386,6 +2394,14 @@ typedef struct Constraint
 	bool		initially_valid;	/* mark the new constraint as valid? */
 } Constraint;
 
+typedef struct ConstraintNotNull
+{
+	NodeTag		type;
+
+	char	   *conname;		/* Constraint name, or NULL if unnamed */
+	char	   *column;			/* column on which it applies */
+} ConstraintNotNull;
+
 /* ----------------------
  *		Create/Drop Table Space Statements
  * ----------------------
diff --git a/src/pl/plpgsql/src/expected/plpgsql_varprops.out b/src/pl/plpgsql/src/expected/plpgsql_varprops.out
index 25115a02bd..6db1ab1093 100644
--- a/src/pl/plpgsql/src/expected/plpgsql_varprops.out
+++ b/src/pl/plpgsql/src/expected/plpgsql_varprops.out
@@ -255,8 +255,8 @@ begin
   x := null;  -- fail
 end$$;
 NOTICE:  x = (1,2)
-ERROR:  domain var_record_nn does not allow null values
-CONTEXT:  PL/pgSQL function inline_code_block line 6 at assignment
+ERROR:  value for domain var_record_nn violates check constraint "var_record_nn_value_not_null"
+CONTEXT:  PL/pgSQL function inline_code_block line 5 at assignment
 do $$
 declare x var_record_colnn;  -- fail
 begin
diff --git a/src/test/modules/test_pg_dump/t/001_base.pl b/src/test/modules/test_pg_dump/t/001_base.pl
index f5da6bf46d..b89228b51c 100644
--- a/src/test/modules/test_pg_dump/t/001_base.pl
+++ b/src/test/modules/test_pg_dump/t/001_base.pl
@@ -289,8 +289,9 @@ my %tests = (
 		  'ALTER EXTENSION test_pg_dump ADD TABLE regress_pg_dump_table_added;',
 		regexp => qr/^
 			\QCREATE TABLE public.regress_pg_dump_table_added (\E
-			\n\s+\Qcol1 integer NOT NULL,\E
-			\n\s+\Qcol2 integer\E
+			\n\s+\Qcol1 integer,\E
+			\n\s+\Qcol2 integer,\E
+			\n\s+\QCONSTRAINT regress_pg_dump_table_added_col1_not_null CHECK ((col1 IS NOT NULL))\E
 			\n\);\n/xm,
 		like => { binary_upgrade => 1, },
 	},
@@ -375,8 +376,9 @@ my %tests = (
 		  'CREATE TABLE regress_pg_dump_table_added (col1 int not null, col2 int);',
 		regexp => qr/^
 			\QCREATE TABLE public.regress_pg_dump_table_added (\E
-			\n\s+\Qcol1 integer NOT NULL,\E
-			\n\s+\Qcol2 integer\E
+			\n\s+\Qcol1 integer,\E
+			\n\s+\Qcol2 integer,\E
+			\n\s+\QCONSTRAINT regress_pg_dump_table_added_col1_not_null CHECK ((col1 IS NOT NULL))\E
 			\n\);\n/xm,
 		like => { binary_upgrade => 1, },
 	},
@@ -411,8 +413,9 @@ my %tests = (
 	'CREATE TABLE regress_pg_dump_table' => {
 		regexp => qr/^
 			\QCREATE TABLE public.regress_pg_dump_table (\E
-			\n\s+\Qcol1 integer NOT NULL,\E
+			\n\s+\Qcol1 integer,\E
 			\n\s+\Qcol2 integer,\E
+			\n\s+\QCONSTRAINT regress_pg_dump_table_col1_not_null CHECK ((col1 IS NOT NULL)),\E
 			\n\s+\QCONSTRAINT regress_pg_dump_table_col2_check CHECK ((col2 > 0))\E
 			\n\);\n/xm,
 		like => { binary_upgrade => 1, },
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index d63f4f1cba..dd37c6ab22 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -1119,9 +1119,13 @@ ERROR:  relation "non_existent" does not exist
 create table atacc1 (test int not null);
 alter table atacc1 add constraint "atacc1_pkey" primary key (test);
 alter table atacc1 alter column test drop not null;
-ERROR:  column "test" is in a primary key
 alter table atacc1 drop constraint "atacc1_pkey";
-alter table atacc1 alter column test drop not null;
+\d atacc1
+               Table "public.atacc1"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ test   | integer |           |          | 
+
 insert into atacc1 values (null);
 alter table atacc1 alter test set not null;
 ERROR:  column "test" of relation "atacc1" contains null values
@@ -3235,7 +3239,7 @@ CREATE TABLE test_drop_constr_child () INHERITS (test_drop_constr_parent);
 ALTER TABLE ONLY test_drop_constr_parent DROP CONSTRAINT "test_drop_constr_parent_c_check";
 -- should fail
 INSERT INTO test_drop_constr_child (c) VALUES (NULL);
-ERROR:  new row for relation "test_drop_constr_child" violates check constraint "test_drop_constr_parent_c_check"
+ERROR:  null value in column "c" of relation "test_drop_constr_child" violates not-null constraint
 DETAIL:  Failing row contains (null).
 DROP TABLE test_drop_constr_parent CASCADE;
 NOTICE:  drop cascades to table test_drop_constr_child
@@ -3677,6 +3681,7 @@ Indexes:
     "test_add_column_pkey" PRIMARY KEY, btree (c3)
 Check constraints:
     "test_add_column_c5_check" CHECK (c5 > 8)
+    "test_add_column_c5_not_null" CHECK (c5 IS NOT NULL)
 Foreign-key constraints:
     "test_add_column_c4_fkey" FOREIGN KEY (c4) REFERENCES test_add_column(c3)
 Referenced by:
@@ -3698,6 +3703,7 @@ Indexes:
     "test_add_column_pkey" PRIMARY KEY, btree (c3)
 Check constraints:
     "test_add_column_c5_check" CHECK (c5 > 8)
+    "test_add_column_c5_not_null" CHECK (c5 IS NOT NULL)
 Foreign-key constraints:
     "test_add_column_c4_fkey" FOREIGN KEY (c4) REFERENCES test_add_column(c3)
 Referenced by:
diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expected/cluster.out
index 542c2e098c..a666d89ef5 100644
--- a/src/test/regress/expected/cluster.out
+++ b/src/test/regress/expected/cluster.out
@@ -247,11 +247,12 @@ ERROR:  insert or update on table "clstr_tst" violates foreign key constraint "c
 DETAIL:  Key (b)=(1111) is not present in table "clstr_tst_s".
 SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass
 ORDER BY 1;
-    conname     
-----------------
+       conname        
+----------------------
+ clstr_tst_a_not_null
  clstr_tst_con
  clstr_tst_pkey
-(2 rows)
+(3 rows)
 
 SELECT relname, relkind,
     EXISTS(SELECT 1 FROM pg_class WHERE oid = c.reltoastrelid) AS hastoast
diff --git a/src/test/regress/expected/collate.out b/src/test/regress/expected/collate.out
index 246832575c..78675a79ef 100644
--- a/src/test/regress/expected/collate.out
+++ b/src/test/regress/expected/collate.out
@@ -21,6 +21,8 @@ CREATE TABLE collate_test1 (
 --------+---------+-----------+----------+---------
  a      | integer |           |          | 
  b      | text    | C         | not null | 
+Check constraints:
+    "collate_test1_b_not_null" CHECK (b IS NOT NULL)
 
 CREATE TABLE collate_test_fail (
     a int COLLATE "C",
@@ -38,6 +40,8 @@ CREATE TABLE collate_test_like (
 --------+---------+-----------+----------+---------
  a      | integer |           |          | 
  b      | text    | C         | not null | 
+Check constraints:
+    "collate_test1_b_not_null" CHECK (b IS NOT NULL)
 
 CREATE TABLE collate_test2 (
     a int,
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index e6f6602d95..8602e8c248 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -754,6 +754,105 @@ ALTER TABLE deferred_excl ADD EXCLUDE (f1 WITH =);
 ERROR:  could not create exclusion constraint "deferred_excl_f1_excl"
 DETAIL:  Key (f1)=(3) conflicts with key (f1)=(3).
 DROP TABLE deferred_excl;
+-- verify CHECK constraints created for NOT NULL clauses
+CREATE TABLE notnull_tbl1 (a INTEGER NOT NULL);
+\d notnull_tbl1
+            Table "public.notnull_tbl1"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           | not null | 
+Check constraints:
+    "notnull_tbl1_a_not_null" CHECK (a IS NOT NULL)
+
+-- DROP NOT NULL gets rid of both the attnotnull flag and the constraint itself
+ALTER TABLE notnull_tbl1 ALTER a DROP NOT NULL;
+\d notnull_tbl1
+            Table "public.notnull_tbl1"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           |          | 
+
+-- SET NOT NULL puts both back
+ALTER TABLE notnull_tbl1 ALTER a SET NOT NULL;
+\d notnull_tbl1
+            Table "public.notnull_tbl1"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           | not null | 
+Check constraints:
+    "notnull_tbl1_a_not_null" CHECK (a IS NOT NULL)
+
+-- The simple syntax must not create redundant constraint
+ALTER TABLE notnull_tbl1 ALTER a SET NOT NULL;
+\d notnull_tbl1
+            Table "public.notnull_tbl1"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           | not null | 
+Check constraints:
+    "notnull_tbl1_a_not_null" CHECK (a IS NOT NULL)
+
+-- but this should create a second one
+ALTER TABLE notnull_tbl1 ADD check (a IS NOT NULL);
+\d notnull_tbl1
+            Table "public.notnull_tbl1"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           | not null | 
+Check constraints:
+    "notnull_tbl1_a_not_null" CHECK (a IS NOT NULL)
+    "notnull_tbl1_a_not_null1" CHECK (a IS NOT NULL)
+
+-- Dropping the first one keeps attnotnull intact
+ALTER TABLE notnull_tbl1 DROP CONSTRAINT notnull_tbl1_a_not_null;
+\d notnull_tbl1
+            Table "public.notnull_tbl1"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           | not null | 
+Check constraints:
+    "notnull_tbl1_a_not_null1" CHECK (a IS NOT NULL)
+
+-- but removing the second constraint resets the flag
+ALTER TABLE notnull_tbl1 DROP CONSTRAINT notnull_tbl1_a_not_null1;
+\d notnull_tbl1
+            Table "public.notnull_tbl1"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           |          | 
+
+DROP TABLE notnull_tbl1;
+CREATE TABLE notnull_tbl2 (a INTEGER PRIMARY KEY);
+ALTER TABLE notnull_tbl2 ALTER a DROP NOT NULL;
+ERROR:  column "a" is in a primary key
+CREATE TABLE notnull_tbl3 (a INTEGER NOT NULL, CHECK (a IS NOT NULL));
+ALTER TABLE notnull_tbl3 ALTER A DROP NOT NULL;
+ERROR:  cannot DROP NOT NULL when multiple possible constraints exist
+HINT:  Consider specifying which constraint to drop with ALTER TABLE .. DROP CONSTRAINT.
+ALTER TABLE notnull_tbl3 ADD b int, ADD CONSTRAINT pk PRIMARY KEY (a, b);
+\d notnull_tbl3
+            Table "public.notnull_tbl3"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           | not null | 
+ b      | integer |           | not null | 
+Indexes:
+    "pk" PRIMARY KEY, btree (a, b)
+Check constraints:
+    "notnull_tbl3_a_check" CHECK (a IS NOT NULL)
+    "notnull_tbl3_a_not_null" CHECK (a IS NOT NULL)
+
+ALTER TABLE notnull_tbl3 DROP CONSTRAINT pk;
+\d notnull_tbl3
+            Table "public.notnull_tbl3"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           | not null | 
+ b      | integer |           |          | 
+Check constraints:
+    "notnull_tbl3_a_check" CHECK (a IS NOT NULL)
+    "notnull_tbl3_a_not_null" CHECK (a IS NOT NULL)
+
 -- Comments
 -- Setup a low-level role to enforce non-superuser checks.
 CREATE ROLE regress_constraint_comments;
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 4407a017a9..80401b0f14 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -766,22 +766,26 @@ CREATE TABLE part_b PARTITION OF parted (
 ) FOR VALUES IN ('b');
 NOTICE:  merging constraint "check_a" with inherited definition
 -- conislocal should be false for any merged constraints, true otherwise
-SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY conislocal, coninhcount;
- conislocal | coninhcount 
-------------+-------------
- f          |           1
- t          |           0
-(2 rows)
+SELECT conname, conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY coninhcount DESC, conname;
+      conname      | conislocal | coninhcount 
+-------------------+------------+-------------
+ check_a           | f          |           1
+ parted_b_not_null | f          |           1
+ check_b           | t          |           0
+ part_b_b_not_null | t          |           0
+(4 rows)
 
 -- Once check_b is added to the parent, it should be made non-local for part_b
 ALTER TABLE parted ADD CONSTRAINT check_b CHECK (b >= 0);
 NOTICE:  merging constraint "check_b" with inherited definition
-SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass;
+SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY coninhcount DESC, conname;
  conislocal | coninhcount 
 ------------+-------------
  f          |           1
  f          |           1
-(2 rows)
+ f          |           1
+ t          |           0
+(4 rows)
 
 -- Neither check_a nor check_b are droppable from part_b
 ALTER TABLE part_b DROP CONSTRAINT check_a;
@@ -792,10 +796,12 @@ ERROR:  cannot drop inherited constraint "check_b" of relation "part_b"
 -- traditional inheritance where they will be left behind, because they would
 -- be local constraints.
 ALTER TABLE parted DROP CONSTRAINT check_a, DROP CONSTRAINT check_b;
-SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass;
- conislocal | coninhcount 
-------------+-------------
-(0 rows)
+SELECT conname, conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY coninhcount;
+      conname      | conislocal | coninhcount 
+-------------------+------------+-------------
+ part_b_b_not_null | t          |           0
+ parted_b_not_null | f          |           1
+(2 rows)
 
 -- specify PARTITION BY for a partition
 CREATE TABLE fail_part_col_not_found PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE (c);
@@ -819,6 +825,9 @@ DETAIL:  Failing row contains (1, null).
  a      | integer |           | not null | 1
  b      | integer |           | not null | 1
 Partition of: parted_notnull_inh_test FOR VALUES IN (1)
+Check constraints:
+    "parted_notnull_inh_test1_a_not_null" CHECK (a IS NOT NULL)
+    "parted_notnull_inh_test_b_not_null" CHECK (b IS NOT NULL)
 
 drop table parted_notnull_inh_test;
 -- check that collations are assigned in partition bound expressions
@@ -859,6 +868,9 @@ drop table test_part_coll_posix;
  b      | integer |           | not null | 1       | plain    |              | 
 Partition of: parted FOR VALUES IN ('b')
 Partition constraint: ((a IS NOT NULL) AND (a = 'b'::text))
+Check constraints:
+    "part_b_b_not_null" CHECK (b IS NOT NULL)
+    "parted_b_not_null" CHECK (b IS NOT NULL)
 
 -- Both partition bound and partition key in describe output
 \d+ part_c
@@ -870,6 +882,9 @@ Partition constraint: ((a IS NOT NULL) AND (a = 'b'::text))
 Partition of: parted FOR VALUES IN ('c')
 Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text))
 Partition key: RANGE (b)
+Check constraints:
+    "part_c_b_not_null" CHECK (b IS NOT NULL)
+    "parted_b_not_null" CHECK (b IS NOT NULL)
 Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10)
 
 -- a level-2 partition's constraint will include the parent's expressions
@@ -881,6 +896,9 @@ Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10)
  b      | integer |           | not null | 0       | plain    |              | 
 Partition of: part_c FOR VALUES FROM (1) TO (10)
 Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text) AND (b IS NOT NULL) AND (b >= 1) AND (b < 10))
+Check constraints:
+    "part_c_b_not_null" CHECK (b IS NOT NULL)
+    "parted_b_not_null" CHECK (b IS NOT NULL)
 
 -- Show partition count in the parent's describe output
 -- Tempted to include \d+ output listing partitions with bound info but
@@ -893,6 +911,8 @@ Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text) AND (b IS NOT NULL) A
  a      | text    |           |          | 
  b      | integer |           | not null | 0
 Partition key: LIST (a)
+Check constraints:
+    "parted_b_not_null" CHECK (b IS NOT NULL)
 Number of partitions: 3 (Use \d+ to list them.)
 
 \d hash_parted
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index 0ed94f1d2f..8a5692a8fa 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -73,6 +73,8 @@ CREATE TABLE test_like_id_1 (a bigint GENERATED ALWAYS AS IDENTITY, b text);
 --------+--------+-----------+----------+------------------------------
  a      | bigint |           | not null | generated always as identity
  b      | text   |           |          | 
+Check constraints:
+    "test_like_id_1_a_not_null" CHECK (a IS NOT NULL)
 
 INSERT INTO test_like_id_1 (b) VALUES ('b1');
 SELECT * FROM test_like_id_1;
@@ -88,6 +90,8 @@ CREATE TABLE test_like_id_2 (LIKE test_like_id_1);
 --------+--------+-----------+----------+---------
  a      | bigint |           | not null | 
  b      | text   |           |          | 
+Check constraints:
+    "test_like_id_1_a_not_null" CHECK (a IS NOT NULL)
 
 INSERT INTO test_like_id_2 (b) VALUES ('b2');
 ERROR:  null value in column "a" of relation "test_like_id_2" violates not-null constraint
@@ -104,6 +108,8 @@ CREATE TABLE test_like_id_3 (LIKE test_like_id_1 INCLUDING IDENTITY);
 --------+--------+-----------+----------+------------------------------
  a      | bigint |           | not null | generated always as identity
  b      | text   |           |          | 
+Check constraints:
+    "test_like_id_1_a_not_null" CHECK (a IS NOT NULL)
 
 INSERT INTO test_like_id_3 (b) VALUES ('b3');
 SELECT * FROM test_like_id_3;  -- identity was copied and applied
@@ -355,6 +361,7 @@ NOTICE:  merging constraint "ctlt1_a_check" with inherited definition
  b      | text |           |          |         | extended |              | B
 Check constraints:
     "ctlt1_a_check" CHECK (length(a) > 2)
+    "ctlt1_inh_a_not_null" CHECK (a IS NOT NULL)
 Inherits: ctlt1
 
 SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid AND c.conrelid = 'ctlt1_inh'::regclass;
@@ -373,6 +380,7 @@ NOTICE:  merging multiple inherited definitions of column "a"
  b      | text |           |          |         | extended |              | 
  c      | text |           |          |         | external |              | 
 Check constraints:
+    "ctlt13_inh_a_not_null" CHECK (a IS NOT NULL)
     "ctlt1_a_check" CHECK (length(a) > 2)
     "ctlt3_a_check" CHECK (length(a) < 5)
     "ctlt3_c_check" CHECK (length(c) < 7)
@@ -391,6 +399,7 @@ NOTICE:  merging column "a" with inherited definition
 Indexes:
     "ctlt13_like_expr_idx" btree ((a || c))
 Check constraints:
+    "ctlt13_like_a_not_null" CHECK (a IS NOT NULL)
     "ctlt1_a_check" CHECK (length(a) > 2)
     "ctlt3_a_check" CHECK (length(a) < 5)
     "ctlt3_c_check" CHECK (length(c) < 7)
diff --git a/src/test/regress/expected/domain.out b/src/test/regress/expected/domain.out
index 73b010f6ed..d8260a069c 100644
--- a/src/test/regress/expected/domain.out
+++ b/src/test/regress/expected/domain.out
@@ -688,6 +688,15 @@ drop domain dnotnulltest cascade;
 NOTICE:  drop cascades to 2 other objects
 DETAIL:  drop cascades to column col2 of table domnotnull
 drop cascades to column col1 of table domnotnull
+create domain dnotnulltest integer constraint dnn not null;
+select conname, contype, contypid::regtype from pg_constraint c
+	where contypid = 'dnotnulltest'::regtype;
+ conname | contype |   contypid   
+---------+---------+--------------
+ dnn     | c       | dnotnulltest
+(1 row)
+
+drop domain dnotnulltest;
 -- Test ALTER DOMAIN .. DEFAULT ..
 create table domdeftest (col1 ddef1);
 insert into domdeftest default values;
diff --git a/src/test/regress/expected/event_trigger.out b/src/test/regress/expected/event_trigger.out
index 5a10958df5..a63d9fd1b8 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -448,6 +448,7 @@ NOTICE:  END: command_tag=ALTER TABLE type=table identity=evttrig.one
 ALTER TABLE evttrig.one DROP COLUMN col_c;
 NOTICE:  NORMAL: orig=t normal=f istemp=f type=table column identity=evttrig.one.col_c name={evttrig,one,col_c} args={}
 NOTICE:  NORMAL: orig=f normal=t istemp=f type=default value identity=for evttrig.one.col_c name={evttrig,one,col_c} args={}
+NOTICE:  NORMAL: orig=f normal=t istemp=f type=table constraint identity=one_col_c_not_null on evttrig.one name={evttrig,one,one_col_c_not_null} args={}
 NOTICE:  END: command_tag=ALTER TABLE type=table identity=evttrig.one
 ALTER TABLE evttrig.id ALTER COLUMN col_d SET DATA TYPE bigint;
 NOTICE:  END: command_tag=ALTER SEQUENCE type=sequence identity=evttrig.id_col_d_seq
@@ -467,8 +468,10 @@ NOTICE:  NORMAL: orig=t normal=f istemp=f type=schema identity=evttrig name={evt
 NOTICE:  NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.one name={evttrig,one} args={}
 NOTICE:  NORMAL: orig=f normal=t istemp=f type=sequence identity=evttrig.one_col_a_seq name={evttrig,one_col_a_seq} args={}
 NOTICE:  NORMAL: orig=f normal=t istemp=f type=default value identity=for evttrig.one.col_a name={evttrig,one,col_a} args={}
+NOTICE:  NORMAL: orig=f normal=t istemp=f type=table constraint identity=one_col_a_not_null on evttrig.one name={evttrig,one,one_col_a_not_null} args={}
 NOTICE:  NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.two name={evttrig,two} args={}
 NOTICE:  NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.id name={evttrig,id} args={}
+NOTICE:  NORMAL: orig=f normal=t istemp=f type=table constraint identity=id_col_d_not_null on evttrig.id name={evttrig,id,id_col_d_not_null} args={}
 NOTICE:  NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.parted name={evttrig,parted} args={}
 NOTICE:  NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.part_1_10 name={evttrig,part_1_10} args={}
 NOTICE:  NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.part_10_20 name={evttrig,part_10_20} args={}
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index 33505352cc..e8fc8af18f 100644
--- a/src/test/regress/expected/foreign_data.out
+++ b/src/test/regress/expected/foreign_data.out
@@ -742,6 +742,7 @@ COMMENT ON COLUMN ft1.c1 IS 'ft1.c1';
  c2     | text    |           |          |         | (param2 'val2', param3 'val3') | extended |              | 
  c3     | date    |           |          |         |                                | plain    |              | 
 Check constraints:
+    "ft1_c1_not_null" CHECK (c1 IS NOT NULL)
     "ft1_c2_check" CHECK (c2 <> ''::text)
     "ft1_c3_check" CHECK (c3 >= '01-01-1994'::date AND c3 <= '01-31-1994'::date)
 Server: s0
@@ -864,8 +865,10 @@ ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STORAGE PLAIN;
  c9     | integer |           |          |         |                                | plain    |              | 
  c10    | integer |           |          |         | (p1 'v1')                      | plain    |              | 
 Check constraints:
+    "ft1_c1_not_null" CHECK (c1 IS NOT NULL)
     "ft1_c2_check" CHECK (c2 <> ''::text)
     "ft1_c3_check" CHECK (c3 >= '01-01-1994'::date AND c3 <= '01-31-1994'::date)
+    "ft1_c6_not_null" CHECK (c6 IS NOT NULL)
 Server: s0
 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
 
@@ -913,8 +916,10 @@ ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1;
  c8               | text    |           |          |         | (p2 'V2')
  c10              | integer |           |          |         | (p1 'v1')
 Check constraints:
+    "ft1_c1_not_null" CHECK (foreign_column_1 IS NOT NULL)
     "ft1_c2_check" CHECK (c2 <> ''::text)
     "ft1_c3_check" CHECK (c3 >= '01-01-1994'::date AND c3 <= '01-31-1994'::date)
+    "ft1_c6_not_null" CHECK (c6 IS NOT NULL)
 Server: s0
 FDW options: (quote '~', "be quoted" 'value', escape '@')
 
@@ -1406,6 +1411,8 @@ CREATE FOREIGN TABLE ft2 () INHERITS (fd_pt1)
  c1     | integer |           | not null |         | plain    |              | 
  c2     | text    |           |          |         | extended |              | 
  c3     | date    |           |          |         | plain    |              | 
+Check constraints:
+    "fd_pt1_c1_not_null" CHECK (c1 IS NOT NULL)
 Child tables: ft2
 
 \d+ ft2
@@ -1415,6 +1422,8 @@ Child tables: ft2
  c1     | integer |           | not null |         |             | plain    |              | 
  c2     | text    |           |          |         |             | extended |              | 
  c3     | date    |           |          |         |             | plain    |              | 
+Check constraints:
+    "fd_pt1_c1_not_null" CHECK (c1 IS NOT NULL)
 Server: s0
 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
 Inherits: fd_pt1
@@ -1427,6 +1436,8 @@ DROP FOREIGN TABLE ft2;
  c1     | integer |           | not null |         | plain    |              | 
  c2     | text    |           |          |         | extended |              | 
  c3     | date    |           |          |         | plain    |              | 
+Check constraints:
+    "fd_pt1_c1_not_null" CHECK (c1 IS NOT NULL)
 
 CREATE FOREIGN TABLE ft2 (
 	c1 integer NOT NULL,
@@ -1440,6 +1451,8 @@ CREATE FOREIGN TABLE ft2 (
  c1     | integer |           | not null |         |             | plain    |              | 
  c2     | text    |           |          |         |             | extended |              | 
  c3     | date    |           |          |         |             | plain    |              | 
+Check constraints:
+    "ft2_c1_not_null" CHECK (c1 IS NOT NULL)
 Server: s0
 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
 
@@ -1451,6 +1464,8 @@ ALTER FOREIGN TABLE ft2 INHERIT fd_pt1;
  c1     | integer |           | not null |         | plain    |              | 
  c2     | text    |           |          |         | extended |              | 
  c3     | date    |           |          |         | plain    |              | 
+Check constraints:
+    "fd_pt1_c1_not_null" CHECK (c1 IS NOT NULL)
 Child tables: ft2
 
 \d+ ft2
@@ -1460,6 +1475,8 @@ Child tables: ft2
  c1     | integer |           | not null |         |             | plain    |              | 
  c2     | text    |           |          |         |             | extended |              | 
  c3     | date    |           |          |         |             | plain    |              | 
+Check constraints:
+    "ft2_c1_not_null" CHECK (c1 IS NOT NULL)
 Server: s0
 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
 Inherits: fd_pt1
@@ -1481,6 +1498,8 @@ NOTICE:  merging column "c3" with inherited definition
  c1     | integer |           | not null |         |             | plain    |              | 
  c2     | text    |           |          |         |             | extended |              | 
  c3     | date    |           |          |         |             | plain    |              | 
+Check constraints:
+    "ft2_c1_not_null" CHECK (c1 IS NOT NULL)
 Server: s0
 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
 Inherits: fd_pt1
@@ -1494,6 +1513,8 @@ Child tables: ct3,
  c1     | integer |           | not null |         | plain    |              | 
  c2     | text    |           |          |         | extended |              | 
  c3     | date    |           |          |         | plain    |              | 
+Check constraints:
+    "ft2_c1_not_null" CHECK (c1 IS NOT NULL)
 Inherits: ft2
 
 \d+ ft3
@@ -1503,6 +1524,9 @@ Inherits: ft2
  c1     | integer |           | not null |         |             | plain    |              | 
  c2     | text    |           |          |         |             | extended |              | 
  c3     | date    |           |          |         |             | plain    |              | 
+Check constraints:
+    "ft2_c1_not_null" CHECK (c1 IS NOT NULL)
+    "ft3_c1_not_null" CHECK (c1 IS NOT NULL)
 Server: s0
 Inherits: ft2
 
@@ -1524,6 +1548,9 @@ ALTER TABLE fd_pt1 ADD COLUMN c8 integer;
  c6     | integer |           |          |         | plain    |              | 
  c7     | integer |           | not null |         | plain    |              | 
  c8     | integer |           |          |         | plain    |              | 
+Check constraints:
+    "fd_pt1_c1_not_null" CHECK (c1 IS NOT NULL)
+    "fd_pt1_c7_not_null" CHECK (c7 IS NOT NULL)
 Child tables: ft2
 
 \d+ ft2
@@ -1538,6 +1565,9 @@ Child tables: ft2
  c6     | integer |           |          |         |             | plain    |              | 
  c7     | integer |           | not null |         |             | plain    |              | 
  c8     | integer |           |          |         |             | plain    |              | 
+Check constraints:
+    "fd_pt1_c7_not_null" CHECK (c7 IS NOT NULL)
+    "ft2_c1_not_null" CHECK (c1 IS NOT NULL)
 Server: s0
 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
 Inherits: fd_pt1
@@ -1556,6 +1586,9 @@ Child tables: ct3,
  c6     | integer |           |          |         | plain    |              | 
  c7     | integer |           | not null |         | plain    |              | 
  c8     | integer |           |          |         | plain    |              | 
+Check constraints:
+    "fd_pt1_c7_not_null" CHECK (c7 IS NOT NULL)
+    "ft2_c1_not_null" CHECK (c1 IS NOT NULL)
 Inherits: ft2
 
 \d+ ft3
@@ -1570,6 +1603,10 @@ Inherits: ft2
  c6     | integer |           |          |         |             | plain    |              | 
  c7     | integer |           | not null |         |             | plain    |              | 
  c8     | integer |           |          |         |             | plain    |              | 
+Check constraints:
+    "fd_pt1_c7_not_null" CHECK (c7 IS NOT NULL)
+    "ft2_c1_not_null" CHECK (c1 IS NOT NULL)
+    "ft3_c1_not_null" CHECK (c1 IS NOT NULL)
 Server: s0
 Inherits: ft2
 
@@ -1598,6 +1635,9 @@ ALTER TABLE fd_pt1 ALTER COLUMN c8 SET STORAGE EXTERNAL;
  c6     | integer |           | not null |         | plain    |              | 
  c7     | integer |           |          |         | plain    |              | 
  c8     | text    |           |          |         | external |              | 
+Check constraints:
+    "fd_pt1_c1_not_null" CHECK (c1 IS NOT NULL)
+    "fd_pt1_c6_not_null" CHECK (c6 IS NOT NULL)
 Child tables: ft2
 
 \d+ ft2
@@ -1612,6 +1652,9 @@ Child tables: ft2
  c6     | integer |           | not null |         |             | plain    |              | 
  c7     | integer |           |          |         |             | plain    |              | 
  c8     | text    |           |          |         |             | external |              | 
+Check constraints:
+    "fd_pt1_c6_not_null" CHECK (c6 IS NOT NULL)
+    "ft2_c1_not_null" CHECK (c1 IS NOT NULL)
 Server: s0
 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
 Inherits: fd_pt1
@@ -1631,6 +1674,8 @@ ALTER TABLE fd_pt1 DROP COLUMN c8;
  c1     | integer |           | not null |         | plain    | 10000        | 
  c2     | text    |           |          |         | extended |              | 
  c3     | date    |           |          |         | plain    |              | 
+Check constraints:
+    "fd_pt1_c1_not_null" CHECK (c1 IS NOT NULL)
 Child tables: ft2
 
 \d+ ft2
@@ -1640,6 +1685,8 @@ Child tables: ft2
  c1     | integer |           | not null |         |             | plain    | 10000        | 
  c2     | text    |           |          |         |             | extended |              | 
  c3     | date    |           |          |         |             | plain    |              | 
+Check constraints:
+    "ft2_c1_not_null" CHECK (c1 IS NOT NULL)
 Server: s0
 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
 Inherits: fd_pt1
@@ -1654,11 +1701,12 @@ SELECT relname, conname, contype, conislocal, coninhcount, connoinherit
   FROM pg_class AS pc JOIN pg_constraint AS pgc ON (conrelid = pc.oid)
   WHERE pc.relname = 'fd_pt1'
   ORDER BY 1,2;
- relname |  conname   | contype | conislocal | coninhcount | connoinherit 
----------+------------+---------+------------+-------------+--------------
- fd_pt1  | fd_pt1chk1 | c       | t          |           0 | t
- fd_pt1  | fd_pt1chk2 | c       | t          |           0 | f
-(2 rows)
+ relname |      conname       | contype | conislocal | coninhcount | connoinherit 
+---------+--------------------+---------+------------+-------------+--------------
+ fd_pt1  | fd_pt1_c1_not_null | c       | t          |           0 | f
+ fd_pt1  | fd_pt1chk1         | c       | t          |           0 | t
+ fd_pt1  | fd_pt1chk2         | c       | t          |           0 | f
+(3 rows)
 
 -- child does not inherit NO INHERIT constraints
 \d+ fd_pt1
@@ -1669,6 +1717,7 @@ SELECT relname, conname, contype, conislocal, coninhcount, connoinherit
  c2     | text    |           |          |         | extended |              | 
  c3     | date    |           |          |         | plain    |              | 
 Check constraints:
+    "fd_pt1_c1_not_null" CHECK (c1 IS NOT NULL)
     "fd_pt1chk1" CHECK (c1 > 0) NO INHERIT
     "fd_pt1chk2" CHECK (c2 <> ''::text)
 Child tables: ft2
@@ -1682,6 +1731,7 @@ Child tables: ft2
  c3     | date    |           |          |         |             | plain    |              | 
 Check constraints:
     "fd_pt1chk2" CHECK (c2 <> ''::text)
+    "ft2_c1_not_null" CHECK (c1 IS NOT NULL)
 Server: s0
 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
 Inherits: fd_pt1
@@ -1716,6 +1766,7 @@ ALTER FOREIGN TABLE ft2 INHERIT fd_pt1;
  c2     | text    |           |          |         | extended |              | 
  c3     | date    |           |          |         | plain    |              | 
 Check constraints:
+    "fd_pt1_c1_not_null" CHECK (c1 IS NOT NULL)
     "fd_pt1chk1" CHECK (c1 > 0) NO INHERIT
     "fd_pt1chk2" CHECK (c2 <> ''::text)
 Child tables: ft2
@@ -1729,6 +1780,7 @@ Child tables: ft2
  c3     | date    |           |          |         |             | plain    |              | 
 Check constraints:
     "fd_pt1chk2" CHECK (c2 <> ''::text)
+    "ft2_c1_not_null" CHECK (c1 IS NOT NULL)
 Server: s0
 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
 Inherits: fd_pt1
@@ -1747,6 +1799,7 @@ ALTER TABLE fd_pt1 ADD CONSTRAINT fd_pt1chk3 CHECK (c2 <> '') NOT VALID;
  c2     | text    |           |          |         | extended |              | 
  c3     | date    |           |          |         | plain    |              | 
 Check constraints:
+    "fd_pt1_c1_not_null" CHECK (c1 IS NOT NULL)
     "fd_pt1chk3" CHECK (c2 <> ''::text) NOT VALID
 Child tables: ft2
 
@@ -1760,6 +1813,7 @@ Child tables: ft2
 Check constraints:
     "fd_pt1chk2" CHECK (c2 <> ''::text)
     "fd_pt1chk3" CHECK (c2 <> ''::text) NOT VALID
+    "ft2_c1_not_null" CHECK (c1 IS NOT NULL)
 Server: s0
 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
 Inherits: fd_pt1
@@ -1774,6 +1828,7 @@ ALTER TABLE fd_pt1 VALIDATE CONSTRAINT fd_pt1chk3;
  c2     | text    |           |          |         | extended |              | 
  c3     | date    |           |          |         | plain    |              | 
 Check constraints:
+    "fd_pt1_c1_not_null" CHECK (c1 IS NOT NULL)
     "fd_pt1chk3" CHECK (c2 <> ''::text)
 Child tables: ft2
 
@@ -1787,6 +1842,7 @@ Child tables: ft2
 Check constraints:
     "fd_pt1chk2" CHECK (c2 <> ''::text)
     "fd_pt1chk3" CHECK (c2 <> ''::text)
+    "ft2_c1_not_null" CHECK (c1 IS NOT NULL)
 Server: s0
 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
 Inherits: fd_pt1
@@ -1806,6 +1862,7 @@ ALTER TABLE fd_pt1 RENAME CONSTRAINT fd_pt1chk3 TO f2_check;
  f3     | date    |           |          |         | plain    |              | 
 Check constraints:
     "f2_check" CHECK (f2 <> ''::text)
+    "fd_pt1_c1_not_null" CHECK (f1 IS NOT NULL)
 Child tables: ft2
 
 \d+ ft2
@@ -1818,6 +1875,7 @@ Child tables: ft2
 Check constraints:
     "f2_check" CHECK (f2 <> ''::text)
     "fd_pt1chk2" CHECK (f2 <> ''::text)
+    "ft2_c1_not_null" CHECK (f1 IS NOT NULL)
 Server: s0
 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
 Inherits: fd_pt1
@@ -1864,6 +1922,8 @@ CREATE FOREIGN TABLE fd_pt2_1 PARTITION OF fd_pt2 FOR VALUES IN (1)
  c2     | text    |           |          |         | extended |              | 
  c3     | date    |           |          |         | plain    |              | 
 Partition key: LIST (c1)
+Check constraints:
+    "fd_pt2_c1_not_null" CHECK (c1 IS NOT NULL)
 Partitions: fd_pt2_1 FOR VALUES IN (1)
 
 \d+ fd_pt2_1
@@ -1875,6 +1935,8 @@ Partitions: fd_pt2_1 FOR VALUES IN (1)
  c3     | date    |           |          |         |             | plain    |              | 
 Partition of: fd_pt2 FOR VALUES IN (1)
 Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1))
+Check constraints:
+    "fd_pt2_c1_not_null" CHECK (c1 IS NOT NULL)
 Server: s0
 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
 
@@ -1894,6 +1956,8 @@ CREATE FOREIGN TABLE fd_pt2_1 (
  c2     | text         |           |          |         |             | extended |              | 
  c3     | date         |           |          |         |             | plain    |              | 
  c4     | character(1) |           |          |         |             | extended |              | 
+Check constraints:
+    "fd_pt2_1_c1_not_null" CHECK (c1 IS NOT NULL)
 Server: s0
 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
 
@@ -1909,6 +1973,8 @@ DROP FOREIGN TABLE fd_pt2_1;
  c2     | text    |           |          |         | extended |              | 
  c3     | date    |           |          |         | plain    |              | 
 Partition key: LIST (c1)
+Check constraints:
+    "fd_pt2_c1_not_null" CHECK (c1 IS NOT NULL)
 Number of partitions: 0
 
 CREATE FOREIGN TABLE fd_pt2_1 (
@@ -1923,6 +1989,8 @@ CREATE FOREIGN TABLE fd_pt2_1 (
  c1     | integer |           | not null |         |             | plain    |              | 
  c2     | text    |           |          |         |             | extended |              | 
  c3     | date    |           |          |         |             | plain    |              | 
+Check constraints:
+    "fd_pt2_1_c1_not_null" CHECK (c1 IS NOT NULL)
 Server: s0
 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
 
@@ -1936,6 +2004,8 @@ ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1);
  c2     | text    |           |          |         | extended |              | 
  c3     | date    |           |          |         | plain    |              | 
 Partition key: LIST (c1)
+Check constraints:
+    "fd_pt2_c1_not_null" CHECK (c1 IS NOT NULL)
 Partitions: fd_pt2_1 FOR VALUES IN (1)
 
 \d+ fd_pt2_1
@@ -1947,6 +2017,8 @@ Partitions: fd_pt2_1 FOR VALUES IN (1)
  c3     | date    |           |          |         |             | plain    |              | 
 Partition of: fd_pt2 FOR VALUES IN (1)
 Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1))
+Check constraints:
+    "fd_pt2_1_c1_not_null" CHECK (c1 IS NOT NULL)
 Server: s0
 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
 
@@ -1964,6 +2036,8 @@ ALTER TABLE fd_pt2_1 ADD CONSTRAINT p21chk CHECK (c2 <> '');
  c2     | text    |           |          |         | extended |              | 
  c3     | date    |           |          |         | plain    |              | 
 Partition key: LIST (c1)
+Check constraints:
+    "fd_pt2_c1_not_null" CHECK (c1 IS NOT NULL)
 Partitions: fd_pt2_1 FOR VALUES IN (1)
 
 \d+ fd_pt2_1
@@ -1976,6 +2050,8 @@ Partitions: fd_pt2_1 FOR VALUES IN (1)
 Partition of: fd_pt2 FOR VALUES IN (1)
 Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1))
 Check constraints:
+    "fd_pt2_1_c1_not_null" CHECK (c1 IS NOT NULL)
+    "fd_pt2_1_c3_not_null" CHECK (c3 IS NOT NULL)
     "p21chk" CHECK (c2 <> ''::text)
 Server: s0
 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
@@ -1994,6 +2070,9 @@ ALTER TABLE fd_pt2 ALTER c2 SET NOT NULL;
  c2     | text    |           | not null |         | extended |              | 
  c3     | date    |           |          |         | plain    |              | 
 Partition key: LIST (c1)
+Check constraints:
+    "fd_pt2_c1_not_null" CHECK (c1 IS NOT NULL)
+    "fd_pt2_c2_not_null" CHECK (c2 IS NOT NULL)
 Number of partitions: 0
 
 \d+ fd_pt2_1
@@ -2004,6 +2083,8 @@ Number of partitions: 0
  c2     | text    |           |          |         |             | extended |              | 
  c3     | date    |           | not null |         |             | plain    |              | 
 Check constraints:
+    "fd_pt2_1_c1_not_null" CHECK (c1 IS NOT NULL)
+    "fd_pt2_1_c3_not_null" CHECK (c3 IS NOT NULL)
     "p21chk" CHECK (c2 <> ''::text)
 Server: s0
 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
@@ -2023,6 +2104,8 @@ ALTER TABLE fd_pt2 ADD CONSTRAINT fd_pt2chk1 CHECK (c1 > 0);
  c3     | date    |           |          |         | plain    |              | 
 Partition key: LIST (c1)
 Check constraints:
+    "fd_pt2_c1_not_null" CHECK (c1 IS NOT NULL)
+    "fd_pt2_c2_not_null" CHECK (c2 IS NOT NULL)
     "fd_pt2chk1" CHECK (c1 > 0)
 Number of partitions: 0
 
@@ -2034,6 +2117,9 @@ Number of partitions: 0
  c2     | text    |           | not null |         |             | extended |              | 
  c3     | date    |           | not null |         |             | plain    |              | 
 Check constraints:
+    "fd_pt2_1_c1_not_null" CHECK (c1 IS NOT NULL)
+    "fd_pt2_1_c2_not_null" CHECK (c2 IS NOT NULL)
+    "fd_pt2_1_c3_not_null" CHECK (c3 IS NOT NULL)
     "p21chk" CHECK (c2 <> ''::text)
 Server: s0
 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
diff --git a/src/test/regress/expected/generated.out b/src/test/regress/expected/generated.out
index bb4190340e..28f5175fec 100644
--- a/src/test/regress/expected/generated.out
+++ b/src/test/regress/expected/generated.out
@@ -252,6 +252,8 @@ SELECT * FROM gtest1_1;
 --------+---------+-----------+----------+------------------------------------
  a      | integer |           | not null | 
  b      | integer |           |          | generated always as (a * 2) stored
+Check constraints:
+    "gtest1_1_a_not_null" CHECK (a IS NOT NULL)
 Inherits: gtest1
 
 INSERT INTO gtest1_1 VALUES (4);
diff --git a/src/test/regress/expected/identity.out b/src/test/regress/expected/identity.out
index 5f03d8e14f..d194961054 100644
--- a/src/test/regress/expected/identity.out
+++ b/src/test/regress/expected/identity.out
@@ -362,6 +362,8 @@ SELECT seqtypid::regtype FROM pg_sequence WHERE seqrelid = 'itest3_a_seq'::regcl
 --------+---------+-----------+----------+----------------------------------
  a      | integer |           | not null | generated by default as identity
  b      | text    |           |          | 
+Check constraints:
+    "itest3_a_not_null" CHECK (a IS NOT NULL)
 
 ALTER TABLE itest3 ALTER COLUMN a TYPE text;  -- error
 ERROR:  identity column type must be smallint, integer, or bigint
@@ -376,6 +378,9 @@ ALTER TABLE itest3
  a      | integer |           | not null | generated by default as identity
  b      | text    |           |          | 
  c      | integer |           | not null | generated always as identity
+Check constraints:
+    "itest3_a_not_null" CHECK (a IS NOT NULL)
+    "itest3_c_not_null" CHECK (c IS NOT NULL)
 
 -- ALTER COLUMN ... SET
 CREATE TABLE itest6 (a int GENERATED ALWAYS AS IDENTITY, b text);
@@ -506,6 +511,10 @@ TABLE itest8;
  f3     | integer |           | not null | generated by default as identity | plain   |              | 
  f4     | bigint  |           | not null | generated always as identity     | plain   |              | 
  f5     | bigint  |           |          |                                  | plain   |              | 
+Check constraints:
+    "itest8_f2_not_null" CHECK (f2 IS NOT NULL)
+    "itest8_f3_not_null" CHECK (f3 IS NOT NULL)
+    "itest8_f4_not_null" CHECK (f4 IS NOT NULL)
 
 \d itest8_f2_seq
                    Sequence "public.itest8_f2_seq"
diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out
index 1bdd430f06..8a3e9e6400 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -1065,16 +1065,18 @@ create table idxpart3 (b int not null, a int not null);
 alter table idxpart attach partition idxpart3 for values from (20, 20) to (30, 30);
 select conname, contype, conrelid::regclass, conindid::regclass, conkey
   from pg_constraint where conrelid::regclass::text like 'idxpart%'
-  order by conname;
-    conname     | contype | conrelid  |    conindid    | conkey 
-----------------+---------+-----------+----------------+--------
- idxpart1_pkey  | p       | idxpart1  | idxpart1_pkey  | {1,2}
- idxpart21_pkey | p       | idxpart21 | idxpart21_pkey | {1,2}
- idxpart22_pkey | p       | idxpart22 | idxpart22_pkey | {1,2}
- idxpart2_pkey  | p       | idxpart2  | idxpart2_pkey  | {1,2}
- idxpart3_pkey  | p       | idxpart3  | idxpart3_pkey  | {2,1}
- idxpart_pkey   | p       | idxpart   | idxpart_pkey   | {1,2}
-(6 rows)
+  order by conrelid::regclass::text, conname;
+       conname       | contype | conrelid  |    conindid    | conkey 
+---------------------+---------+-----------+----------------+--------
+ idxpart_pkey        | p       | idxpart   | idxpart_pkey   | {1,2}
+ idxpart1_pkey       | p       | idxpart1  | idxpart1_pkey  | {1,2}
+ idxpart2_pkey       | p       | idxpart2  | idxpart2_pkey  | {1,2}
+ idxpart21_pkey      | p       | idxpart21 | idxpart21_pkey | {1,2}
+ idxpart22_pkey      | p       | idxpart22 | idxpart22_pkey | {1,2}
+ idxpart3_a_not_null | c       | idxpart3  | -              | {2}
+ idxpart3_b_not_null | c       | idxpart3  | -              | {1}
+ idxpart3_pkey       | p       | idxpart3  | idxpart3_pkey  | {2,1}
+(8 rows)
 
 drop table idxpart;
 -- Verify that multi-layer partitioning honors the requirement that all
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 2d49e765de..a718969975 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -832,10 +832,10 @@ select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg
 (2 rows)
 
 insert into ac (aa) values (NULL);
-ERROR:  new row for relation "ac" violates check constraint "ac_check"
+ERROR:  null value in column "aa" of relation "ac" violates not-null constraint
 DETAIL:  Failing row contains (null).
 insert into bc (aa) values (NULL);
-ERROR:  new row for relation "bc" violates check constraint "ac_check"
+ERROR:  null value in column "aa" of relation "bc" violates not-null constraint
 DETAIL:  Failing row contains (null, null).
 alter table bc drop constraint ac_check;  -- fail, disallowed
 ERROR:  cannot drop inherited constraint "ac_check" of relation "bc"
@@ -848,21 +848,21 @@ select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg
 -- try the unnamed-constraint case
 alter table ac add check (aa is not null);
 select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
- relname |   conname   | contype | conislocal | coninhcount |      consrc      
----------+-------------+---------+------------+-------------+------------------
- ac      | ac_aa_check | c       | t          |           0 | (aa IS NOT NULL)
- bc      | ac_aa_check | c       | f          |           1 | (aa IS NOT NULL)
+ relname |    conname     | contype | conislocal | coninhcount |      consrc      
+---------+----------------+---------+------------+-------------+------------------
+ ac      | ac_aa_not_null | c       | t          |           0 | (aa IS NOT NULL)
+ bc      | ac_aa_not_null | c       | f          |           1 | (aa IS NOT NULL)
 (2 rows)
 
 insert into ac (aa) values (NULL);
-ERROR:  new row for relation "ac" violates check constraint "ac_aa_check"
+ERROR:  null value in column "aa" of relation "ac" violates not-null constraint
 DETAIL:  Failing row contains (null).
 insert into bc (aa) values (NULL);
-ERROR:  new row for relation "bc" violates check constraint "ac_aa_check"
+ERROR:  null value in column "aa" of relation "bc" violates not-null constraint
 DETAIL:  Failing row contains (null, null).
-alter table bc drop constraint ac_aa_check;  -- fail, disallowed
-ERROR:  cannot drop inherited constraint "ac_aa_check" of relation "bc"
-alter table ac drop constraint ac_aa_check;
+alter table bc drop constraint ac_aa_not_null;  -- fail, disallowed
+ERROR:  cannot drop inherited constraint "ac_aa_not_null" of relation "bc"
+alter table ac drop constraint ac_aa_not_null;
 select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
  relname | conname | contype | conislocal | coninhcount | consrc 
 ---------+---------+---------+------------+-------------+--------
@@ -925,7 +925,7 @@ select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg
 ---------+---------+---------+------------+-------------+----------
  ac      | check_a | c       | t          |           0 | (a <> 0)
  bc      | check_b | c       | t          |           0 | (b <> 0)
- cc      | check_a | c       | f          |           1 | (a <> 0)
+ cc      | check_a | c       | t          |           0 | (a <> 0)
  cc      | check_b | c       | t          |           0 | (b <> 0)
  cc      | check_c | c       | t          |           0 | (c <> 0)
 (5 rows)
@@ -1017,7 +1017,6 @@ Inherits: pp1,
 
 alter table pp1 add column a2 int check (a2 > 0);
 NOTICE:  merging definition of column "a2" for child "cc2"
-NOTICE:  merging constraint "pp1_a2_check" with inherited definition
 \d cc2
                      Table "public.cc2"
  Column |       Type       | Collation | Nullable | Default 
@@ -1737,6 +1736,359 @@ select * from cnullparent where f1 = 2;
 drop table cnullparent cascade;
 NOTICE:  drop cascades to table cnullchild
 --
+-- Test inheritance of NOT NULL constraints
+--
+create table pp1 (f1 int);
+create table cc1 (f2 text, f3 int) inherits (pp1);
+\d cc1
+                Table "public.cc1"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ f1     | integer |           |          | 
+ f2     | text    |           |          | 
+ f3     | integer |           |          | 
+Inherits: pp1
+
+create table cc2(f4 float) inherits(pp1,cc1);
+NOTICE:  merging multiple inherited definitions of column "f1"
+\d cc2
+                     Table "public.cc2"
+ Column |       Type       | Collation | Nullable | Default 
+--------+------------------+-----------+----------+---------
+ f1     | integer          |           |          | 
+ f2     | text             |           |          | 
+ f3     | integer          |           |          | 
+ f4     | double precision |           |          | 
+Inherits: pp1,
+          cc1
+
+-- named NOT NULL constraint
+alter table cc1 add column a2 int constraint nn not null;
+\d cc1
+                Table "public.cc1"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ f1     | integer |           |          | 
+ f2     | text    |           |          | 
+ f3     | integer |           |          | 
+ a2     | integer |           | not null | 
+Check constraints:
+    "nn" CHECK (a2 IS NOT NULL)
+Inherits: pp1
+Number of child tables: 1 (Use \d+ to list them.)
+
+\d cc2
+                     Table "public.cc2"
+ Column |       Type       | Collation | Nullable | Default 
+--------+------------------+-----------+----------+---------
+ f1     | integer          |           |          | 
+ f2     | text             |           |          | 
+ f3     | integer          |           |          | 
+ f4     | double precision |           |          | 
+ a2     | integer          |           | not null | 
+Check constraints:
+    "nn" CHECK (a2 IS NOT NULL)
+Inherits: pp1,
+          cc1
+
+alter table pp1 alter column f1 set not null;
+\d pp1
+                Table "public.pp1"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ f1     | integer |           | not null | 
+Check constraints:
+    "pp1_f1_not_null" CHECK (f1 IS NOT NULL)
+Number of child tables: 2 (Use \d+ to list them.)
+
+\d cc1
+                Table "public.cc1"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ f1     | integer |           | not null | 
+ f2     | text    |           |          | 
+ f3     | integer |           |          | 
+ a2     | integer |           | not null | 
+Check constraints:
+    "nn" CHECK (a2 IS NOT NULL)
+    "pp1_f1_not_null" CHECK (f1 IS NOT NULL)
+Inherits: pp1
+Number of child tables: 1 (Use \d+ to list them.)
+
+\d cc2
+                     Table "public.cc2"
+ Column |       Type       | Collation | Nullable | Default 
+--------+------------------+-----------+----------+---------
+ f1     | integer          |           | not null | 
+ f2     | text             |           |          | 
+ f3     | integer          |           |          | 
+ f4     | double precision |           |          | 
+ a2     | integer          |           | not null | 
+Check constraints:
+    "nn" CHECK (a2 IS NOT NULL)
+    "pp1_f1_not_null" CHECK (f1 IS NOT NULL)
+Inherits: pp1,
+          cc1
+
+-- have a look at pg_constraint
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'c' and
+ conrelid in ('pp1'::regclass, 'cc1'::regclass, 'cc2'::regclass)
+ order by 2, 1;
+ conrelid |     conname     | contype | coninhcount | conislocal 
+----------+-----------------+---------+-------------+------------
+ cc1      | nn              | c       |           0 | t
+ cc2      | nn              | c       |           1 | f
+ pp1      | pp1_f1_not_null | c       |           0 | t
+ cc1      | pp1_f1_not_null | c       |           1 | f
+ cc2      | pp1_f1_not_null | c       |           1 | f
+(5 rows)
+
+-- remove constraint from cc2; one is gone, the other stays
+alter table cc2 alter column a2 drop not null;
+ERROR:  cannot drop inherited constraint "nn" of relation "cc2"
+-- remove constraint cc1, should succeed
+alter table cc1 alter column a2 drop not null;
+-- have a look at pg_constraint
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'c' and
+ conrelid in ('pp1'::regclass, 'cc1'::regclass, 'cc2'::regclass)
+ order by 2, 1;
+ conrelid |     conname     | contype | coninhcount | conislocal 
+----------+-----------------+---------+-------------+------------
+ pp1      | pp1_f1_not_null | c       |           0 | t
+ cc1      | pp1_f1_not_null | c       |           1 | f
+ cc2      | pp1_f1_not_null | c       |           1 | f
+(3 rows)
+
+-- same for cc2
+alter table cc2 alter column f1 drop not null;
+ERROR:  cannot drop inherited constraint "pp1_f1_not_null" of relation "cc2"
+-- remove from cc1, should fail again
+alter table cc1 alter column f1 drop not null;
+ERROR:  cannot drop inherited constraint "pp1_f1_not_null" of relation "cc1"
+-- remove from pp1, should succeed
+alter table pp1 alter column f1 drop not null;
+ERROR:  constraint "pp1_f1_not_null" of relation "cc2" does not exist
+-- have a look at pg_constraint
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'c' and
+ conrelid in ('pp1'::regclass, 'cc1'::regclass, 'cc2'::regclass)
+ order by 2, 1;
+ conrelid |     conname     | contype | coninhcount | conislocal 
+----------+-----------------+---------+-------------+------------
+ pp1      | pp1_f1_not_null | c       |           0 | t
+ cc1      | pp1_f1_not_null | c       |           1 | f
+ cc2      | pp1_f1_not_null | c       |           1 | f
+(3 rows)
+
+drop table pp1 cascade;
+NOTICE:  drop cascades to 2 other objects
+DETAIL:  drop cascades to table cc1
+drop cascades to table cc2
+\d cc1
+\d cc2
+--
+-- test inherit/deinherit
+--
+create table parent(f1 int);
+create table child1(f1 int not null);
+create table child2(f1 int);
+-- child1 should have not null constraint
+alter table child1 inherit parent;
+-- should fail, missing NOT NULL constraint
+alter table child2 inherit child1;
+ERROR:  column "f1" in child table must be marked NOT NULL
+alter table child2 alter column f1 set not null;
+alter table child2 inherit child1;
+-- add NOT NULL constraint recursively
+alter table parent alter column f1 set not null;
+\d parent
+               Table "public.parent"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ f1     | integer |           | not null | 
+Check constraints:
+    "parent_f1_not_null" CHECK (f1 IS NOT NULL)
+Number of child tables: 1 (Use \d+ to list them.)
+
+\d child1
+               Table "public.child1"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ f1     | integer |           | not null | 
+Check constraints:
+    "child1_f1_not_null" CHECK (f1 IS NOT NULL)
+    "parent_f1_not_null" CHECK (f1 IS NOT NULL)
+Inherits: parent
+Number of child tables: 1 (Use \d+ to list them.)
+
+\d child2
+               Table "public.child2"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ f1     | integer |           | not null | 
+Check constraints:
+    "child2_f1_not_null" CHECK (f1 IS NOT NULL)
+    "parent_f1_not_null" CHECK (f1 IS NOT NULL)
+Inherits: child1
+
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'c' and
+ conrelid in ('parent'::regclass, 'child1'::regclass, 'child2'::regclass)
+ order by 2, 1;
+ conrelid |      conname       | contype | coninhcount | conislocal 
+----------+--------------------+---------+-------------+------------
+ child1   | child1_f1_not_null | c       |           0 | t
+ child2   | child2_f1_not_null | c       |           1 | t
+ parent   | parent_f1_not_null | c       |           0 | t
+ child1   | parent_f1_not_null | c       |           1 | f
+ child2   | parent_f1_not_null | c       |           1 | f
+(5 rows)
+
+--
+-- test deinherit procedure
+--
+-- deinherit child1
+alter table child1 no inherit parent;
+\d parent
+               Table "public.parent"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ f1     | integer |           | not null | 
+Check constraints:
+    "parent_f1_not_null" CHECK (f1 IS NOT NULL)
+
+\d child1
+               Table "public.child1"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ f1     | integer |           | not null | 
+Check constraints:
+    "child1_f1_not_null" CHECK (f1 IS NOT NULL)
+    "parent_f1_not_null" CHECK (f1 IS NOT NULL)
+Number of child tables: 1 (Use \d+ to list them.)
+
+\d child2
+               Table "public.child2"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ f1     | integer |           | not null | 
+Check constraints:
+    "child2_f1_not_null" CHECK (f1 IS NOT NULL)
+    "parent_f1_not_null" CHECK (f1 IS NOT NULL)
+Inherits: child1
+
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'c' and
+ conrelid in ('parent'::regclass, 'child1'::regclass, 'child2'::regclass)
+ order by 2, 1;
+ conrelid |      conname       | contype | coninhcount | conislocal 
+----------+--------------------+---------+-------------+------------
+ child1   | child1_f1_not_null | c       |           0 | t
+ child2   | child2_f1_not_null | c       |           1 | t
+ parent   | parent_f1_not_null | c       |           0 | t
+ child1   | parent_f1_not_null | c       |           0 | t
+ child2   | parent_f1_not_null | c       |           1 | f
+(5 rows)
+
+-- test inhcount of child2, should fail
+alter table child2 alter f1 drop not null;
+ERROR:  cannot DROP NOT NULL when multiple possible constraints exist
+HINT:  Consider specifying which constraint to drop with ALTER TABLE .. DROP CONSTRAINT.
+-- should succeed
+drop table parent;
+drop table child1 cascade;
+NOTICE:  drop cascades to table child2
+--
+-- test multi inheritance tree
+--
+create table parent(f1 int not null);
+create table c1() inherits(parent);
+create table c2() inherits(parent);
+create table d1() inherits(c1, c2);
+NOTICE:  merging multiple inherited definitions of column "f1"
+-- show constraint info
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'c' and
+ conrelid in ('parent'::regclass, 'c1'::regclass, 'c2'::regclass, 'd1'::regclass)
+ order by 2, 1;
+ conrelid |      conname       | contype | coninhcount | conislocal 
+----------+--------------------+---------+-------------+------------
+ parent   | parent_f1_not_null | c       |           0 | t
+ c1       | parent_f1_not_null | c       |           1 | f
+ c2       | parent_f1_not_null | c       |           1 | f
+ d1       | parent_f1_not_null | c       |           2 | f
+(4 rows)
+
+drop table parent cascade;
+NOTICE:  drop cascades to 3 other objects
+DETAIL:  drop cascades to table c1
+drop cascades to table c2
+drop cascades to table d1
+-- test child table with inherited columns and
+-- with explicitely specified not null constraints
+create table parent_1(f1 int);
+create table parent_2(f2 text);
+create table child(f1 int not null, f2 text not null) inherits(parent_1, parent_2);
+NOTICE:  merging column "f1" with inherited definition
+NOTICE:  merging column "f2" with inherited definition
+-- show constraint info
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'c' and
+ conrelid in ('parent_1'::regclass, 'parent_2'::regclass, 'child'::regclass)
+ order by 2, 1;
+ conrelid |      conname      | contype | coninhcount | conislocal 
+----------+-------------------+---------+-------------+------------
+ child    | child_f1_not_null | c       |           0 | t
+ child    | child_f2_not_null | c       |           0 | t
+(2 rows)
+
+-- also drops child table
+drop table parent_1 cascade;
+NOTICE:  drop cascades to table child
+drop table parent_2;
+-- test multi layer inheritance tree
+create table p1(f1 int not null);
+create table p2(f1 int not null);
+create table p3(f2 int);
+create table p4(f1 int not null, f3 text not null);
+create table c() inherits(p1, p2, p3, p4);
+NOTICE:  merging multiple inherited definitions of column "f1"
+NOTICE:  merging multiple inherited definitions of column "f1"
+ERROR:  relation "c" already exists
+-- constraint on f1 should have three parents
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'c' and
+ conrelid in ('p1'::regclass, 'p2'::regclass, 'p3'::regclass, 'p4'::regclass, 'c'::regclass)
+ order by 2, 1;
+ conrelid |    conname     | contype | coninhcount | conislocal 
+----------+----------------+---------+-------------+------------
+ p1       | p1_f1_not_null | c       |           0 | t
+ p2       | p2_f1_not_null | c       |           0 | t
+ p4       | p4_f1_not_null | c       |           0 | t
+ p4       | p4_f3_not_null | c       |           0 | t
+(4 rows)
+
+create table d(a int not null, f1 int) inherits(p3, c);
+ERROR:  relation "d" already exists
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'c' and
+ conrelid in ('p1'::regclass, 'p2'::regclass, 'p3'::regclass, 'p4'::regclass, 'c'::regclass, 'd'::regclass)
+ order by 2, 1;
+ conrelid |    conname     | contype | coninhcount | conislocal 
+----------+----------------+---------+-------------+------------
+ p1       | p1_f1_not_null | c       |           0 | t
+ p2       | p2_f1_not_null | c       |           0 | t
+ p4       | p4_f1_not_null | c       |           0 | t
+ p4       | p4_f3_not_null | c       |           0 | t
+(4 rows)
+
+drop table p1 cascade;
+drop table p2;
+drop table p3;
+drop table p4;
+--
 -- Check use of temporary tables with inheritance trees
 --
 create table inh_perm_parent (a1 int);
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index e6e082de2f..cc79dd4fdd 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -155,6 +155,8 @@ SELECT pubname, puballtables FROM pg_publication WHERE pubname = 'testpub_forall
  data   | text    |           |          |                                          | extended |              | 
 Indexes:
     "testpub_tbl2_pkey" PRIMARY KEY, btree (id)
+Check constraints:
+    "testpub_tbl2_id_not_null" CHECK (id IS NOT NULL)
 Publications:
     "testpub_foralltables"
 
@@ -1066,6 +1068,8 @@ Publications:
  data   | text    |           |          |                                          | extended |              | 
 Indexes:
     "testpub_tbl1_pkey" PRIMARY KEY, btree (id)
+Check constraints:
+    "testpub_tbl1_id_not_null" CHECK (id IS NOT NULL)
 Publications:
     "testpib_ins_trunct"
     "testpub_default"
@@ -1092,6 +1096,8 @@ ERROR:  relation "testpub_nopk" is not part of the publication
  data   | text    |           |          |                                          | extended |              | 
 Indexes:
     "testpub_tbl1_pkey" PRIMARY KEY, btree (id)
+Check constraints:
+    "testpub_tbl1_id_not_null" CHECK (id IS NOT NULL)
 Publications:
     "testpib_ins_trunct"
     "testpub_fortbl"
diff --git a/src/test/regress/expected/replica_identity.out b/src/test/regress/expected/replica_identity.out
index e25ec06a84..4c3e284cf3 100644
--- a/src/test/regress/expected/replica_identity.out
+++ b/src/test/regress/expected/replica_identity.out
@@ -92,6 +92,10 @@ Indexes:
     "test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <> '3'::text
     "test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb) DEFERRABLE
     "test_replica_identity_unique_nondefer" UNIQUE CONSTRAINT, btree (keya, keyb)
+Check constraints:
+    "test_replica_identity_id_not_null" CHECK (id IS NOT NULL)
+    "test_replica_identity_keya_not_null" CHECK (keya IS NOT NULL)
+    "test_replica_identity_keyb_not_null" CHECK (keyb IS NOT NULL)
 
 -- succeed, nondeferrable unique constraint over nonnullable cols
 ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_unique_nondefer;
@@ -122,6 +126,10 @@ Indexes:
     "test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <> '3'::text
     "test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb) DEFERRABLE
     "test_replica_identity_unique_nondefer" UNIQUE CONSTRAINT, btree (keya, keyb)
+Check constraints:
+    "test_replica_identity_id_not_null" CHECK (id IS NOT NULL)
+    "test_replica_identity_keya_not_null" CHECK (keya IS NOT NULL)
+    "test_replica_identity_keyb_not_null" CHECK (keyb IS NOT NULL)
 
 SELECT count(*) FROM pg_index WHERE indrelid = 'test_replica_identity'::regclass AND indisreplident;
  count 
@@ -170,6 +178,10 @@ Indexes:
     "test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <> '3'::text
     "test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb) DEFERRABLE
     "test_replica_identity_unique_nondefer" UNIQUE CONSTRAINT, btree (keya, keyb)
+Check constraints:
+    "test_replica_identity_id_not_null" CHECK (id IS NOT NULL)
+    "test_replica_identity_keya_not_null" CHECK (keya IS NOT NULL)
+    "test_replica_identity_keyb_not_null" CHECK (keyb IS NOT NULL)
 Replica Identity: FULL
 
 ALTER TABLE test_replica_identity REPLICA IDENTITY NOTHING;
@@ -192,6 +204,8 @@ ALTER TABLE test_replica_identity2 REPLICA IDENTITY USING INDEX test_replica_ide
  id     | integer |           | not null | 
 Indexes:
     "test_replica_identity2_id_key" UNIQUE CONSTRAINT, btree (id) REPLICA IDENTITY
+Check constraints:
+    "test_replica_identity2_id_not_null" CHECK (id IS NOT NULL)
 
 ALTER TABLE test_replica_identity2 ALTER COLUMN id TYPE bigint;
 \d test_replica_identity2
@@ -201,6 +215,8 @@ ALTER TABLE test_replica_identity2 ALTER COLUMN id TYPE bigint;
  id     | bigint |           | not null | 
 Indexes:
     "test_replica_identity2_id_key" UNIQUE CONSTRAINT, btree (id) REPLICA IDENTITY
+Check constraints:
+    "test_replica_identity2_id_not_null" CHECK (id IS NOT NULL)
 
 -- straight index variant
 CREATE TABLE test_replica_identity3 (id int NOT NULL);
@@ -213,6 +229,8 @@ ALTER TABLE test_replica_identity3 REPLICA IDENTITY USING INDEX test_replica_ide
  id     | integer |           | not null | 
 Indexes:
     "test_replica_identity3_id_key" UNIQUE, btree (id) REPLICA IDENTITY
+Check constraints:
+    "test_replica_identity3_id_not_null" CHECK (id IS NOT NULL)
 
 ALTER TABLE test_replica_identity3 ALTER COLUMN id TYPE bigint;
 \d test_replica_identity3
@@ -222,12 +240,25 @@ ALTER TABLE test_replica_identity3 ALTER COLUMN id TYPE bigint;
  id     | bigint |           | not null | 
 Indexes:
     "test_replica_identity3_id_key" UNIQUE, btree (id) REPLICA IDENTITY
+Check constraints:
+    "test_replica_identity3_id_not_null" CHECK (id IS NOT NULL)
 
 -- ALTER TABLE DROP NOT NULL is not allowed for columns part of an index
 -- used as replica identity.
 ALTER TABLE test_replica_identity3 ALTER COLUMN id DROP NOT NULL;
 ERROR:  column "id" is in index used as replica identity
+-- Dropping the primary key is not allowed if that would leave the replica
+-- identity as nullable
+CREATE TABLE test_replica_identity4 (a int not null, b int, c int,
+	PRIMARY KEY (b, c));
+CREATE UNIQUE INDEX test_replica_identity4_a_b_key ON test_replica_identity4 (a, b);
+ALTER TABLE test_replica_identity4 REPLICA IDENTITY USING INDEX test_replica_identity4_a_b_key;
+ALTER TABLE test_replica_identity4 DROP CONSTRAINT test_replica_identity4_pkey;
+ERROR:  column "b" is in index used as replica identity
+ALTER TABLE test_replica_identity4 ALTER b SET NOT NULL;
+ALTER TABLE test_replica_identity4 DROP CONSTRAINT test_replica_identity4_pkey;
 DROP TABLE test_replica_identity;
 DROP TABLE test_replica_identity2;
 DROP TABLE test_replica_identity3;
+DROP TABLE test_replica_identity4;
 DROP TABLE test_replica_identity_othertable;
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index b5f6eecba1..b295abe8fe 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -123,6 +123,8 @@ CREATE POLICY p1r ON document AS RESTRICTIVE TO regress_rls_dave
  dtitle  | text    |           |          | 
 Indexes:
     "document_pkey" PRIMARY KEY, btree (did)
+Check constraints:
+    "document_dlevel_not_null" CHECK (dlevel IS NOT NULL)
 Foreign-key constraints:
     "document_cid_fkey" FOREIGN KEY (cid) REFERENCES category(cid)
 Policies:
@@ -947,6 +949,8 @@ CREATE POLICY pp1r ON part_document AS RESTRICTIVE TO regress_rls_dave
  dauthor | name    |           |          |         | plain    |              | 
  dtitle  | text    |           |          |         | extended |              | 
 Partition key: RANGE (cid)
+Check constraints:
+    "part_document_dlevel_not_null" CHECK (dlevel IS NOT NULL)
 Policies:
     POLICY "pp1"
       USING ((dlevel <= ( SELECT uaccount.seclv
diff --git a/src/test/regress/expected/typed_table.out b/src/test/regress/expected/typed_table.out
index 2e47ecbcf5..ca0331c0a6 100644
--- a/src/test/regress/expected/typed_table.out
+++ b/src/test/regress/expected/typed_table.out
@@ -129,5 +129,7 @@ CREATE TABLE persons3 OF person_type (
  name   | text    |           | not null | ''::text
 Indexes:
     "persons3_pkey" PRIMARY KEY, btree (id)
+Check constraints:
+    "persons3_name_not_null" CHECK (name IS NOT NULL)
 Typed table of type: person_type
 
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index e7013f5e15..c49fde679a 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -852,7 +852,7 @@ create table atacc1 (test int not null);
 alter table atacc1 add constraint "atacc1_pkey" primary key (test);
 alter table atacc1 alter column test drop not null;
 alter table atacc1 drop constraint "atacc1_pkey";
-alter table atacc1 alter column test drop not null;
+\d atacc1
 insert into atacc1 values (null);
 alter table atacc1 alter test set not null;
 delete from atacc1;
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 5ffcd4ffc7..ae427d25e9 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -556,6 +556,39 @@ ALTER TABLE deferred_excl ADD EXCLUDE (f1 WITH =);
 
 DROP TABLE deferred_excl;
 
+-- verify CHECK constraints created for NOT NULL clauses
+CREATE TABLE notnull_tbl1 (a INTEGER NOT NULL);
+\d notnull_tbl1
+-- DROP NOT NULL gets rid of both the attnotnull flag and the constraint itself
+ALTER TABLE notnull_tbl1 ALTER a DROP NOT NULL;
+\d notnull_tbl1
+-- SET NOT NULL puts both back
+ALTER TABLE notnull_tbl1 ALTER a SET NOT NULL;
+\d notnull_tbl1
+-- The simple syntax must not create redundant constraint
+ALTER TABLE notnull_tbl1 ALTER a SET NOT NULL;
+\d notnull_tbl1
+-- but this should create a second one
+ALTER TABLE notnull_tbl1 ADD check (a IS NOT NULL);
+\d notnull_tbl1
+-- Dropping the first one keeps attnotnull intact
+ALTER TABLE notnull_tbl1 DROP CONSTRAINT notnull_tbl1_a_not_null;
+\d notnull_tbl1
+-- but removing the second constraint resets the flag
+ALTER TABLE notnull_tbl1 DROP CONSTRAINT notnull_tbl1_a_not_null1;
+\d notnull_tbl1
+DROP TABLE notnull_tbl1;
+
+CREATE TABLE notnull_tbl2 (a INTEGER PRIMARY KEY);
+ALTER TABLE notnull_tbl2 ALTER a DROP NOT NULL;
+
+CREATE TABLE notnull_tbl3 (a INTEGER NOT NULL, CHECK (a IS NOT NULL));
+ALTER TABLE notnull_tbl3 ALTER A DROP NOT NULL;
+ALTER TABLE notnull_tbl3 ADD b int, ADD CONSTRAINT pk PRIMARY KEY (a, b);
+\d notnull_tbl3
+ALTER TABLE notnull_tbl3 DROP CONSTRAINT pk;
+\d notnull_tbl3
+
 -- Comments
 -- Setup a low-level role to enforce non-superuser checks.
 CREATE ROLE regress_constraint_comments;
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index 5175f404f7..6418ef8196 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -532,11 +532,11 @@ CREATE TABLE part_b PARTITION OF parted (
 	CONSTRAINT check_b CHECK (b >= 0)
 ) FOR VALUES IN ('b');
 -- conislocal should be false for any merged constraints, true otherwise
-SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY conislocal, coninhcount;
+SELECT conname, conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY coninhcount DESC, conname;
 
 -- Once check_b is added to the parent, it should be made non-local for part_b
 ALTER TABLE parted ADD CONSTRAINT check_b CHECK (b >= 0);
-SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass;
+SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY coninhcount DESC, conname;
 
 -- Neither check_a nor check_b are droppable from part_b
 ALTER TABLE part_b DROP CONSTRAINT check_a;
@@ -546,7 +546,7 @@ ALTER TABLE part_b DROP CONSTRAINT check_b;
 -- traditional inheritance where they will be left behind, because they would
 -- be local constraints.
 ALTER TABLE parted DROP CONSTRAINT check_a, DROP CONSTRAINT check_b;
-SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass;
+SELECT conname, conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY coninhcount;
 
 -- specify PARTITION BY for a partition
 CREATE TABLE fail_part_col_not_found PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE (c);
diff --git a/src/test/regress/sql/domain.sql b/src/test/regress/sql/domain.sql
index f2ca1fb675..3a2a782501 100644
--- a/src/test/regress/sql/domain.sql
+++ b/src/test/regress/sql/domain.sql
@@ -408,6 +408,13 @@ update domnotnull set col1 = null;
 
 drop domain dnotnulltest cascade;
 
+create domain dnotnulltest integer constraint dnn not null;
+
+select conname, contype, contypid::regtype from pg_constraint c
+	where contypid = 'dnotnulltest'::regtype;
+
+drop domain dnotnulltest;
+
 -- Test ALTER DOMAIN .. DEFAULT ..
 create table domdeftest (col1 ddef1);
 
diff --git a/src/test/regress/sql/indexing.sql b/src/test/regress/sql/indexing.sql
index 429120e710..b395bb79cb 100644
--- a/src/test/regress/sql/indexing.sql
+++ b/src/test/regress/sql/indexing.sql
@@ -522,7 +522,7 @@ create table idxpart3 (b int not null, a int not null);
 alter table idxpart attach partition idxpart3 for values from (20, 20) to (30, 30);
 select conname, contype, conrelid::regclass, conindid::regclass, conkey
   from pg_constraint where conrelid::regclass::text like 'idxpart%'
-  order by conname;
+  order by conrelid::regclass::text, conname;
 drop table idxpart;
 
 -- Verify that multi-layer partitioning honors the requirement that all
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 195aedb5ff..e714c6ce7c 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -279,8 +279,8 @@ select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg
 insert into ac (aa) values (NULL);
 insert into bc (aa) values (NULL);
 
-alter table bc drop constraint ac_aa_check;  -- fail, disallowed
-alter table ac drop constraint ac_aa_check;
+alter table bc drop constraint ac_aa_not_null;  -- fail, disallowed
+alter table ac drop constraint ac_aa_not_null;
 select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
 
 alter table ac add constraint ac_check check (aa is not null);
@@ -641,6 +641,169 @@ select * from cnullparent;
 select * from cnullparent where f1 = 2;
 drop table cnullparent cascade;
 
+--
+-- Test inheritance of NOT NULL constraints
+--
+create table pp1 (f1 int);
+create table cc1 (f2 text, f3 int) inherits (pp1);
+\d cc1
+create table cc2(f4 float) inherits(pp1,cc1);
+\d cc2
+
+-- named NOT NULL constraint
+alter table cc1 add column a2 int constraint nn not null;
+\d cc1
+\d cc2
+alter table pp1 alter column f1 set not null;
+\d pp1
+\d cc1
+\d cc2
+
+-- have a look at pg_constraint
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'c' and
+ conrelid in ('pp1'::regclass, 'cc1'::regclass, 'cc2'::regclass)
+ order by 2, 1;
+
+-- remove constraint from cc2; one is gone, the other stays
+alter table cc2 alter column a2 drop not null;
+
+-- remove constraint cc1, should succeed
+alter table cc1 alter column a2 drop not null;
+
+-- have a look at pg_constraint
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'c' and
+ conrelid in ('pp1'::regclass, 'cc1'::regclass, 'cc2'::regclass)
+ order by 2, 1;
+
+-- same for cc2
+alter table cc2 alter column f1 drop not null;
+
+-- remove from cc1, should fail again
+alter table cc1 alter column f1 drop not null;
+
+-- remove from pp1, should succeed
+alter table pp1 alter column f1 drop not null;
+
+-- have a look at pg_constraint
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'c' and
+ conrelid in ('pp1'::regclass, 'cc1'::regclass, 'cc2'::regclass)
+ order by 2, 1;
+
+drop table pp1 cascade;
+\d cc1
+\d cc2
+
+--
+-- test inherit/deinherit
+--
+create table parent(f1 int);
+create table child1(f1 int not null);
+create table child2(f1 int);
+
+-- child1 should have not null constraint
+alter table child1 inherit parent;
+
+-- should fail, missing NOT NULL constraint
+alter table child2 inherit child1;
+
+alter table child2 alter column f1 set not null;
+alter table child2 inherit child1;
+
+-- add NOT NULL constraint recursively
+alter table parent alter column f1 set not null;
+
+\d parent
+\d child1
+\d child2
+
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'c' and
+ conrelid in ('parent'::regclass, 'child1'::regclass, 'child2'::regclass)
+ order by 2, 1;
+
+--
+-- test deinherit procedure
+--
+
+-- deinherit child1
+alter table child1 no inherit parent;
+\d parent
+\d child1
+\d child2
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'c' and
+ conrelid in ('parent'::regclass, 'child1'::regclass, 'child2'::regclass)
+ order by 2, 1;
+
+-- test inhcount of child2, should fail
+alter table child2 alter f1 drop not null;
+
+-- should succeed
+
+drop table parent;
+drop table child1 cascade;
+
+--
+-- test multi inheritance tree
+--
+create table parent(f1 int not null);
+create table c1() inherits(parent);
+create table c2() inherits(parent);
+create table d1() inherits(c1, c2);
+
+-- show constraint info
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'c' and
+ conrelid in ('parent'::regclass, 'c1'::regclass, 'c2'::regclass, 'd1'::regclass)
+ order by 2, 1;
+
+drop table parent cascade;
+
+-- test child table with inherited columns and
+-- with explicitely specified not null constraints
+create table parent_1(f1 int);
+create table parent_2(f2 text);
+create table child(f1 int not null, f2 text not null) inherits(parent_1, parent_2);
+
+-- show constraint info
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'c' and
+ conrelid in ('parent_1'::regclass, 'parent_2'::regclass, 'child'::regclass)
+ order by 2, 1;
+
+-- also drops child table
+drop table parent_1 cascade;
+drop table parent_2;
+
+-- test multi layer inheritance tree
+create table p1(f1 int not null);
+create table p2(f1 int not null);
+create table p3(f2 int);
+create table p4(f1 int not null, f3 text not null);
+
+create table c() inherits(p1, p2, p3, p4);
+
+-- constraint on f1 should have three parents
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'c' and
+ conrelid in ('p1'::regclass, 'p2'::regclass, 'p3'::regclass, 'p4'::regclass, 'c'::regclass)
+ order by 2, 1;
+
+create table d(a int not null, f1 int) inherits(p3, c);
+
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = 'c' and
+ conrelid in ('p1'::regclass, 'p2'::regclass, 'p3'::regclass, 'p4'::regclass, 'c'::regclass, 'd'::regclass)
+ order by 2, 1;
+
+drop table p1 cascade;
+drop table p2;
+drop table p3;
+drop table p4;
+
 --
 -- Check use of temporary tables with inheritance trees
 --
diff --git a/src/test/regress/sql/replica_identity.sql b/src/test/regress/sql/replica_identity.sql
index 33da829713..d0acf99e6c 100644
--- a/src/test/regress/sql/replica_identity.sql
+++ b/src/test/regress/sql/replica_identity.sql
@@ -98,7 +98,18 @@ ALTER TABLE test_replica_identity3 ALTER COLUMN id TYPE bigint;
 -- used as replica identity.
 ALTER TABLE test_replica_identity3 ALTER COLUMN id DROP NOT NULL;
 
+-- Dropping the primary key is not allowed if that would leave the replica
+-- identity as nullable
+CREATE TABLE test_replica_identity4 (a int not null, b int, c int,
+	PRIMARY KEY (b, c));
+CREATE UNIQUE INDEX test_replica_identity4_a_b_key ON test_replica_identity4 (a, b);
+ALTER TABLE test_replica_identity4 REPLICA IDENTITY USING INDEX test_replica_identity4_a_b_key;
+ALTER TABLE test_replica_identity4 DROP CONSTRAINT test_replica_identity4_pkey;
+ALTER TABLE test_replica_identity4 ALTER b SET NOT NULL;
+ALTER TABLE test_replica_identity4 DROP CONSTRAINT test_replica_identity4_pkey;
+
 DROP TABLE test_replica_identity;
 DROP TABLE test_replica_identity2;
 DROP TABLE test_replica_identity3;
+DROP TABLE test_replica_identity4;
 DROP TABLE test_replica_identity_othertable;

view thread (196+ messages)  latest in thread

Message-ID: <20220909175813.vu5kd2m67mrrqkjv@alvherre.pgsql>
Permalink:  ../20220909175813.vu5kd2m67mrrqkjv@alvherre.pgsql/
Also on:    postgresql.org/message-id/20220909175813.vu5kd2m67mrrqkjv@alvherre.pgsql

copy link · copy postgr.es · copy git am

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: alvherre@alvh.no-ip.org, amitlangote09@gmail.com, laurenz.albe@cybertec.at, pgsql-hackers@lists.postgresql.org
  Subject: Re: cataloguing NOT NULL constraints
  In-Reply-To: <20220909175813.vu5kd2m67mrrqkjv@alvherre.pgsql>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox