On Fri, Apr 18, 2025 at 4:07 PM jian he <jian.universal...@gmail.com> wrote:
>
> 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 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 <jian.universal...@gmail.com>
Date: Mon, 28 Apr 2025 12:27:13 +0800
Subject: [PATCH v5 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:
demo:
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, vacuum_index_cleanup=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 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=r-cx1rubyxqpurwb331u47rsngz...@mail.gmail.com
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
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 2705cf11330..a784dbfbeac 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 attribute number */
Node *qual; /* Check expr or CONSTR_FOREIGN Constraint */
ExprState *qualstate; /* Execution state for CHECK expr */
} NewConstraint;
@@ -6182,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;
@@ -6235,10 +6241,70 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
wholeatt->attnum);
}
}
- if (notnull_attrs || notnull_virtual_attrs)
- needscan = true;
}
+ /*
+ * 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;
+ 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 validate not-null constraint in Phase3. Add
+ * the to-be-validated not-null constraint to Phase 3's queue.
+ */
+ 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,
@@ -9918,13 +10006,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);
@@ -13179,6 +13269,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);
@@ -13242,7 +13333,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..648c696725b 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,194 @@ 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 not null attribute. If
+ * it does, then 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 pg_index;
+ 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)));
+
+ pg_index = table_open(IndexRelationId, AccessShareLock);
+ indscan = systable_beginscan(pg_index, 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
+ * validate not-null constraints
+ */
+ if (!index->indimmediate || !index->indisvalid || !index->indislive)
+ continue;
+
+ /* can not use expression index or partial 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->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(pg_index, AccessShareLock);
+
+ if (attnums == NIL)
+ return false;
+
+ /*
+ * every not-null constraints require an index for fast validatation.
+ */
+ 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/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
