v6-0001-ALTER-DOMAIN-ADD-NOT-NULL-NOT-VALID.patch

From 28f28d82fd2cfabbd9834648b51d54b88ee9742b Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Mon, 2 Mar 2026 04:15:04 +0800
Subject: [PATCH v6 1/1] ALTER DOMAIN ADD NOT NULL NOT VALID

We already support NOT VALID for not-null table constraints. Now extends the
same capability to domains by allowing domain's NOT NULL constraint to be marked
as NOT VALID. This will affects other related ALTER DOMAIN command:

ALTER DOMAIN ADD NOT NULL: If the domain already possesses a NOT
VALID not-null constraint, this command raise an error, and fail.

ALTER DOMAIN DROP NOT NULL: NOT VALID not-null domain constraint will be dropped.

ALTER DOMAIN SET NOT NULL, similar to command ALTER TABLE SET NOT NULL, this
will validate the existing NOT VALID not-null constraint and updates
pg_constraint.convalidated to true.

However if the domain already has a validated not-null constraint, adding a NOT
VALID not-null constraint has no effect.

\dD Output: The "Nullable" column in \dD will now display "not null not valid"
for NOT VALID not-null doamin constraints.

Author: jian he <jian.universality@gmail.com>
Reviewed-by: Kirill Reshke <reshkekirill@gmail.com>
Reviewed-by: Quan Zongliang <quanzongliang@yeah.net>

discussion: https://postgr.es/m/CACJufxGcABLgmH951SJkkihK+FW8KR3=odBhXEVCF9atQbur2Q@mail.gmail.com
relate discussion: https://postgr.es/m/CACJufxE2oFcLmrqDrqJrH5k03fv+v9=+-PBs-mV5WsJ=31XMyw@mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/5768
---
 doc/src/sgml/catalogs.sgml           |   2 +-
 doc/src/sgml/ref/alter_domain.sgml   |   1 -
 src/backend/catalog/pg_constraint.c  |   7 +-
 src/backend/commands/typecmds.c      | 148 ++++++++++++++++++++++++---
 src/backend/parser/gram.y            |   8 +-
 src/bin/pg_dump/t/002_pg_dump.pl     |  16 +++
 src/bin/psql/describe.c              |   5 +-
 src/test/regress/expected/domain.out |  37 +++++++
 src/test/regress/sql/domain.sql      |  24 +++++
 9 files changed, 219 insertions(+), 29 deletions(-)

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index e7067c84ece..98fb2779f46 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -9632,7 +9632,7 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
        <structfield>typnotnull</structfield> <type>bool</type>
       </para>
       <para>
-       <structfield>typnotnull</structfield> represents a not-null
+       <structfield>typnotnull</structfield> represents a (possibly invalid) not-null
        constraint on a type.  Used for domains only.
       </para></entry>
      </row>
diff --git a/doc/src/sgml/ref/alter_domain.sgml b/doc/src/sgml/ref/alter_domain.sgml
index 74855172222..4807116eb05 100644
--- a/doc/src/sgml/ref/alter_domain.sgml
+++ b/doc/src/sgml/ref/alter_domain.sgml
@@ -92,7 +92,6 @@ ALTER DOMAIN <replaceable class="parameter">name</replaceable>
       valid using <command>ALTER DOMAIN ... VALIDATE CONSTRAINT</command>.
       Newly inserted or updated rows are always checked against all
       constraints, even those marked <literal>NOT VALID</literal>.
-      <literal>NOT VALID</literal> is only accepted for <literal>CHECK</literal> constraints.
      </para>
     </listitem>
    </varlistentry>
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index b12765ae691..09baf48dd57 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -651,7 +651,7 @@ findNotNullConstraint(Oid relid, const char *colname)
 }
 
 /*
- * Find and return the pg_constraint tuple that implements a validated
+ * Find and return the pg_constraint tuple that implements a (possibly not valid)
  * not-null constraint for the given domain.
  */
 HeapTuple
