Subject: Re: using index to speedup add not null constraints to a table

From: jian he <[email-masked]>
Date: Fri, 18 Apr 2025 16:07:34 +0800

On Wed, Feb 5, 2025 at 4:24 PM jian he <[email-masked]> wrote:
>
> rebased new patch attached.
> I also did some cosmetic changes. comments refined.
> make sure using index_scan mechanism to fast check column not-null can
> only be used via btree index.
> isolation tests are simplified.

I realized that my previous patch was quite wrong,
we should not do indexscan verify individual not-null constraints on phase2.

So a new patch is attached,
the main idea is Phase2 collects all to be added not-null constraints
to AlteredTableInfo->constraints.
then in Phase3  check, can we use index to fast check not-null
constraint or not.

To minimize concurrency issues, using an index scan to quickly validate
NOT NULL constraints requires strict conditions in Phase3:
* No table rewrite
* No table scan
* Each NOT NULL constraint must have a suitable supporting index for
fast checking
* The table must already hold an AccessExclusiveLock
* The DDL must not involve creating any new indexes

I don't have any good ideas to do the regress tests.
I use
                ereport(NOTICE,
                        errmsg("all not-null constraints on relation
\"%s\" are validated by index scan",
                                RelationGetRelationName(oldrel)));
to do the tests.

for example:
create temp table t2 (x int, y int, z int, primary key (x, y));
create unique index t2_z_uidx on t2(z);

alter table t2 alter column z set not null;
NOTICE:  all not-null constraints on relation "t2" are validated by index scan
ALTER TABLE
From 0b4ad35e8fbc46f93ba6df2ef7013ff2f3055216 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Fri, 18 Apr 2025 16:05:42 +0800
Subject: [PATCH v4 1/1] using indexscan to speedup add not null constraints

This patch tries to use index_beginscan() / index_getnext() / index_endscan()
mentioned in [1] to speedup adding not-null constraints to the existing table.

the main logic happen in phase3 ATRewriteTable
1. collect all not-null constraints.
2. does each not-null constraint have a corresponding index to vertify it, if
   not then do the normal processing.
3. if table scan, table rewrite, table was not in AccessExclusiveLock.
   not-null constraint was on virtual generated column
   then we can not use indexcan to fast check not-null constraint.
3. If a table scan or rewrite occurs and the table is not held with an
   AccessExclusiveLock, and the NOT NULL constraint is on a virtual generated
   column, then we can not use indexscan to fast validate not-null constraints.

concurrency concern:
ALTER TABLE SET NOT NULL will take an ACCESS EXCLUSIVE lock, so there is less
variant of racing issue can occur?  to prove accurate, I wrote some isolation
tests. see[2]

performance:
It will only be slower than usual to add a NOT NULL constraint if your index is
bloated and a significant portion of that bloat is due to rows with NULL values.

demo:
drop table if exists t;
create unlogged table t(a int, b int, c int) with (autovacuum_enabled = off);
insert into t select g, g+1 from generate_series(1,1_000_000) g;
create index t_idx_a on t(a);

COMMAND: alter table t add constraint t1 not null a;
patch Time: 1.178 ms
master Time:  15.137 ms

100% table bloat: via (delete from t)
COMMAND: alter table t add constraint t1 not null a;
patch Time:   0.962 ms
master Time: 16.404 ms

case when: %20 percent values are NULL and have been deleted from heap but they
still on the index.

drop table if exists t;
create unlogged table t(a int, b int) with (autovacuum_enabled = off);
insert into t select case when g % 5 = 0 then null else g end, g+1
from generate_series(1,1_000_000) g;
create index t_idx_a on t(a);
delete from t where a is null;

alter table t add constraint t1 not null a;
patch Time:: 17.443 ms
master Time: 23.045 ms

references:
[1] https://postgr.es/m/CA%2BTgmoa5NKz8iGW_9v7wz%3D-%2BzQFu%3DE4SZoaTaU1znLaEXRYp-Q%40mail.gmail.com
[2] https://postgr.es/m/900056D1-32DF-4927-8251-3E0C0DC407FD%40anarazel.de
discussion: https://postgr.es/m/CACJufxFiW=4k1is=F1J=[email-masked]
commitfest entry: https://commitfest.postgresql.org/patch/5444/
---
 src/backend/commands/tablecmds.c              | 129 ++++++++++-
 src/backend/executor/execIndexing.c           | 200 ++++++++++++++++++
 src/include/executor/executor.h               |   2 +
 .../expected/indexscan-check-notnull.out      | 102 +++++++++
 src/test/isolation/isolation_schedule         |   1 +
 .../specs/indexscan-check-notnull.spec        |  45 ++++
 src/test/regress/expected/aggregates.out      |   1 +
 src/test/regress/expected/alter_table.out     |  42 ++++
 src/test/regress/expected/indexing.out        |   2 +
 src/test/regress/expected/publication.out     |   1 +
 src/test/regress/expected/tablespace.out      |   1 +
 src/test/regress/sql/alter_table.sql          |  46 ++++
 12 files changed, 566 insertions(+), 6 deletions(-)
 create mode 100644 src/test/isolation/expected/indexscan-check-notnull.out
 create mode 100644 src/test/isolation/specs/indexscan-check-notnull.spec

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index b3ed69457fc..01011a72aea 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -209,16 +209,19 @@ typedef struct AlteredTableInfo
 	List	   *changedStatisticsDefs;	/* string definitions of same */
 } AlteredTableInfo;
 
-/* Struct describing one new constraint to check in Phase 3 scan */
-/* Note: new not-null constraints are handled elsewhere */
+/*
+ * Struct describing one new constraint to check in Phase 3 scan. Note: new
+ * not-null constraints are handled here too.
+*/
 typedef struct NewConstraint
 {
 	char	   *name;			/* Constraint name, or NULL if none */
-	ConstrType	contype;		/* CHECK or FOREIGN */
+	ConstrType	contype;		/* CHECK or FOREIGN or NOT-NULL */
 	Oid			refrelid;		/* PK rel, if FOREIGN */
 	Oid			refindid;		/* OID of PK's index, if FOREIGN */
 	bool		conwithperiod;	/* Whether the new FOREIGN KEY uses PERIOD */
 	Oid			conid;			/* OID of pg_constraint entry, if FOREIGN */
+	int			attnum;			/* NOT-NULL constraint attnum */
 	Node	   *qual;			/* Check expr or CONSTR_FOREIGN Constraint */
 	ExprState  *qualstate;		/* Execution state for CHECK expr */
 } NewConstraint;
@@ -6128,6 +6131,7 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
 	BulkInsertState bistate;
 	int			ti_options;
 	ExprState  *partqualstate = NULL;
+	bool		add_index = false;
 
 	/*
 	 * Open the relation(s).  We have surely already locked the existing
@@ -6181,6 +6185,9 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
 				needscan = true;
 				con->qualstate = ExecPrepareExpr((Expr *) expand_generated_columns_in_expr(con->qual, oldrel, 1), estate);
 				break;
+			case CONSTR_NOTNULL:
+				/* Nothing to do here */
+				break;
 			case CONSTR_FOREIGN:
 				/* Nothing to do here */
 				break;
@@ -6234,10 +6241,85 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
 														wholeatt->attnum);
 			}
 		}
-		if (notnull_attrs || notnull_virtual_attrs)
-			needscan = true;
 	}
 
