On Wed, Feb 5, 2025 at 4:24 PM jian he <jian.universal...@gmail.com> 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 <jian.universal...@gmail.com>
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=r-cx1rubyxqpurwb331u47rsngz...@mail.gmail.com
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