@@ -675,13 +675,8 @@ findDomainNotNullConstraint(Oid typid)
 	{
 		Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(conTup);
 
-		/*
-		 * We're looking for a NOTNULL constraint that's marked validated.
-		 */
 		if (con->contype != CONSTRAINT_NOTNULL)
 			continue;
-		if (!con->convalidated)
-			continue;
 
 		/* Found it */
 		retval = heap_copytuple(conTup);
diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c
index 3dab6bb5a79..2137f68a70f 100644
--- a/src/backend/commands/typecmds.c
+++ b/src/backend/commands/typecmds.c
@@ -129,7 +129,7 @@ static Oid	findRangeSubOpclass(List *opcname, Oid subtype);
 static Oid	findRangeCanonicalFunction(List *procname, Oid typeOid);
 static Oid	findRangeSubtypeDiffFunction(List *procname, Oid subtype);
 static void validateDomainCheckConstraint(Oid domainoid, const char *ccbin, LOCKMODE lockmode);
-static void validateDomainNotNullConstraint(Oid domainoid);
+static void validateDomainNotNullConstraint(Oid domainoid, LOCKMODE lockmode);
 static List *get_rels_with_domain(Oid domainOid, LOCKMODE lockmode);
 static void checkEnumOwner(HeapTuple tup);
 static char *domainAddCheckConstraint(Oid domainOid, Oid domainNamespace,
@@ -2790,12 +2790,73 @@ AlterDomainNotNull(List *names, bool notNull)
 	checkDomainOwner(tup);
 
 	/* Is the domain already set to the desired constraint? */
-	if (typTup->typnotnull == notNull)
+	if (!typTup->typnotnull && !notNull)
 	{
 		table_close(typrel, RowExclusiveLock);
 		return address;
 	}
 
+	if (typTup->typnotnull && notNull)
+	{
+		ScanKeyData key[1];
+		SysScanDesc scan;
+		HeapTuple	conTup;
+
+		Relation	pg_constraint = table_open(ConstraintRelationId,
+											   AccessShareLock);
+
+		ScanKeyInit(&key[0],
+					Anum_pg_constraint_contypid,
+					BTEqualStrategyNumber, F_OIDEQ,
+					ObjectIdGetDatum(domainoid));
+
+		scan = systable_beginscan(pg_constraint, ConstraintTypidIndexId, true,
+								  NULL, 1, key);
+
+		while (HeapTupleIsValid(conTup = systable_getnext(scan)))
+		{
+			Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(conTup);
+
+			if (con->contype != CONSTRAINT_NOTNULL)
+				continue;
+
+			/*
+			 * ALTER DOMAIN SET NOT NULL require validate the existing NOT
+			 * VALID not-null domain constraint, while at it, set
+			 * pg_constraint.convalidated to true.
+			 */
+			if (!con->convalidated)
+			{
+				HeapTuple	copyTuple;
+				Form_pg_constraint copy_con;
+
+				validateDomainNotNullConstraint(domainoid, ShareUpdateExclusiveLock);
+
+				copyTuple = heap_copytuple(conTup);
+				copy_con = (Form_pg_constraint) GETSTRUCT(copyTuple);
+
+				copy_con->convalidated = true;
+				CatalogTupleUpdate(pg_constraint, &copyTuple->t_self, copyTuple);
+
+				InvokeObjectPostAlterHook(ConstraintRelationId, con->oid, 0);
+
+				heap_freetuple(copyTuple);
+			}
+
+			break;
+		}
+		systable_endscan(scan);
+
+		table_close(pg_constraint, AccessShareLock);
+		table_close(typrel, RowExclusiveLock);
+
+		/*
+		 * Now the existing not-null domain constraint is valid, no need to
+		 * add a new one. Exit now.
+		 */
+		return address;
+	}
+
 	if (notNull)
 	{
 		Constraint *constr;
@@ -2809,7 +2870,7 @@ AlterDomainNotNull(List *names, bool notNull)
 								   typTup->typbasetype, typTup->typtypmod,
 								   constr, NameStr(typTup->typname), NULL);
 
-		validateDomainNotNullConstraint(domainoid);
+		validateDomainNotNullConstraint(domainoid, ShareLock);
 	}
 	else
 	{
@@ -3023,10 +3084,51 @@ AlterDomainAddConstraint(List *names, Node *newConstraint,
 	}
 	else if (constr->contype == CONSTR_NOTNULL)
 	{
-		/* Is the domain already set NOT NULL? */
+		/* Is the domain already have a NOT NULL constraint? */
 		if (typTup->typnotnull)
 		{
+			HeapTuple	conTup;
+			ScanKeyData key[1];
+			SysScanDesc scan;
+
+			Relation	pg_constraint = table_open(ConstraintRelationId,
+												   AccessShareLock);
+
+			ScanKeyInit(&key[0],
+						Anum_pg_constraint_contypid,
+						BTEqualStrategyNumber, F_OIDEQ,
+						ObjectIdGetDatum(domainoid));
+
+			scan = systable_beginscan(pg_constraint,
+									  ConstraintTypidIndexId,
+									  true, NULL, 1, key);
+			while (HeapTupleIsValid(conTup = systable_getnext(scan)))
+			{
+				Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(conTup);
+
+				if (con->contype != CONSTRAINT_NOTNULL)
+					continue;
+
+				/*
+				 * Cannot adding a new VALID NOT NULL constraint if the domain
+				 * already has a NOT VALID one. However, adding a NOT VALID
+				 * NOT NULL constraint when a VALID one exists is allowed.
+				 */
+				if (!con->convalidated && constr->initially_valid)
+					ereport(ERROR,
+							errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+							errmsg("incompatible NOT VALID constraint \"%s\" on domain \"%s\"",
+								   NameStr(con->conname),
+								   NameStr(typTup->typname)),
+							errhint("You might need to validate it using %s.",
+									"ALTER DOMAIN ... VALIDATE CONSTRAINT"));
+				break;
+			}
+			systable_endscan(scan);
+
+			table_close(pg_constraint, AccessShareLock);
 			table_close(typrel, RowExclusiveLock);
+
 			return address;
 		}
 		domainAddNotNullConstraint(domainoid, typTup->typnamespace,
@@ -3034,7 +3136,7 @@ AlterDomainAddConstraint(List *names, Node *newConstraint,
 								   constr, NameStr(typTup->typname), constrAddr);
 
 		if (!constr->skip_validation)
-			validateDomainNotNullConstraint(domainoid);
+			validateDomainNotNullConstraint(domainoid, ShareLock);
 
 		typTup->typnotnull = true;
 		CatalogTupleUpdate(typrel, &tup->t_self, tup);
@@ -3117,23 +3219,28 @@ AlterDomainValidateConstraint(List *names, const char *constrName)
 						constrName, TypeNameToString(typename))));
 
 	con = (Form_pg_constraint) GETSTRUCT(tuple);
-	if (con->contype != CONSTRAINT_CHECK)
+	if (con->contype != CONSTRAINT_CHECK && con->contype != CONSTRAINT_NOTNULL)
 		ereport(ERROR,
 				(errcode(ERRCODE_WRONG_OBJECT_TYPE),
-				 errmsg("constraint \"%s\" of domain \"%s\" is not a check constraint",
+				 errmsg("constraint \"%s\" of domain \"%s\" is not a check or not-null constraint",
 						constrName, TypeNameToString(typename))));
 
 	if (!con->convalidated)
 	{
-		val = SysCacheGetAttrNotNull(CONSTROID, tuple, Anum_pg_constraint_conbin);
-		conbin = TextDatumGetCString(val);
+		if (con->contype == CONSTRAINT_CHECK)
+		{
+			val = SysCacheGetAttrNotNull(CONSTROID, tuple, Anum_pg_constraint_conbin);
+			conbin = TextDatumGetCString(val);
 
-		/*
-		 * Locking related relations with ShareUpdateExclusiveLock is ok
-		 * because not-yet-valid constraints are still enforced against
-		 * concurrent inserts or updates.
-		 */
-		validateDomainCheckConstraint(domainoid, conbin, ShareUpdateExclusiveLock);
+			/*
+			 * Locking related relations with ShareUpdateExclusiveLock is ok
+			 * because not-yet-valid constraints are still enforced against
+			 * concurrent inserts or updates.
+			 */
+			validateDomainCheckConstraint(domainoid, conbin, ShareUpdateExclusiveLock);
+		}
+		else
+			validateDomainNotNullConstraint(domainoid, ShareUpdateExclusiveLock);
 
 		/*
 		 * Now update the catalog, while we have the door open.
@@ -3162,9 +3269,16 @@ AlterDomainValidateConstraint(List *names, const char *constrName)
 
 /*
  * Verify that all columns currently using the domain are not null.
+ *
+ * This support validating the existing not-null constraint and adding a new
+ * not-null constraint on a domain.
+ *
+ * The lockmode is used for relations using the domain.  It should be ShareLock
+ * when adding a new not-null to domain.  It can be ShareUpdateExclusiveLock
+ * when validating the existing not-null constraint.
  */
 static void
-validateDomainNotNullConstraint(Oid domainoid)
+validateDomainNotNullConstraint(Oid domainoid, LOCKMODE lockmode)
 {
 	List	   *rels;
 	ListCell   *rt;
@@ -3172,7 +3286,7 @@ validateDomainNotNullConstraint(Oid domainoid)
 	/* Fetch relation list with attributes based on this domain */
 	/* ShareLock is sufficient to prevent concurrent data changes */
 
-	rels = get_rels_with_domain(domainoid, ShareLock);
+	rels = get_rels_with_domain(domainoid, lockmode);
 
 	foreach(rt, rels)
 	{
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c567252acc4..bf1d3d1f584 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4512,11 +4512,13 @@ DomainConstraintElem:
 					n->contype = CONSTR_NOTNULL;
 					n->location = @1;
 					n->keys = list_make1(makeString("value"));
-					/* no NOT VALID, NO INHERIT support */
+					/* NO INHERIT is not supported */
 					processCASbits($3, @3, "NOT NULL",
 								   NULL, NULL, NULL,
-								   NULL, NULL, yyscanner);
-					n->initially_valid = true;
+								   &n->skip_validation,
+								   NULL, yyscanner);
+					n->is_enforced = true;
+					n->initially_valid = !n->skip_validation;
 					$$ = (Node *) n;
 				}
 		;
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index f15bd06adcc..e17bf0ec99b 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -1026,6 +1026,22 @@ my %tests = (
 		},
 	},
 
+	'DOMAIN CONSTRAINT NOT NULL / NOT VALID' => {
+		create_sql => 'CREATE DOMAIN dump_test.test_domain_nn AS INT;
+					   ALTER DOMAIN dump_test.test_domain_nn ADD CONSTRAINT nn NOT NULL NOT VALID;',
+		regexp => qr/^
+			\QALTER DOMAIN dump_test.test_domain_nn\E \n^\s+
+			\QADD CONSTRAINT nn NOT NULL NOT VALID;\E
+			/xm,
+		like => {
+			%full_runs, %dump_test_schema_runs, section_post_data => 1,
+		},
+		unlike => {
+			exclude_dump_test_schema => 1,
+			only_dump_measurement => 1,
+		},
+	},
+
 	'CONSTRAINT NOT NULL / NOT VALID (child1)' => {
 		regexp => qr/^
 		\QCREATE TABLE dump_test.test_table_nn_chld1 (\E\n
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 4352991e541..45f204e8818 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4593,7 +4593,10 @@ listDomains(const char *pattern, bool verbose, bool showSystem)
 					  "       pg_catalog.format_type(t.typbasetype, t.typtypmod) as \"%s\",\n"
 					  "       (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type bt\n"
 					  "        WHERE c.oid = t.typcollation AND bt.oid = t.typbasetype AND t.typcollation <> bt.typcollation) as \"%s\",\n"
-					  "       CASE WHEN t.typnotnull THEN 'not null' END as \"%s\",\n"
+					  "       CASE WHEN t.typnotnull THEN "
+					  "       (SELECT lower(pg_catalog.pg_get_constraintdef(r.oid, true)) FROM pg_catalog.pg_constraint r "
+					  "        WHERE t.oid = r.contypid AND r.contype = " CppAsString2(CONSTRAINT_NOTNULL) ")"
+					  "       END as \"%s\",\n"
 					  "       t.typdefault as \"%s\",\n"
 					  "       pg_catalog.array_to_string(ARRAY(\n"
 					  "         SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM pg_catalog.pg_constraint r WHERE t.oid = r.contypid AND r.contype = " CppAsString2(CONSTRAINT_CHECK) " ORDER BY r.conname\n"
diff --git a/src/test/regress/expected/domain.out b/src/test/regress/expected/domain.out
index 62a48a523a2..b6a3e5aa688 100644
--- a/src/test/regress/expected/domain.out
+++ b/src/test/regress/expected/domain.out
@@ -927,6 +927,43 @@ ALTER DOMAIN things VALIDATE CONSTRAINT meow;
 ERROR:  column "stuff" of table "thethings" contains values that violate the new constraint
 UPDATE thethings SET stuff = 10;
 ALTER DOMAIN things VALIDATE CONSTRAINT meow;
+ALTER DOMAIN things ADD CONSTRAINT nn1 NOT NULL;
+ALTER DOMAIN things ADD CONSTRAINT domain_nn NOT NULL NOT VALID; -- no-op
+ALTER DOMAIN things DROP NOT NULL;
+INSERT INTO thethings VALUES(NULL);
+ALTER DOMAIN things ADD CONSTRAINT domain_nn NOT NULL NOT VALID; -- ok
+INSERT INTO thethings VALUES(NULL); -- error
+ERROR:  domain things does not allow null values
+ALTER DOMAIN things ADD CONSTRAINT nn1 NOT NULL; -- error
+ERROR:  incompatible NOT VALID constraint "domain_nn" on domain "things"
+HINT:  You might need to validate it using ALTER DOMAIN ... VALIDATE CONSTRAINT.
+ALTER DOMAIN things ADD NOT NULL; -- error
+ERROR:  incompatible NOT VALID constraint "domain_nn" on domain "things"
+HINT:  You might need to validate it using ALTER DOMAIN ... VALIDATE CONSTRAINT.
+ALTER DOMAIN things SET NOT NULL; -- error
+ERROR:  column "stuff" of table "thethings" contains null values
+ALTER DOMAIN things VALIDATE CONSTRAINT domain_nn; -- error
+ERROR:  column "stuff" of table "thethings" contains null values
+ALTER DOMAIN things ADD CONSTRAINT domain_nn1 NOT NULL NOT VALID; -- no-op
+\dD things
+                                      List of domains
+ Schema |  Name  |  Type   | Collation |      Nullable      | Default |       Check        
+--------+--------+---------+-----------+--------------------+---------+--------------------
+ public | things | integer |           | not null not valid |         | CHECK (VALUE < 11)
+(1 row)
+
+SELECT conname, pg_get_constraintdef(oid)
+FROM   pg_constraint
+WHERE  contypid = 'things'::regtype AND contype = 'n';
+  conname  | pg_get_constraintdef 
+-----------+----------------------
+ domain_nn | NOT NULL NOT VALID
+(1 row)
+
+UPDATE thethings SET stuff = 10 WHERE stuff IS NULL;
+ALTER DOMAIN things SET NOT NULL; -- ok
+ALTER DOMAIN things VALIDATE CONSTRAINT domain_nn; -- ok
+ALTER DOMAIN things DROP NOT NULL;
 -- Confirm ALTER DOMAIN with RULES.
 create table domtab (col1 integer);
 create domain dom as integer;
diff --git a/src/test/regress/sql/domain.sql b/src/test/regress/sql/domain.sql
index b8f5a639712..9b367793450 100644
--- a/src/test/regress/sql/domain.sql
+++ b/src/test/regress/sql/domain.sql
@@ -537,6 +537,30 @@ ALTER DOMAIN things VALIDATE CONSTRAINT meow;
 UPDATE thethings SET stuff = 10;
 ALTER DOMAIN things VALIDATE CONSTRAINT meow;
 
+ALTER DOMAIN things ADD CONSTRAINT nn1 NOT NULL;
+ALTER DOMAIN things ADD CONSTRAINT domain_nn NOT NULL NOT VALID; -- no-op
+ALTER DOMAIN things DROP NOT NULL;
+
+INSERT INTO thethings VALUES(NULL);
+ALTER DOMAIN things ADD CONSTRAINT domain_nn NOT NULL NOT VALID; -- ok
+
+INSERT INTO thethings VALUES(NULL); -- error
+ALTER DOMAIN things ADD CONSTRAINT nn1 NOT NULL; -- error
+ALTER DOMAIN things ADD NOT NULL; -- error
+ALTER DOMAIN things SET NOT NULL; -- error
+ALTER DOMAIN things VALIDATE CONSTRAINT domain_nn; -- error
+ALTER DOMAIN things ADD CONSTRAINT domain_nn1 NOT NULL NOT VALID; -- no-op
+
+\dD things
+SELECT conname, pg_get_constraintdef(oid)
+FROM   pg_constraint
+WHERE  contypid = 'things'::regtype AND contype = 'n';
+
+UPDATE thethings SET stuff = 10 WHERE stuff IS NULL;
+ALTER DOMAIN things SET NOT NULL; -- ok
+ALTER DOMAIN things VALIDATE CONSTRAINT domain_nn; -- ok
+ALTER DOMAIN things DROP NOT NULL;
+
 -- Confirm ALTER DOMAIN with RULES.
 create table domtab (col1 integer);
 create domain dom as integer;
-- 
2.34.1