+	/*
+	 * we better use existsing index to check not-null constraint, rather than
+	 * through underlying index created by this command.  XXX TODO maybe this is
+	 * not necessary?
+	*/
+	for (AlterTablePass pass = 0; pass < AT_NUM_PASSES; pass++)
+	{
+		List	   *subcmds = tab->subcmds[pass];
+
+		if (subcmds == NIL)
+			continue;
+
+		foreach_node(AlterTableCmd, cmd, subcmds)
+		{
+			if (cmd->subtype == AT_AddIndex ||
+				cmd->subtype == AT_ReAddIndex ||
+				cmd->subtype == AT_AddIndexConstraint)
+				add_index = true;
+		}
+	}
+
+	/*
+	 * The conditions for using indexscan mechanism fast checking not-null
+	 * constraints are quite strict.  If a table scan or rewrite is expected to
+	 * occur later, using indexscan would just waste cycle.  Additionally,
+	 * indexes cannot be created on virtual generated columns, so fast checking
+	 * not-null constraints is not applicable to them.  This optimization is
+	 * also limited to regular relations.
+	 *
+	 * To avoid concurrency issue, we only do it when table was locked in
+	 * AccessExclusiveLock.
+	*/
+	if (!needscan && notnull_virtual_attrs == NIL &&
+		newrel == NULL && !tab->rewrite &&
+		oldrel->rd_rel->relkind == RELKIND_RELATION &&
+		!add_index &&
+		CheckRelationLockedByMe(oldrel, AccessExclusiveLock, false))
+	{
+		List	   *notnull_attnums = NIL;
+
+		foreach(l, tab->constraints)
+		{
+			NewConstraint *con = lfirst(l);
+
+			Form_pg_attribute attr = TupleDescAttr(newTupDesc, con->attnum - 1);
+
+			if (con->contype != CONSTR_NOTNULL)
+				continue;
+
+			Assert(attr->attnotnull);
+			Assert(con->attnum > 0);
+			Assert(attr->attnum == con->attnum);
+
+			if (attr->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+			{
+				needscan = true;
+				break;
+			}
+
+			notnull_attnums = lappend_int(notnull_attnums, attr->attnum);
+		}
+
+		if (notnull_attnums != NIL)
+		{
+			Assert(CheckRelationLockedByMe(oldrel, AccessExclusiveLock, false));
+
+			if (!index_check_notnull(oldrel, notnull_attnums))
+				needscan = true;
+			else
+				ereport(NOTICE,
+						errmsg("all not-null constraints on relation \"%s\" are validated by index scan",
+								RelationGetRelationName(oldrel)));
+		}
+	}
+	else if (notnull_attrs || notnull_virtual_attrs)
+		needscan = true;
+
 	if (newrel || needscan)
 	{
 		ExprContext *econtext;
@@ -7909,6 +7991,7 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
 	CookedConstraint *ccon;
 	List	   *cooked;
 	bool		is_no_inherit = false;
+	AlteredTableInfo *tab = NULL;
 
 	/* Guard against stack overflow due to overly deep inheritance tree. */
 	check_stack_depth();
@@ -8033,10 +8116,31 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
 	constraint->is_no_inherit = is_no_inherit;
 	constraint->conname = conName;
 
+	tab = ATGetQueueEntry(wqueue, rel);
+
 	/* and do it */
 	cooked = AddRelationNewConstraints(rel, NIL, list_make1(constraint),
 									   false, !recursing, false, NULL);
 	ccon = linitial(cooked);
+
+	Assert(ccon->contype == CONSTR_NOTNULL);
+
+	/*
+	 * we may use indexscan to fast check not-null constraint. To do that, we
+	 * need add the not-null constraint to AlteredTableInfo->constraints.
+	*/
+	if (!ccon->skip_validation)
+	{
+		NewConstraint *newcon;
+		newcon = (NewConstraint *) palloc0(sizeof(NewConstraint));
+
+		newcon->name = ccon->name;
+		newcon->contype = ccon->contype;
+		newcon->attnum = ccon->attnum;
+
+		tab->constraints = lappend(tab->constraints, newcon);
+	}
+
 	ObjectAddressSet(address, ConstraintRelationId, ccon->conoid);
 
 	InvokeObjectPostAlterHook(RelationRelationId,
@@ -9887,13 +9991,15 @@ ATAddCheckNNConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
 	{
 		CookedConstraint *ccon = (CookedConstraint *) lfirst(lcon);
 
-		if (!ccon->skip_validation && ccon->contype != CONSTR_NOTNULL)
+		if (!ccon->skip_validation)
 		{
 			NewConstraint *newcon;
 
 			newcon = (NewConstraint *) palloc0(sizeof(NewConstraint));
 			newcon->name = ccon->name;
 			newcon->contype = ccon->contype;
+			if (ccon->contype == CONSTR_NOTNULL)
+				newcon->attnum = ccon->attnum;
 			newcon->qual = ccon->expr;
 
 			tab->constraints = lappend(tab->constraints, newcon);
@@ -13146,6 +13252,7 @@ QueueNNConstraintValidation(List **wqueue, Relation conrel, Relation rel,
 	List	   *children = NIL;
 	AttrNumber	attnum;
 	char	   *colname;
+	NewConstraint *newcon;
 
 	con = (Form_pg_constraint) GETSTRUCT(contuple);
 	Assert(con->contype == CONSTRAINT_NOTNULL);
@@ -13209,7 +13316,17 @@ QueueNNConstraintValidation(List **wqueue, Relation conrel, Relation rel,
 	/* Set attnotnull appropriately without queueing another validation */
 	set_attnotnull(NULL, rel, attnum, true, false);
 
+	/* Queue validation for phase 3 */
+	newcon = (NewConstraint *) palloc0(sizeof(NewConstraint));
+	newcon->name = colname;
+	newcon->contype = CONSTR_NOTNULL;
+	newcon->attnum = attnum;
+
+	/* Find or create work queue entry for this table */
 	tab = ATGetQueueEntry(wqueue, rel);
+
+	tab->constraints = lappend(tab->constraints, newcon);
+
 	tab->verify_new_notnull = true;
 
 	/*
diff --git a/src/backend/executor/execIndexing.c b/src/backend/executor/execIndexing.c
index bdf862b2406..92a0e692d01 100644
--- a/src/backend/executor/execIndexing.c
+++ b/src/backend/executor/execIndexing.c
@@ -108,12 +108,15 @@
 
 #include "access/genam.h"
 #include "access/relscan.h"
+#include "access/table.h"
 #include "access/tableam.h"
 #include "access/xact.h"
 #include "catalog/index.h"
+#include "catalog/pg_am_d.h"
 #include "executor/executor.h"
 #include "nodes/nodeFuncs.h"
 #include "storage/lmgr.h"
+#include "utils/fmgroids.h"
 #include "utils/multirangetypes.h"
 #include "utils/rangetypes.h"
 #include "utils/snapmgr.h"
@@ -145,6 +148,7 @@ static bool index_expression_changed_walker(Node *node,
 											Bitmapset *allUpdatedCols);
 static void ExecWithoutOverlapsNotEmpty(Relation rel, NameData attname, Datum attval,
 										char typtype, Oid atttypid);
+static bool index_check_notnull_internal(Relation relation, List *attnums, List *idxs);
 
 /* ----------------------------------------------------------------
  *		ExecOpenIndices
@@ -1172,3 +1176,199 @@ ExecWithoutOverlapsNotEmpty(Relation rel, NameData attname, Datum attval, char t
 				 errmsg("empty WITHOUT OVERLAPS value found in column \"%s\" in relation \"%s\"",
 						NameStr(attname), RelationGetRelationName(rel))));
 }
+
+/*
+ * notnull_attnums: Attribute numbers for all newly added NOT NULL constraints.
+ *
+ * First check if relation have suitable index for each notnull_attnums. If it
+ * does, using indexscan mechanism to verify that all attributes on
+ * notnull_attnums are indeed NOT NULL.
+ */
+bool
+index_check_notnull(Relation relation, List *notnull_attnums)
+{
+	Relation	indrel;
+	Relation	index_rel;
+	SysScanDesc indscan;
+	ScanKeyData skey;
+	HeapTuple	htup;
+	TupleDesc	tupdesc;
+	Form_pg_attribute attr;
+	List	   *result = NIL;
+	List	   *attnums = NIL;
+
+	tupdesc = RelationGetDescr(relation);
+
+	/* Prepare to scan pg_index for entries having indrelid = this rel. */
+	ScanKeyInit(&skey,
+				Anum_pg_index_indrelid,
+				BTEqualStrategyNumber, F_OIDEQ,
+				ObjectIdGetDatum(RelationGetRelid(relation)));
+
+	indrel = table_open(IndexRelationId, AccessShareLock);
+	indscan = systable_beginscan(indrel, IndexIndrelidIndexId, true,
+								 NULL, 1, &skey);
+
+	while (HeapTupleIsValid(htup = systable_getnext(indscan)))
+	{
+		Form_pg_index index = (Form_pg_index) GETSTRUCT(htup);
+
+		/*
+		 * we can only use non-deferred, valid, and alive btree index to fast
+		 * check not-null
+		 */
+		if (!index->indimmediate || !index->indisvalid || !index->indislive)
+			continue;
+
+		/* can not use expression index or partical index too */
+		if (!heap_attisnull(htup, Anum_pg_index_indexprs, NULL) ||
+			!heap_attisnull(htup, Anum_pg_index_indpred, NULL))
+			continue;
+
+		for (int i = 0; i < index->indnkeyatts; i++)
+		{
+			attr = TupleDescAttr(tupdesc, (index->indkey.values[i] - 1));
+
+			if (list_member_int(notnull_attnums, attr->attnum))
+			{
+				index_rel = index_open(index->indexrelid, AccessShareLock);
+
+				if (index_rel->rd_rel->relkind == RELKIND_INDEX &&
+					index_rel->rd_rel->relam == BTREE_AM_OID &&
+					!list_member_int(attnums, attr->attnum))
+				{
+					attnums = lappend_int(attnums, attr->attnum);
+					result = lappend_oid(result, index->indexrelid);
+				}
+				index_close(index_rel, AccessShareLock);
+			}
+		}
+	}
+	systable_endscan(indscan);
+	table_close(indrel, AccessShareLock);
+
+	if (attnums == NIL)
+		return false;
+
+	/*
+	 * every not-null constraint requires an index for fast validatation.
+	 * Without an index, we better use normal tablescan to validate the not-null
+	 * constraints
+	*/
+	foreach_int(attno, notnull_attnums)
+	{
+		if(!list_member_int(attnums, attno))
+			return false;
+	}
+
+	return index_check_notnull_internal(relation, attnums, result);
+}
+
+/*
+ * Use indexscan mechanism to fast check each attribute in "attnums" are
+ * not-null or not.
+ */
+static bool
+index_check_notnull_internal(Relation relation, List *attnums, List *idxs)
+{
+	EState	   *estate;
+	ExprContext *econtext;
+	TupleTableSlot *existing_slot;
+	bool 		all_not_null = true;
+	ListCell   *lc,
+				*lc2;
+
+	/*
+	 * Need an EState for slot to hold the current tuple.
+	 *
+	*/
+	estate = CreateExecutorState();
+	econtext = GetPerTupleExprContext(estate);
+
+	forboth(lc, attnums, lc2, idxs)
+	{
+		SnapshotData DirtySnapshot;
+		IndexScanDesc index_scan;
+		ScanKeyData		scankeys[INDEX_MAX_KEYS];
+		IndexInfo  	*indexInfo;
+		AttrNumber	sk_attno = -1;
+		Relation 	index;
+		int			indnkeyatts;
+
+		AttrNumber	attno = lfirst_int(lc);
+		Oid		indexoid = lfirst_oid(lc2);
+
+		existing_slot = table_slot_create(relation, NULL);
+
+		/* Arrange for econtext's scan tuple to be the tuple under test */
+		econtext->ecxt_scantuple = existing_slot;
+
+		index = index_open(indexoid, AccessShareLock);
+
+		indexInfo = BuildIndexInfo(index);
+		indnkeyatts = IndexRelationGetNumberOfKeyAttributes(index);
+
+		/*
+		 * Search the tuples that are in the index for any violations, including
+		 * tuples that aren't visible yet.
+		*/
+		InitDirtySnapshot(DirtySnapshot);
+
+		for (int i = 0; i < indnkeyatts; i++)
+		{
+			if (indexInfo->ii_IndexAttrNumbers[i] == attno)
+			{
+				sk_attno = i+1;
+				break;
+			}
+		}
+
+		if (sk_attno == -1)
+			elog(ERROR, "index %u should effect on column number %d", indexoid, attno);
+
+		for (int i = 0; i < indnkeyatts; i++)
+		{
+			/* set up an IS NULL scan key so that we ignore not nulls */
+			ScanKeyEntryInitialize(&scankeys[i],
+									SK_ISNULL | SK_SEARCHNULL,
+									sk_attno,		/* index col to scan */
+									InvalidStrategy,/* no strategy */
+									InvalidOid,		/* no strategy subtype */
+									InvalidOid,		/* no collation */
+									InvalidOid,		/* no reg proc for this */
+									(Datum) 0);		/* constant */
+		}
+
+		index_scan = index_beginscan(relation, index, &DirtySnapshot, NULL, indnkeyatts, 0);
+		index_rescan(index_scan, scankeys, indnkeyatts, NULL, 0);
+
+		while (index_getnext_slot(index_scan, ForwardScanDirection, existing_slot))
+		{
+			Datum		values[INDEX_MAX_KEYS];
+			bool		nulls[INDEX_MAX_KEYS];
+
+			/*
+			 * Extract the index column values and isnull flags from the
+			 * existing tuple.
+			*/
+			FormIndexDatum(indexInfo, existing_slot, estate, values, nulls);
+
+			if (nulls[sk_attno - 1])
+			{
+				all_not_null = false;
+				break;
+			}
+		}
+
+		index_endscan(index_scan);
+		index_close(index, AccessShareLock);
+		ExecDropSingleTupleTableSlot(existing_slot);
+		if (!all_not_null)
+			return false;
+	}
+
+	FreeExecutorState(estate);
+
+	return true;
+}
+
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index ae99407db89..f42407ed75e 100644
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -778,6 +778,8 @@ extern void check_exclusion_constraint(Relation heap, Relation index,
 									   const Datum *values, const bool *isnull,
 									   EState *estate, bool newIndex);
 
+extern bool index_check_notnull(Relation relation, List *notnull_attnums);
+
 /*
  * prototypes from functions in execReplication.c
  */
diff --git a/src/test/isolation/expected/indexscan-check-notnull.out b/src/test/isolation/expected/indexscan-check-notnull.out
new file mode 100644
index 00000000000..ac3b9f14c89
--- /dev/null
+++ b/src/test/isolation/expected/indexscan-check-notnull.out
@@ -0,0 +1,102 @@
+Parsed test spec with 2 sessions
+
+starting permutation: b1 b3 m1 hj c1 c3 sn
+step b1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step b3: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step m1: DELETE FROM t;
+step hj: ALTER TABLE t ADD CONSTRAINT t1_nn NOT NULL a; <waiting ...>
+step c1: COMMIT;
+s2: NOTICE:  all not-null constraints on relation "t" are validated by index scan
+step hj: <... completed>
+step c3: COMMIT;
+step sn: SELECT conname, conrelid::regclass, contype, convalidated
+           FROM pg_constraint WHERE conname = 't1_nn';
+
+conname|conrelid|contype|convalidated
+-------+--------+-------+------------
+t1_nn  |t       |n      |t           
+(1 row)
+
+
+starting permutation: b2 b3 m1 hj c1 c3 sn
+step b2: BEGIN;
+step b3: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step m1: DELETE FROM t;
+step hj: ALTER TABLE t ADD CONSTRAINT t1_nn NOT NULL a; <waiting ...>
+step c1: COMMIT;
+s2: NOTICE:  all not-null constraints on relation "t" are validated by index scan
+step hj: <... completed>
+step c3: COMMIT;
+step sn: SELECT conname, conrelid::regclass, contype, convalidated
+           FROM pg_constraint WHERE conname = 't1_nn';
+
+conname|conrelid|contype|convalidated
+-------+--------+-------+------------
+t1_nn  |t       |n      |t           
+(1 row)
+
+
+starting permutation: b1 b4 m1 hj c1 c3 sn
+step b1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step b4: BEGIN;
+step m1: DELETE FROM t;
+step hj: ALTER TABLE t ADD CONSTRAINT t1_nn NOT NULL a; <waiting ...>
+step c1: COMMIT;
+s2: NOTICE:  all not-null constraints on relation "t" are validated by index scan
+step hj: <... completed>
+step c3: COMMIT;
+step sn: SELECT conname, conrelid::regclass, contype, convalidated
+           FROM pg_constraint WHERE conname = 't1_nn';
+
+conname|conrelid|contype|convalidated
+-------+--------+-------+------------
+t1_nn  |t       |n      |t           
+(1 row)
+
+
+starting permutation: b1 b3 hj r1 c2
+step b1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step b3: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step hj: ALTER TABLE t ADD CONSTRAINT t1_nn NOT NULL a;
+ERROR:  column "a" of relation "t" contains null values
+step r1: ROLLBACK;
+step c2: ROLLBACK;
+
+starting permutation: b1 b4 d1 m1 c2 s1 sn r1
+step b1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step b4: BEGIN;
+step d1: DROP INDEX t_ab_idx;
+step m1: DELETE FROM t; <waiting ...>
+step c2: ROLLBACK;
+step m1: <... completed>
+s1: NOTICE:  all not-null constraints on relation "t" are validated by index scan
+step s1: ALTER TABLE t ADD CONSTRAINT t1_nn NOT NULL a;
+step sn: SELECT conname, conrelid::regclass, contype, convalidated
+           FROM pg_constraint WHERE conname = 't1_nn';
+
+conname|conrelid|contype|convalidated
+-------+--------+-------+------------
+t1_nn  |t       |n      |t           
+(1 row)
+
+step r1: ROLLBACK;
+
+starting permutation: b2 b3 m1 d1 s0 s1 c1 c3 sn
+step b2: BEGIN;
+step b3: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step m1: DELETE FROM t;
+step d1: DROP INDEX t_ab_idx; <waiting ...>
+step s0: savepoint s0;
+s1: NOTICE:  all not-null constraints on relation "t" are validated by index scan
+step s1: ALTER TABLE t ADD CONSTRAINT t1_nn NOT NULL a;
+step c1: COMMIT;
+step d1: <... completed>
+step c3: COMMIT;
+step sn: SELECT conname, conrelid::regclass, contype, convalidated
+           FROM pg_constraint WHERE conname = 't1_nn';
+
+conname|conrelid|contype|convalidated
+-------+--------+-------+------------
+t1_nn  |t       |n      |t           
+(1 row)
+
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index e3c669a29c7..1b9e8f932ac 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -56,6 +56,7 @@ test: merge-delete
 test: merge-update
 test: merge-match-recheck
 test: merge-join
+test: indexscan-check-notnull
 test: delete-abort-savept
 test: delete-abort-savept-2
 test: aborted-keyrevoke
diff --git a/src/test/isolation/specs/indexscan-check-notnull.spec b/src/test/isolation/specs/indexscan-check-notnull.spec
new file mode 100644
index 00000000000..d0ca928f3af
--- /dev/null
+++ b/src/test/isolation/specs/indexscan-check-notnull.spec
@@ -0,0 +1,45 @@
+
+#
+# using indexscan to check a column not-null constraint
+# is satisfied or not.
+#
+
+setup
+{
+  CREATE TABLE t (a int, b int);
+  CREATE INDEX t_ab_idx on t(a,b);
+  INSERT INTO t values (null, 1);
+  INSERT INTO t SELECT x, x*10 FROM generate_series(1,3) g(x);
+}
+
+teardown
+{
+  DROP TABLE t;
+}
+
+session s1
+step b1  { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step b2  { BEGIN; }
+step m1  { DELETE FROM t;}
+step s0  { savepoint s0;}
+step s1  { ALTER TABLE t ADD CONSTRAINT t1_nn NOT NULL a;}
+step sn  { SELECT conname, conrelid::regclass, contype, convalidated
+           FROM pg_constraint WHERE conname = 't1_nn';
+         }
+step r1  { ROLLBACK; }
+step c1  { COMMIT; }
+
+session s2
+step b3  { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step b4  { BEGIN; }
+step hj  { ALTER TABLE t ADD CONSTRAINT t1_nn NOT NULL a;}
+step d1  { DROP INDEX t_ab_idx;}
+step c2  { ROLLBACK; }
+step c3  { COMMIT; }
+
+permutation b1 b3 m1 hj c1 c3 sn
+permutation b2 b3 m1 hj c1 c3 sn
+permutation b1 b4 m1 hj c1 c3 sn
+permutation b1 b3 hj r1 c2
+permutation b1 b4 d1 m1 c2 s1 sn r1
+permutation b2 b3 m1 d1 s0 s1 c1 c3 sn
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index 6b6371c3e74..6dab127f73b 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -1461,6 +1461,7 @@ explain (costs off) select y,z from t2 group by y,z;
 
 -- Make the column NOT NULL and ensure we remove the redundant column
 alter table t2 alter column z set not null;
+NOTICE:  all not-null constraints on relation "t2" are validated by index scan
 explain (costs off) select y,z from t2 group by y,z;
       QUERY PLAN      
 ----------------------
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 476266e3f4b..a00fe5175ba 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -4833,3 +4833,45 @@ drop publication pub1;
 drop schema alter1 cascade;
 drop schema alter2 cascade;
 NOTICE:  drop cascades to table alter2.t1
+-----fast add not-null constraint tests.
+CREATE TABLE tp (a int, b int GENERATED ALWAYS AS (22) stored) PARTITION BY range(a);
+CREATE TABLE tpp1(a int, b int GENERATED ALWAYS AS (a+1) stored);
+CREATE TABLE tpp2(a int, b int GENERATED ALWAYS AS (a+10) stored);
+ALTER TABLE tp ATTACH PARTITION tpp1 FOR values from ( 1 ) to (10);
+ALTER TABLE tp ATTACH PARTITION tpp2 FOR values from ( 10 ) to (21);
+INSERT INTO tp select g from generate_series(1,19) g;
+CREATE INDEX ON tp(b);
+ALTER TABLE tp alter column b set not null;
+NOTICE:  all not-null constraints on relation "tpp1" are validated by index scan
+NOTICE:  all not-null constraints on relation "tpp2" are validated by index scan
+ALTER TABLE tp alter column b drop not null;
+ALTER TABLE tpp1 alter column b set not null;
+NOTICE:  all not-null constraints on relation "tpp1" are validated by index scan
+ALTER TABLE tpp2 alter column b set not null;
+NOTICE:  all not-null constraints on relation "tpp2" are validated by index scan
+ALTER TABLE tp alter column b drop not null;
+ALTER TABLE tp alter column b set not null, ALTER COLUMN b SET EXPRESSION AS (a * 3);
+------test non-partitioned table
+CREATE TABLE t1(f1 INT, f2 int, f3 int,f4 int, f5 int);
+INSERT INTO t1 select g, g+1, g+2, g+3, g+4 from generate_series(1, 100) g;
+CREATE INDEX t1_f1_f2_idx ON t1(f1,f2);
+CREATE UNIQUE INDEX t1_f3idx ON t1(f3);
+CREATE INDEX t1_f3f4idx ON t1(f3) include(f4);
+CREATE INDEX hash_f5_idx ON t1 USING hash (f5 int4_ops);
+--table rewrite, so can not use indexscan to check not-null constraint.
+ALTER TABLE t1 alter column f1 set not null, ALTER f1 TYPE BIGINT;
+ALTER TABLE t1 alter column f1 drop not null;
+ALTER TABLE t1 add constraint nn not null f1, ALTER f1 TYPE BIGINT; --now ok
+ALTER TABLE t1 alter column f1 drop not null, alter column f2 drop not null;
+INSERT INTO t1 values(11, NULL, 1,2,3);
+ALTER TABLE t1 alter column f2 set not null, alter column f1 set not null; --error
+ERROR:  column "f2" of relation "t1" contains null values
+DELETE FROM t1 where f1 = 11;
+ALTER TABLE t1 alter column f2 set not null, alter column f1 set not null; --ok
+NOTICE:  all not-null constraints on relation "t1" are validated by index scan
+--using indexscan to check not-null can only apply to key columns, not include column
+ALTER TABLE t1 add constraint nnf4 not null f4;
+--cannot fast ALTER TABLE SET NOT NULL by utilizing hash index.
+ALTER TABLE t1 add constraint nn_f5 not null f5;
+drop table t1;
+drop table tpp1, tpp2, tp;
diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out
index bcf1db11d73..5f106c74d87 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -1186,6 +1186,7 @@ create table idxpart0 partition of idxpart (i) for values with (modulus 2, remai
 create table idxpart1 partition of idxpart (i) for values with (modulus 2, remainder 1);
 alter table idxpart0 add primary key(i);
 alter table idxpart add primary key(i);
+NOTICE:  all not-null constraints on relation "idxpart1" are validated by index scan
 select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid,
   conname, conislocal, coninhcount, connoinherit, convalidated
   from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid)
@@ -1279,6 +1280,7 @@ alter table idxpart attach partition idxpart0 default;
 alter table only idxpart add primary key (a);  -- fail, no not-null constraint
 ERROR:  column "a" of table "idxpart0" is not marked NOT NULL
 alter table idxpart0 alter column a set not null;
+NOTICE:  all not-null constraints on relation "idxpart0" are validated by index scan
 alter table only idxpart add primary key (a);  -- now it works
 alter index idxpart_pkey attach partition idxpart0_a_key;
 drop table idxpart;
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 4de96c04f9d..e985d4f5022 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -778,6 +778,7 @@ ALTER PUBLICATION testpub_fortable_insert ADD TABLE testpub_tbl5 (b, c);
 /* not all replica identities are good enough */
 CREATE UNIQUE INDEX testpub_tbl5_b_key ON testpub_tbl5 (b, c);
 ALTER TABLE testpub_tbl5 ALTER b SET NOT NULL, ALTER c SET NOT NULL;
+NOTICE:  all not-null constraints on relation "testpub_tbl5" are validated by index scan
 ALTER TABLE testpub_tbl5 REPLICA IDENTITY USING INDEX testpub_tbl5_b_key;
 -- error: replica identity (b,c) is not covered by column list (a, c)
 UPDATE testpub_tbl5 SET a = 1;
diff --git a/src/test/regress/expected/tablespace.out b/src/test/regress/expected/tablespace.out
index a90e39e5738..d28bccb5378 100644
--- a/src/test/regress/expected/tablespace.out
+++ b/src/test/regress/expected/tablespace.out
@@ -603,6 +603,7 @@ INSERT INTO testschema.test_default_tab_p VALUES (1);
 CREATE INDEX test_index1 on testschema.test_default_tab_p (val);
 CREATE INDEX test_index2 on testschema.test_default_tab_p (val) TABLESPACE regress_tblspace;
 ALTER TABLE testschema.test_default_tab_p ADD CONSTRAINT test_index3 PRIMARY KEY (id);
+NOTICE:  all not-null constraints on relation "test_default_tab_p1" are validated by index scan
 ALTER TABLE testschema.test_default_tab_p ADD CONSTRAINT test_index4 UNIQUE (id) USING INDEX TABLESPACE regress_tblspace;
 \d testschema.test_index1
 Partitioned index "testschema.test_index1"
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 5ce9d1e429f..626fd3798f8 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -3125,3 +3125,49 @@ alter table alter1.t1 set schema alter2;
 drop publication pub1;
 drop schema alter1 cascade;
 drop schema alter2 cascade;
+
+-----fast add not-null constraint tests.
+CREATE TABLE tp (a int, b int GENERATED ALWAYS AS (22) stored) PARTITION BY range(a);
+CREATE TABLE tpp1(a int, b int GENERATED ALWAYS AS (a+1) stored);
+CREATE TABLE tpp2(a int, b int GENERATED ALWAYS AS (a+10) stored);
+ALTER TABLE tp ATTACH PARTITION tpp1 FOR values from ( 1 ) to (10);
+ALTER TABLE tp ATTACH PARTITION tpp2 FOR values from ( 10 ) to (21);
+INSERT INTO tp select g from generate_series(1,19) g;
+CREATE INDEX ON tp(b);
+
+ALTER TABLE tp alter column b set not null;
+ALTER TABLE tp alter column b drop not null;
+
+ALTER TABLE tpp1 alter column b set not null;
+ALTER TABLE tpp2 alter column b set not null;
+
+ALTER TABLE tp alter column b drop not null;
+ALTER TABLE tp alter column b set not null, ALTER COLUMN b SET EXPRESSION AS (a * 3);
+
+------test non-partitioned table
+CREATE TABLE t1(f1 INT, f2 int, f3 int,f4 int, f5 int);
+INSERT INTO t1 select g, g+1, g+2, g+3, g+4 from generate_series(1, 100) g;
+CREATE INDEX t1_f1_f2_idx ON t1(f1,f2);
+CREATE UNIQUE INDEX t1_f3idx ON t1(f3);
+CREATE INDEX t1_f3f4idx ON t1(f3) include(f4);
+CREATE INDEX hash_f5_idx ON t1 USING hash (f5 int4_ops);
+
+--table rewrite, so can not use indexscan to check not-null constraint.
+ALTER TABLE t1 alter column f1 set not null, ALTER f1 TYPE BIGINT;
+ALTER TABLE t1 alter column f1 drop not null;
+ALTER TABLE t1 add constraint nn not null f1, ALTER f1 TYPE BIGINT; --now ok
+
+ALTER TABLE t1 alter column f1 drop not null, alter column f2 drop not null;
+INSERT INTO t1 values(11, NULL, 1,2,3);
+ALTER TABLE t1 alter column f2 set not null, alter column f1 set not null; --error
+DELETE FROM t1 where f1 = 11;
+ALTER TABLE t1 alter column f2 set not null, alter column f1 set not null; --ok
+
+--using indexscan to check not-null can only apply to key columns, not include column
+ALTER TABLE t1 add constraint nnf4 not null f4;
+
+--cannot fast ALTER TABLE SET NOT NULL by utilizing hash index.
+ALTER TABLE t1 add constraint nn_f5 not null f5;
+
+drop table t1;
+drop table tpp1, tpp2, tp;
-- 
2.34.1

---

From: jian he <[email-masked]>
Date: Mon, 28 Apr 2025 12:36:14 +0800

>                                 RelationGetRelationName(oldrel)));
for tests, just found out i can imitate
src/test/modules/test_misc/t/001_constraint_validation.pl,

So I created a file:
src/test/modules/test_misc/t/008_indexscan_validate_notnull.pl
for TAP tests.
From 8072ef9578eafe6facf29996d08e129d60a8419e Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Mon, 28 Apr 2025 12:27:13 +0800
Subject: [PATCH v5 1/1] using indexscan to speedup add not null constraints

concurrency concern:

performance:
demo:

drop table if exists t;
create unlogged table t(a int, b int) with (autovacuum_enabled = off, vacuum_index_cleanup=off);

alter table t alter column a drop not null;
alter table t add constraint t1 not null a;

the above two statement running serval times:
patch Time:: 1.084  ms
master Time: 12.045 ms

references:
[1] https://postgr.es/m/CA%2BTgmoa5NKz8iGW_9v7wz%3D-%2BzQFu%3DE4SZoaTaU1znLaEXRYp-Q%40mail.gmail.com
[2] https://postgr.es/m/900056D1-32DF-4927-8251-3E0C0DC407FD%40anarazel.de
discussion: https://postgr.es/m/CACJufxFiW=4k1is=F1J=[email-masked]
commitfest entry: https://commitfest.postgresql.org/patch/5444
---
 src/backend/commands/tablecmds.c              | 113 +++++++++-
 src/backend/executor/execIndexing.c           | 195 ++++++++++++++++++
 src/include/executor/executor.h               |   2 +
 .../expected/indexscan-check-notnull.out      | 102 +++++++++
 src/test/isolation/isolation_schedule         |   1 +
 .../specs/indexscan-check-notnull.spec        |  45 ++++
 src/test/modules/test_misc/meson.build        |   1 +
 .../t/008_indexscan_validate_notnull.pl       | 138 +++++++++++++
 8 files changed, 591 insertions(+), 6 deletions(-)
 create mode 100644 src/test/isolation/expected/indexscan-check-notnull.out
 create mode 100644 src/test/isolation/specs/indexscan-check-notnull.spec
 create mode 100644 src/test/modules/test_misc/t/008_indexscan_validate_notnull.pl

index 2705cf11330..a784dbfbeac 100644
 
+	int			attnum;			/* NOT-NULL constraint attribute number */
@@ -6182,6 +6185,9 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
@@ -6235,10 +6241,70 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
 
+	/*
+	 * The conditions for using indexscan mechanism fast validate not-null
+	 * constraints are quite strict. All of the following conditions must be
+	 * met.
+	 * 1. We must have the needs to validate not-null constraints
+	 *    (AlteredTableInfo->verify_new_notnull is true).
+	 * 2. If a table scan or table rewrite is expected later, using indexscan is
+	 *    just wastes cycles.
+	 * 3. indexes cannot be created on virtual generated columns, so fast
+	 *    checking not-null constraints is not applicable to them.
+	 * 4. optimization is also limited to regular relations.
+	 * 5. To avoid concurrency issue, we only do it when table was locked in
+	 *    AccessExclusiveLock.
+	*/
+	if (!needscan &&
+		newrel == NULL &&
+		!tab->rewrite &&
+		tab->verify_new_notnull &&
+		notnull_virtual_attrs == NIL &&
+		oldrel->rd_rel->relkind == RELKIND_RELATION &&
+		CheckRelationLockedByMe(oldrel, AccessExclusiveLock, false))
+	{
+		List	   *notnull_attnums = NIL;
+
+		foreach(l, tab->constraints)
+		{
+			NewConstraint *con = lfirst(l);
+
+			Form_pg_attribute attr = TupleDescAttr(newTupDesc, con->attnum - 1);
+
+			if (con->contype != CONSTR_NOTNULL)
+				continue;
+
+			Assert(attr->attnotnull);
+			Assert(con->attnum > 0);
+			Assert(attr->attnum == con->attnum);
+
+			if (attr->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+			{
+				needscan = true;
+				break;
+			}
+
+			notnull_attnums = lappend_int(notnull_attnums, attr->attnum);
+		}
+
+		/* notnull_attnums is all not-null attributes we validate later */
+		if (notnull_attnums != NIL)
+		{
+			Assert(CheckRelationLockedByMe(oldrel, AccessExclusiveLock, false));
+
+			if (!index_check_notnull(oldrel, notnull_attnums))
+				needscan = true;
+			else
+				ereport(DEBUG1,
+						errmsg_internal("all not-null constraints on relation \"%s\" are validated by index scan",
+										RelationGetRelationName(oldrel)));
+		}
+	}
+	else if (notnull_attrs || notnull_virtual_attrs)
+		needscan = true;
+
 	if (newrel || needscan)
 	{
 		ExprContext *econtext;
@@ -7910,6 +7976,7 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
 	CookedConstraint *ccon;
 	List	   *cooked;
 	bool		is_no_inherit = false;
+	AlteredTableInfo *tab = NULL;
 
 	/* Guard against stack overflow due to overly deep inheritance tree. */
 	check_stack_depth();
@@ -8034,10 +8101,31 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
 	constraint->is_no_inherit = is_no_inherit;
 	constraint->conname = conName;
 
+	 * we may use indexscan to fast validate not-null constraint in Phase3.  Add
+	 * the to-be-validated not-null constraint to Phase 3's queue.
 
 	InvokeObjectPostAlterHook(RelationRelationId,
@@ -9918,13 +10006,15 @@ ATAddCheckNNConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
 	{
 		CookedConstraint *ccon = (CookedConstraint *) lfirst(lcon);

 
 			tab->constraints = lappend(tab->constraints, newcon);
@@ -13179,6 +13269,7 @@ QueueNNConstraintValidation(List **wqueue, Relation conrel, Relation rel,
 	List	   *children = NIL;
 	AttrNumber	attnum;
 	char	   *colname;
+	NewConstraint *newcon;
 
@@ -13242,7 +13333,17 @@ QueueNNConstraintValidation(List **wqueue, Relation conrel, Relation rel,

 	/*
diff --git a/src/backend/executor/execIndexing.c b/src/backend/executor/execIndexing.c
index bdf862b2406..648c696725b 100644
--- a/src/backend/executor/execIndexing.c
+++ b/src/backend/executor/execIndexing.c
@@ -108,12 +108,15 @@

@@ -1172,3 +1176,194 @@ ExecWithoutOverlapsNotEmpty(Relation rel, NameData attname, Datum attval, char t
+ * First check if relation have suitable index for each not null attribute. If
+ * it does, then using indexscan mechanism to verify that *all* attributes on
+	Relation	pg_index;
+	pg_index = table_open(IndexRelationId, AccessShareLock);
+	indscan = systable_beginscan(pg_index, IndexIndrelidIndexId, true,
+		 * we can only use non-deferred, valid, and alive btree index to
+		 * validate not-null constraints
+		/* can not use expression index or partial index too */
+				if (index_rel->rd_rel->relam == BTREE_AM_OID &&
+	table_close(pg_index, AccessShareLock);
+	 * every not-null constraints require an index for fast validatation.
 
+extern bool index_check_notnull(Relation relation, List *notnull_attnums);
+
 /*
  * prototypes from functions in execReplication.c
  */
diff --git a/src/test/isolation/expected/indexscan-check-notnull.out b/src/test/isolation/expected/indexscan-check-notnull.out
new file mode 100644
index 00000000000..ac3b9f14c89
--- /dev/null
+++ b/src/test/isolation/expected/indexscan-check-notnull.out
@@ -0,0 +1,102 @@
+Parsed test spec with 2 sessions
+
+starting permutation: b1 b3 m1 hj c1 c3 sn
+step b1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step b3: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step m1: DELETE FROM t;
+step hj: ALTER TABLE t ADD CONSTRAINT t1_nn NOT NULL a; <waiting ...>
+step c1: COMMIT;
+s2: NOTICE:  all not-null constraints on relation "t" are validated by index scan
+step hj: <... completed>
+step c3: COMMIT;
+step sn: SELECT conname, conrelid::regclass, contype, convalidated
+           FROM pg_constraint WHERE conname = 't1_nn';
+
+conname|conrelid|contype|convalidated
+-------+--------+-------+------------
+t1_nn  |t       |n      |t           
+(1 row)
+
+
+starting permutation: b2 b3 m1 hj c1 c3 sn
+step b2: BEGIN;
+step b3: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step m1: DELETE FROM t;
+step hj: ALTER TABLE t ADD CONSTRAINT t1_nn NOT NULL a; <waiting ...>
+step c1: COMMIT;
+s2: NOTICE:  all not-null constraints on relation "t" are validated by index scan
+step hj: <... completed>
+step c3: COMMIT;
+step sn: SELECT conname, conrelid::regclass, contype, convalidated
+           FROM pg_constraint WHERE conname = 't1_nn';
+
+conname|conrelid|contype|convalidated
+-------+--------+-------+------------
+t1_nn  |t       |n      |t           
+(1 row)
+
+
+starting permutation: b1 b4 m1 hj c1 c3 sn
+step b1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step b4: BEGIN;
+step m1: DELETE FROM t;
+step hj: ALTER TABLE t ADD CONSTRAINT t1_nn NOT NULL a; <waiting ...>
+step c1: COMMIT;
+s2: NOTICE:  all not-null constraints on relation "t" are validated by index scan
+step hj: <... completed>
+step c3: COMMIT;
+step sn: SELECT conname, conrelid::regclass, contype, convalidated
+           FROM pg_constraint WHERE conname = 't1_nn';
+
+conname|conrelid|contype|convalidated
+-------+--------+-------+------------
+t1_nn  |t       |n      |t           
+(1 row)
+
+
+starting permutation: b1 b3 hj r1 c2
+step b1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step b3: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step hj: ALTER TABLE t ADD CONSTRAINT t1_nn NOT NULL a;
+ERROR:  column "a" of relation "t" contains null values
+step r1: ROLLBACK;
+step c2: ROLLBACK;
+
+starting permutation: b1 b4 d1 m1 c2 s1 sn r1
+step b1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step b4: BEGIN;
+step d1: DROP INDEX t_ab_idx;
+step m1: DELETE FROM t; <waiting ...>
+step c2: ROLLBACK;
+step m1: <... completed>
+s1: NOTICE:  all not-null constraints on relation "t" are validated by index scan
+step s1: ALTER TABLE t ADD CONSTRAINT t1_nn NOT NULL a;
+step sn: SELECT conname, conrelid::regclass, contype, convalidated
+           FROM pg_constraint WHERE conname = 't1_nn';
+
+conname|conrelid|contype|convalidated
+-------+--------+-------+------------
+t1_nn  |t       |n      |t           
+(1 row)
+
+step r1: ROLLBACK;
+
+starting permutation: b2 b3 m1 d1 s0 s1 c1 c3 sn
+step b2: BEGIN;
+step b3: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step m1: DELETE FROM t;
+step d1: DROP INDEX t_ab_idx; <waiting ...>
+step s0: savepoint s0;
+s1: NOTICE:  all not-null constraints on relation "t" are validated by index scan
+step s1: ALTER TABLE t ADD CONSTRAINT t1_nn NOT NULL a;
+step c1: COMMIT;
+step d1: <... completed>
+step c3: COMMIT;
+step sn: SELECT conname, conrelid::regclass, contype, convalidated
+           FROM pg_constraint WHERE conname = 't1_nn';
+
+conname|conrelid|contype|convalidated
+-------+--------+-------+------------
+t1_nn  |t       |n      |t           
+(1 row)
+
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index e3c669a29c7..1b9e8f932ac 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -56,6 +56,7 @@ test: merge-delete
 test: merge-update
 test: merge-match-recheck
 test: merge-join
+test: indexscan-check-notnull
 test: delete-abort-savept
 test: delete-abort-savept-2
 test: aborted-keyrevoke
diff --git a/src/test/isolation/specs/indexscan-check-notnull.spec b/src/test/isolation/specs/indexscan-check-notnull.spec
new file mode 100644
index 00000000000..d0ca928f3af
--- /dev/null
+++ b/src/test/isolation/specs/indexscan-check-notnull.spec
@@ -0,0 +1,45 @@
+
+#
+# using indexscan to check a column not-null constraint
+# is satisfied or not.
+#
+
+setup
+{
+  CREATE TABLE t (a int, b int);
+  CREATE INDEX t_ab_idx on t(a,b);
+  INSERT INTO t values (null, 1);
+  INSERT INTO t SELECT x, x*10 FROM generate_series(1,3) g(x);
+}
+
+teardown
+{
+  DROP TABLE t;
+}
+
+session s1
+step b1  { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step b2  { BEGIN; }
+step m1  { DELETE FROM t;}
+step s0  { savepoint s0;}
+step s1  { ALTER TABLE t ADD CONSTRAINT t1_nn NOT NULL a;}
+step sn  { SELECT conname, conrelid::regclass, contype, convalidated
+           FROM pg_constraint WHERE conname = 't1_nn';
+         }
+step r1  { ROLLBACK; }
+step c1  { COMMIT; }
+
+session s2
+step b3  { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step b4  { BEGIN; }
+step hj  { ALTER TABLE t ADD CONSTRAINT t1_nn NOT NULL a;}
+step d1  { DROP INDEX t_ab_idx;}
+step c2  { ROLLBACK; }
+step c3  { COMMIT; }
+
+permutation b1 b3 m1 hj c1 c3 sn
+permutation b2 b3 m1 hj c1 c3 sn
+permutation b1 b4 m1 hj c1 c3 sn
+permutation b1 b3 hj r1 c2
+permutation b1 b4 d1 m1 c2 s1 sn r1
+permutation b2 b3 m1 d1 s0 s1 c1 c3 sn
diff --git a/src/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build
index 9c50de7efb0..49fa16fb50e 100644
--- a/src/test/modules/test_misc/meson.build
+++ b/src/test/modules/test_misc/meson.build
@@ -16,6 +16,7 @@ tests += {
       't/005_timeouts.pl',
       't/006_signal_autovacuum.pl',
       't/007_catcache_inval.pl',
+      't/008_indexscan_validate_notnull.pl',
     ],
   },
 }
diff --git a/src/test/modules/test_misc/t/008_indexscan_validate_notnull.pl b/src/test/modules/test_misc/t/008_indexscan_validate_notnull.pl
new file mode 100644
index 00000000000..37787858ea0
--- /dev/null
+++ b/src/test/modules/test_misc/t/008_indexscan_validate_notnull.pl
@@ -0,0 +1,138 @@
+
+# Copyright (c) 2025, PostgreSQL Global Development Group
+
+# Verify that indexscan mechanism can speedup ALTER TABLE ADD NOT NULL
+
+
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Initialize a test cluster
+my $node = PostgreSQL::Test::Cluster->new('primary');
+$node->init();
+# Turn message level up to DEBUG1 so that we get the messages we want to see
+$node->append_conf('postgresql.conf', 'client_min_messages = DEBUG1');
+$node->start;
+
+# Run a SQL command and return psql's stderr (including debug messages)
+sub run_sql_command
+{
+	my $sql = shift;
+	my $stderr;
+
+	$node->psql(
+		'postgres',
+		$sql,
+		stderr => \$stderr,
+		on_error_die => 1,
+		on_error_stop => 1);
+	return $stderr;
+}
+
+# Check whether result of run_sql_command shows that we did a verify pass
+sub is_table_verified
+{
+	my $output = shift;
+	return index($output, 'DEBUG:  verifying table') != -1;
+}
+
+# Check whether result of run_sql_command shows that we did a verify pass
+sub is_indexscan_veritify_notnull
+{
+	my $output = shift;
+	return index($output, 'DEBUG:  all not-null constraints on relation') != -1;
+}
+
+my $output;
+
+note "test alter table set not null using indexscan with partitioned table";
+
+run_sql_command(
+    'CREATE TABLE tp (a int, b int ) PARTITION BY range(a);
+     CREATE TABLE tpp1 partition of tp for values from ( 1 ) to (10);
+     CREATE TABLE tpp2 partition of tp for values from ( 10 ) to (21);
+     INSERT INTO tp select g, g + 10 from generate_series(1,19) g;
+     CREATE INDEX ON tp(b); ');
+
+$output = run_sql_command('ALTER TABLE tp alter column b set not null;');
+ok(is_indexscan_veritify_notnull($output),
+	'column b will use indexscan to vertify not-null status, no need scan table');
+ok( $output =~
+	  m/all not-null constraints on relation "tpp1" are validated by index scan/,
+	'all newly added constraints proved by indexscan');
+
+run_sql_command(
+	'ALTER TABLE tp alter column b drop not null;');
+
+# normal run will verify table data
+$output = run_sql_command('ALTER TABLE tpp1 alter column b set not null;');
+ok(is_indexscan_veritify_notnull($output), 'use indexscan to validate column b not-null will not scan table');
+ok(!is_table_verified($output), 'will not scan table');
+ok( $output =~
+	  m/all not-null constraints on relation "tpp1" are validated by index scan/,
+	'all newly added constraints proved by indexscan');
+
+
+$output = run_sql_command('ALTER TABLE tpp2 alter column b set not null;');
+ok(is_indexscan_veritify_notnull($output), 'use indexscan to validate column b not-null, will not scan table');
+ok(!is_table_verified($output), 'will not scan table');
+ok( $output =~
+	  m/all not-null constraints on relation "tpp2" are validated by index scan/,
+	'all newly added constraints proved by indexscan');
+
+run_sql_command(
+	'ALTER TABLE tp alter column b drop not null;');
+
+# we have check only for test_a column, so we need verify table for test_b
+$output = run_sql_command(
+	'ALTER TABLE tp alter column b set not null, alter column b SET data type bigint;'
+);
+ok(!is_table_verified($output), 'table was rewrite');
+ok(!is_indexscan_veritify_notnull($output), 'table was rewrite, can not use indexscan to vertify not null constraints');
+
+
+# test with non-partitioned table and other type index.
+note "test alter table set not null using indexscan with non-partitioned table";
+
+run_sql_command(
+    'CREATE TABLE t1(f1 INT, f2 int, f3 int,f4 int, f5 int);
+     INSERT INTO t1 select g, g+1, g+2, g+3, g+4 from generate_series(1, 100) g;
+     CREATE INDEX t1_f1_f2_idx ON t1(f1,f2);
+     CREATE UNIQUE INDEX t1_f3idx ON t1(f3);
+     CREATE INDEX t1_f3f4idx ON t1(f3) include(f4);
+     CREATE INDEX hash_f5_idx ON t1 USING hash (f5 int4_ops); ');
+
+$output = run_sql_command(
+	'ALTER TABLE t1 alter column f1 set not null, alter f1 type bigint;'
+);
+ok(!is_indexscan_veritify_notnull($output), 'table was rewrite, can not use indexscan to validate not null constraints');
+
+run_sql_command('ALTER TABLE t1 alter column f1 drop not null;');
+run_sql_command('ALTER TABLE t1 add constraint nn not null f1, alter f1 type bigint;');
+
+# can use indexscan mechanism to fast add multiple not-null constraints
+$output = run_sql_command(
+	'ALTER TABLE t1 alter column f2 set not null, alter column f1 set not null;'
+);
+ok( $output =~
+	  m/all not-null constraints on relation "t1" are validated by index scan/,
+	'all not-null constraints are validated by indexscan');
+
+# using indexscan mechanism to fast add a not-null can only apply to key columns, not include column
+$output = run_sql_command(
+	'ALTER TABLE t1 add constraint nnf4 not null f4;'
+);
+ok(!is_indexscan_veritify_notnull($output), 'can not use indexscan to valildate not-null constraint');
+
+# if index is not btree index then cannot use indexscan mechanism to fast add not-null constraint
+$output = run_sql_command(
+	'ALTER TABLE t1 add constraint nn_f5 not null f5;'
+);
+ok(!is_indexscan_veritify_notnull($output), 'can not use indexscan to validate not-null constraint');
+
+$node->stop('fast');
+
+done_testing();
-- 
2.34.1

---

From: Álvaro Herrera <[email-masked]>
Date: Mon, 28 Apr 2025 10:40:53 +0200

> src/test/modules/test_misc/t/008_indexscan_validate_notnull.pl

Seems reasonable, didn't look at it in detail.  I think you don't have
any tests where you try to set multiple columns as NOT NULL in a single
ALTER TABLE command; I think this is worth having.  Something like

CREATE TABLE foo (col1 int, col2 int, col3 int);
... create indexes on col1 and col2 ...
alter table foo set col1 not null,
                set col3 not null,
		add constraint bla not null b;
and stuff like that.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"If you have nothing to say, maybe you need just the right tool to help you
not say it."                   (New York Times, about Microsoft PowerPoint)

---

From: Andres Freund <[email-masked]>
Date: Tue, 13 May 2025 09:37:30 -0400

Hi,

> src/test/modules/test_misc/t/008_indexscan_validate_notnull.pl

The tests have not passed in a few weeks:
https://cirrus-ci.com/github/postgresql-cfbot/postgresql/cf%2F5444

Greetings,

Andres Freund

---

From: jian he <[email-masked]>
Date: Wed, 14 May 2025 17:16:14 +0800

>                 add constraint bla not null b;

newly added tests covered that.
now the test coverage is quite good, IMHO.

also rebased to address test failure.