public inbox for [email protected]
help / color / mirror / Atom feedRe: using index to speedup add not null constraints to a table
8+ messages / 3 participants
[nested] [flat]
* Re: using index to speedup add not null constraints to a table
@ 2025-05-13 13:37 Andres Freund <[email protected]>
1 sibling, 0 replies; 8+ messages in thread
From: Andres Freund @ 2025-05-13 13:37 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
Hi,
On 2025-04-28 12:36:14 +0800, jian he wrote:
> On Fri, Apr 18, 2025 at 4:07 PM jian he <[email protected]> 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.
The tests have not passed in a few weeks:
https://cirrus-ci.com/github/postgresql-cfbot/postgresql/cf%2F5444
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: using index to speedup add not null constraints to a table
@ 2025-05-14 09:16 jian he <[email protected]>
1 sibling, 1 reply; 8+ messages in thread
From: jian he @ 2025-05-14 09:16 UTC (permalink / raw)
To: Álvaro Herrera <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On Mon, Apr 28, 2025 at 4:40 PM Álvaro Herrera <[email protected]> wrote:
>
> On 2025-Apr-28, jian he wrote:
>
> > 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.
>
> 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.
>
newly added tests covered that.
now the test coverage is quite good, IMHO.
also rebased to address test failure.
Attachments:
[application/x-patch] v6-0001-using-indexscan-to-speedup-add-not-null-constrain.patch (29.3K, 2-v6-0001-using-indexscan-to-speedup-add-not-null-constrain.patch)
download | inline diff:
From 8c9a668993386c67a3e6ab14e02c87a3184be83b Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Wed, 14 May 2025 17:14:36 +0800
Subject: [PATCH v6 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 happens in phase3 ATRewriteTable
1. collect all not-null constraints.
2. For each not-null constraint, check whether there is a corresponding index
available for validation. If not, then can not use indexscan to verify not-null
constraints.
3. If any of the following conditions are true
* table scan or rewrite is required,
* table wasn't locked with `AccessExclusiveLock`
* the NOT NULL constraint applies to a virtual generated column
then index scan cannot be used for fast validation.
4. If all conditions are satisfied, attempt to use indexscan to verify
whether the column contains any NULL values.
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 several 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+hw@mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/5444
---
src/backend/commands/tablecmds.c | 125 ++++++++++-
src/backend/executor/execIndexing.c | 200 ++++++++++++++++++
src/include/executor/executor.h | 2 +
.../expected/indexscan-check-notnull.out | 97 +++++++++
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 | 164 ++++++++++++++
8 files changed, 630 insertions(+), 5 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 54ad38247aa..c276450aceb 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,9 +6241,81 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
wholeatt->attnum);
}
}
- if (notnull_attrs || notnull_virtual_attrs)
+ }
+
+ /*
+ * The conditions for using indexscan mechanism fast vertifing not-null
+ * constraints are quite strict. All of the following conditions must be
+ * met.
+ * 1. We must have the need to validate not-null constraints
+ * That means AlteredTableInfo->verify_new_notnull must be true.
+ * 2. If 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. Only apply to regular relations.
+ * 5. To avoid concurrency issue, we only do it when table was locked with
+ * an AccessExclusiveLock, which is the lock acquired during ALTER TABLE
+ * SET NOT NULL.
+ */
+ 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 contains all attributes with NOT NULL constraints
+ * that need validation. If needscan is true, later table scan
+ * will do the validation, indedxscan would just waste cycle.
+ */
+ if (notnull_attnums != NIL && !needscan)
+ {
+ if (!index_check_notnull(oldrel, notnull_attnums))
+ needscan = true;
+ else
+ ereport(DEBUG1,
+ errmsg_internal("all new not-null constraints on relation \"%s\" have been validated by using index scan",
+ RelationGetRelationName(oldrel)));
+ }
+ else
+ {
+ /*
+ * corner case: in Phase2, somehow we didn't add the NOT NULL
+ * constraint to AlteredTableInfo->constraints, but already set
+ * verify_new_notnull to true then table scan is needed.
+ */
needscan = true;
+ }
}
+ else if (notnull_attrs || notnull_virtual_attrs)
+ needscan = true;
if (newrel || needscan)
{
@@ -7910,6 +7988,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 +8113,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 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 +10018,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);
@@ -13166,6 +13268,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);
@@ -13229,7 +13332,19 @@ 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. ALTER TABLE SET NOT NULL will add NOT NULL
+ * constraint to AlteredTableInfo->constraints, for consistency, do the same
+ * here.
+ */
+ 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..d1648e2a964 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: list of attribute numbers of all new not-null constraints.
+ *
+ * First check if the relation has a 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.
+ *
+ * Returning true means by using indexscan mechanism, all new not-null
+ * constraints have been validated.
+ */
+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, validated, 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;
+
+ /* can only apply to the btree index. also collect index attnums */
+ 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 constraint requires an index for indexscan. If no such
+ * index exists, we need to use table scan to validate 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 verify 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..2255335f00f
--- /dev/null
+++ b/src/test/isolation/expected/indexscan-check-notnull.out
@@ -0,0 +1,97 @@
+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;
+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;
+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;
+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>
+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;
+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..5f787cdd070
--- /dev/null
+++ b/src/test/modules/test_misc/t/008_indexscan_validate_notnull.pl
@@ -0,0 +1,164 @@
+
+# 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 new 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, c int, d 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, g + 15, g + random(min=>1::int, max=>10::int) 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 verify not-null status, no need scan table');
+ok( $output =~
+ m/all new not-null constraints on relation "tpp1" have been validated by using index scan/,
+ 'all newly added constraints proved by indexscan');
+
+run_sql_command(
+ 'ALTER TABLE tp ALTER COLUMN b DROP NOT NULL;');
+
+# use indexes on partitions to fast vertifing not-null should be ok
+$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), 'no table scan for pp1');
+ok( $output =~
+ m/all new not-null constraints on relation "tpp1" have been validated by using 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), 'no table scan for tpp2');
+ok( $output =~
+ m/all new not-null constraints on relation "tpp2" have been validated by using index scan/,
+ 'all newly added constraints proved by indexscan');
+
+run_sql_command(
+ 'ALTER TABLE tp ALTER COLUMN b DROP NOT NULL;');
+
+# table rewrite then can not use indexscan to verify not-null constraint
+$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), 'no table scan for tp');
+ok(!is_indexscan_veritify_notnull($output), 'table tp will rewrite, can not use indexscan to verify not-null constraints on it');
+
+# test using indexscan with multiple not-null constraints
+run_sql_command(
+ 'ALTER TABLE tp ALTER COLUMN b DROP NOT NULL;
+ CREATE INDEX ON tp(c);
+ CREATE INDEX ON tp(d); ');
+
+# one column can use indexscan, another can not, so can not use indexscan to verify not-null
+$output = run_sql_command(
+ 'ALTER TABLE tp ALTER COLUMN a SET NOT NULL, ADD CONSTRAINT c_nn NOT NULL c');
+ok(is_table_verified($output), 'table scan for tp');
+ok(!is_indexscan_veritify_notnull($output), 'can not use indexscan to verify not-null constraints on table tp');
+
+# all columns have index on it
+# use indexscan to verify multiple not-null constraints should just fine
+$output = run_sql_command(
+ 'ALTER TABLE tp ALTER COLUMN d SET NOT NULL, ADD CONSTRAINT c_nn NOT NULL c, ALTER COLUMN b SET NOT NULL;');
+ok(!is_table_verified($output), 'no table scan for tp');
+ok(is_indexscan_veritify_notnull($output),
+ 'using indexscan to verify not-null constraints on column b, c, d, no need table scan');
+
+# test with non-partitioned table and other non-Btree index.
+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); ');
+
+# ALTER TABLE VALIDATE CONSTRAINT can not use indexscan,
+# because it only hold ShareUpdateExclusiveLock lock.
+run_sql_command('ALTER TABLE t1 ADD CONSTRAINT nn_t1_f2 NOT NULL f2 NOT VALID;');
+
+$output = run_sql_command(
+ 'ALTER TABLE t1 VALIDATE CONSTRAINT nn_t1_f2;'
+);
+ok(!is_indexscan_veritify_notnull($output), 'can not use indexscan for ALTER TABLE VALIDATE CONSTRAINT command');
+run_sql_command('ALTER TABLE t1 DROP CONSTRAINT nn_t1_f2;');
+
+$output = run_sql_command(
+ 'ALTER TABLE t1 ALTER COLUMN f1 SET NOT NULL, ALTER f1 type bigint;'
+);
+ok(!is_indexscan_veritify_notnull($output), 'table t1 will rewrite, can not use indexscan to validate not-null constraints on t1');
+
+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 new not-null constraints on relation "t1" have been validated by using 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 validate not-null constraint on t1');
+
+# 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 constraints on t1');
+
+$node->stop('fast');
+
+done_testing();
--
2.34.1
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: using index to speedup add not null constraints to a table
@ 2025-10-16 19:57 Álvaro Herrera <[email protected]>
parent: jian he <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Álvaro Herrera @ 2025-10-16 19:57 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
Can you please rebase this? It stopped applying a week ago.
Thanks!
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"Sallah, I said NO camels! That's FIVE camels; can't you count?"
(Indiana Jones)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: using index to speedup add not null constraints to a table
@ 2025-10-20 06:26 jian he <[email protected]>
parent: Álvaro Herrera <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: jian he @ 2025-10-20 06:26 UTC (permalink / raw)
To: Álvaro Herrera <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On Fri, Oct 17, 2025 at 3:57 AM Álvaro Herrera <[email protected]> wrote:
> Can you please rebase this? It stopped applying a week ago.
>
hi.
rebase and minor polishment.
Attachments:
[text/x-patch] v7-0001-using-indexscan-to-speedup-add-not-null-constraints.patch (29.8K, 2-v7-0001-using-indexscan-to-speedup-add-not-null-constraints.patch)
download | inline diff:
From 5a404c03556897b655359adef8057acd35246a1e Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 20 Oct 2025 13:29:32 +0800
Subject: [PATCH v7 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 happens in phase3 ATRewriteTable
1. collect all not-null constraints.
2. For each not-null constraint, check whether there is a corresponding index
available for validation. If not, then can not use indexscan to verify not-null
constraints.
3. If any of the following conditions are true
* table scan or rewrite is required,
* table wasn't locked with `AccessExclusiveLock`
* the NOT NULL constraint applies to a virtual generated column
then index scan cannot be used for fast validation.
4. If all conditions are satisfied, attempt to use indexscan to verify
whether the column contains any NULL values.
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 several 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+hw@mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/5444
---
src/backend/commands/tablecmds.c | 120 +++++++++-
src/backend/executor/execIndexing.c | 213 ++++++++++++++++++
src/include/executor/executor.h | 2 +
.../expected/indexscan-check-notnull.out | 97 ++++++++
src/test/isolation/isolation_schedule | 1 +
.../specs/indexscan-check-notnull.spec | 45 ++++
src/test/modules/test_misc/meson.build | 1 +
.../t/010_indexscan_validate_notnull.pl | 163 ++++++++++++++
8 files changed, 636 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/010_indexscan_validate_notnull.pl
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 5fd8b51312c..e686619d775 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -210,16 +210,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;
@@ -6184,6 +6187,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;
@@ -6237,10 +6243,75 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
wholeatt->attnum);
}
}
- if (notnull_attrs || notnull_virtual_attrs)
- needscan = true;
}
+ /*
+ * The conditions for using indexscan mechanism fast vertifing not-null
+ * constraints are quite strict. All of the following conditions must be
+ * met.
+ * 1. AlteredTableInfo->verify_new_notnull is true.
+ * 2. If table scan (for other reason, like check constraint verification)
+ * 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. This applies to plain relation only.
+ * 5. To prevent possible concurrency issues, the table must already be
+ * locked with an AccessExclusiveLock, which is the lock obtained during
+ * ALTER TABLE SET NOT NULL.
+ */
+ if (!needscan &&
+ newrel == NULL &&
+ tab->verify_new_notnull &&
+ notnull_virtual_attrs == NIL &&
+ oldrel->rd_rel->relkind == RELKIND_RELATION &&
+ CheckRelationLockedByMe(oldrel, AccessExclusiveLock, false))
+ {
+ List *notnull_attnums = NIL;
+
+ Assert(!tab->rewrite);
+
+ foreach(l, tab->constraints)
+ {
+ Form_pg_attribute attr;
+ NewConstraint *con = lfirst(l);
+
+ if (con->contype != CONSTR_NOTNULL)
+ continue;
+
+ attr = TupleDescAttr(newTupDesc, con->attnum - 1);
+
+ Assert(attr->attnotnull);
+ Assert(attr->attnum == con->attnum);
+
+ if (attr->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ {
+ needscan = true;
+ break;
+ }
+
+ notnull_attnums = lappend_int(notnull_attnums, attr->attnum);
+ }
+
+ /*
+ * notnull_attnums contains all attributes with NOT NULL constraints
+ * that needs to be validation.
+ * If needscan is really needed, later table scan will do the
+ * validation, indedxscan would just waste cycle.
+ */
+ if (notnull_attnums != NIL && !needscan)
+ {
+ if (!index_check_notnull(oldrel, notnull_attnums))
+ needscan = true;
+ else
+ ereport(DEBUG1,
+ errmsg_internal("all new not-null constraints on relation \"%s\" have been validated by using index scan",
+ RelationGetRelationName(oldrel)));
+ }
+ }
+ else if (notnull_attrs || notnull_virtual_attrs)
+ needscan = true;
+
if (newrel || needscan)
{
ExprContext *econtext;
@@ -7912,6 +7983,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();
@@ -8036,10 +8108,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 vertify 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,
@@ -9920,13 +10013,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);
@@ -13195,6 +13290,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);
@@ -13258,7 +13354,19 @@ 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.
+ * ALTER TABLE SET NOT NULL will add NOT NULL constraint to
+ * AlteredTableInfo->constraints, for consistency, do the same here.
+ */
+ 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 ca33a854278..7cb539a7bcf 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,212 @@ 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: list of attribute numbers of all new not-null constraints.
+ *
+ * First check whether the relation has a suitable index for each not-null
+ * attribute, then using indexscan mechanism to verify that *all* attributes on
+ * notnull_attnums are indeed not null.
+ *
+ * Returning true means all new not-null constraints have been verified, no
+ * need an extra scan to verify newly added not-null constraints.
+ */
+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 *idxs = 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, validated, 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;
+
+ index_rel = index_open(index->indexrelid, AccessShareLock);
+
+ /* can only apply to btree index */
+ if (index_rel->rd_rel->relam != BTREE_AM_OID)
+ {
+ index_close(index_rel, AccessShareLock);
+ continue;
+ }
+
+ /* collect index attnums while loop */
+ for (int i = 0; i < index->indnkeyatts; i++)
+ {
+ attr = TupleDescAttr(tupdesc, (index->indkey.values[i] - 1));
+
+ if (attr->attisdropped)
+ continue;
+
+ if (list_member_int(notnull_attnums, attr->attnum) &&
+ !list_member_int(attnums, attr->attnum))
+ {
+ attnums = lappend_int(attnums, attr->attnum);
+ idxs = lappend_oid(idxs, index->indexrelid);
+ }
+ }
+ index_close(index_rel, NoLock);
+ }
+ systable_endscan(indscan);
+ table_close(pg_index, AccessShareLock);
+
+ if (attnums == NIL)
+ return false;
+
+ /*
+ * every not-null constraint requires an index for indexscan. If no such
+ * index exists, we need to use regular table scan to validate not-null
+ * constraints.
+ */
+ foreach_int(attno, notnull_attnums)
+ {
+ if(!list_member_int(attnums, attno))
+ return false;
+ }
+
+ return index_check_notnull_internal(relation, attnums, idxs);
+}
+
+/*
+ * Use indexscan mechanism to fast verify 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, NoLock);
+
+ 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, "cache lookup failed for attribute number %d on index %u", 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, NoLock);
+ ExecDropSingleTupleTableSlot(existing_slot);
+
+ /* exit earlier */
+ if (!all_not_null)
+ {
+ FreeExecutorState(estate);
+ return false;
+ }
+ }
+
+ FreeExecutorState(estate);
+
+ return true;
+}
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index 3248e78cd28..20dad31f73c 100644
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -753,6 +753,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..c6a652e2518
--- /dev/null
+++ b/src/test/isolation/expected/indexscan-check-notnull.out
@@ -0,0 +1,97 @@
+Parsed test spec with 2 sessions
+
+starting permutation: s1_b1 s2_b1 s1_d1 s2_a1 s1_c1 s2_c1 s1_q1
+step s1_b1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2_b1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s1_d1: DELETE FROM t;
+step s2_a1: ALTER TABLE t ADD CONSTRAINT t1_nn NOT NULL a; <waiting ...>
+step s1_c1: COMMIT;
+step s2_a1: <... completed>
+step s2_c1: COMMIT;
+step s1_q1: 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: s1_b2 s2_b1 s1_d1 s2_a1 s1_c1 s2_c1 s1_q1
+step s1_b2: BEGIN;
+step s2_b1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s1_d1: DELETE FROM t;
+step s2_a1: ALTER TABLE t ADD CONSTRAINT t1_nn NOT NULL a; <waiting ...>
+step s1_c1: COMMIT;
+step s2_a1: <... completed>
+step s2_c1: COMMIT;
+step s1_q1: 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: s1_b1 s2_b2 s1_d1 s2_a1 s1_c1 s2_c1 s1_q1
+step s1_b1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2_b2: BEGIN;
+step s1_d1: DELETE FROM t;
+step s2_a1: ALTER TABLE t ADD CONSTRAINT t1_nn NOT NULL a; <waiting ...>
+step s1_c1: COMMIT;
+step s2_a1: <... completed>
+step s2_c1: COMMIT;
+step s1_q1: 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: s1_b1 s2_b1 s2_a1 s1_r1 s2_r1
+step s1_b1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2_b1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2_a1: ALTER TABLE t ADD CONSTRAINT t1_nn NOT NULL a;
+ERROR: column "a" of relation "t" contains null values
+step s1_r1: ROLLBACK;
+step s2_r1: ROLLBACK;
+
+starting permutation: s1_b1 s2_b2 s2_d1 s1_d1 s2_r1 s1_a1 s1_q1 s1_r1
+step s1_b1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2_b2: BEGIN;
+step s2_d1: DROP INDEX t_ab_idx;
+step s1_d1: DELETE FROM t; <waiting ...>
+step s2_r1: ROLLBACK;
+step s1_d1: <... completed>
+step s1_a1: ALTER TABLE t ADD CONSTRAINT t1_nn NOT NULL a;
+step s1_q1: 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 s1_r1: ROLLBACK;
+
+starting permutation: s1_b2 s2_b1 s1_d1 s2_d1 s1_s0 s1_a1 s1_c1 s2_c1 s1_q1
+step s1_b2: BEGIN;
+step s2_b1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s1_d1: DELETE FROM t;
+step s2_d1: DROP INDEX t_ab_idx; <waiting ...>
+step s1_s0: SAVEPOINT s0;
+step s1_a1: ALTER TABLE t ADD CONSTRAINT t1_nn NOT NULL a;
+step s1_c1: COMMIT;
+step s2_d1: <... completed>
+step s2_c1: COMMIT;
+step s1_q1: 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 5afae33d370..a5926dc81d7 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -59,6 +59,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..05b4488550c
--- /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 s1_b1 { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step s1_b2 { BEGIN; }
+step s1_d1 { DELETE FROM t;}
+step s1_s0 { SAVEPOINT s0;}
+step s1_a1 { ALTER TABLE t ADD CONSTRAINT t1_nn NOT NULL a;}
+step s1_q1 { SELECT conname, conrelid::regclass, contype, convalidated
+ FROM pg_constraint WHERE conname = 't1_nn';
+ }
+step s1_r1 { ROLLBACK; }
+step s1_c1 { COMMIT; }
+
+session s2
+step s2_b1 { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step s2_b2 { BEGIN; }
+step s2_a1 { ALTER TABLE t ADD CONSTRAINT t1_nn NOT NULL a;}
+step s2_d1 { DROP INDEX t_ab_idx;}
+step s2_r1 { ROLLBACK; }
+step s2_c1 { COMMIT; }
+
+permutation s1_b1 s2_b1 s1_d1 s2_a1 s1_c1 s2_c1 s1_q1
+permutation s1_b2 s2_b1 s1_d1 s2_a1 s1_c1 s2_c1 s1_q1
+permutation s1_b1 s2_b2 s1_d1 s2_a1 s1_c1 s2_c1 s1_q1
+permutation s1_b1 s2_b1 s2_a1 s1_r1 s2_r1
+permutation s1_b1 s2_b2 s2_d1 s1_d1 s2_r1 s1_a1 s1_q1 s1_r1
+permutation s1_b2 s2_b1 s1_d1 s2_d1 s1_s0 s1_a1 s1_c1 s2_c1 s1_q1
diff --git a/src/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build
index f258bf1ccd9..cfcc7fa6a05 100644
--- a/src/test/modules/test_misc/meson.build
+++ b/src/test/modules/test_misc/meson.build
@@ -18,6 +18,7 @@ tests += {
't/007_catcache_inval.pl',
't/008_replslot_single_user.pl',
't/009_log_temp_files.pl',
+ 't/010_indexscan_validate_notnull.pl',
],
},
}
diff --git a/src/test/modules/test_misc/t/010_indexscan_validate_notnull.pl b/src/test/modules/test_misc/t/010_indexscan_validate_notnull.pl
new file mode 100644
index 00000000000..a3f3cb409b6
--- /dev/null
+++ b/src/test/modules/test_misc/t/010_indexscan_validate_notnull.pl
@@ -0,0 +1,163 @@
+# 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 new 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, c int, d 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, g + 15, g + random(min=>1::int, max=>10::int) 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 verify not-null status, no need scan table');
+ok( $output =~
+ m/all new not-null constraints on relation "tpp1" have been validated by using index scan/,
+ 'all newly added constraints proved by indexscan');
+
+run_sql_command(
+ 'ALTER TABLE tp ALTER COLUMN b DROP NOT NULL;');
+
+# use indexes on partitions to fast vertifing not-null should be ok
+$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), 'no table scan for pp1');
+ok( $output =~
+ m/all new not-null constraints on relation "tpp1" have been validated by using 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), 'no table scan for tpp2');
+ok( $output =~
+ m/all new not-null constraints on relation "tpp2" have been validated by using index scan/,
+ 'all newly added constraints proved by indexscan');
+
+run_sql_command(
+ 'ALTER TABLE tp ALTER COLUMN b DROP NOT NULL;');
+
+# table rewrite then can not use indexscan to verify not-null constraint
+$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), 'no table scan for tp');
+ok(!is_indexscan_veritify_notnull($output), 'table tp will rewrite, can not use indexscan to verify not-null constraints on it');
+
+# test using indexscan with multiple not-null constraints
+run_sql_command(
+ 'ALTER TABLE tp ALTER COLUMN b DROP NOT NULL;
+ CREATE INDEX ON tp(c);
+ CREATE INDEX ON tp(d); ');
+
+# one column can use indexscan, another can not, so can not use indexscan to verify not-null
+$output = run_sql_command(
+ 'ALTER TABLE tp ALTER COLUMN a SET NOT NULL, ADD CONSTRAINT c_nn NOT NULL c');
+ok(is_table_verified($output), 'table scan for tp');
+ok(!is_indexscan_veritify_notnull($output), 'can not use indexscan to verify not-null constraints on table tp');
+
+# all columns have index on it
+# use indexscan to verify multiple not-null constraints should just fine
+$output = run_sql_command(
+ 'ALTER TABLE tp ALTER COLUMN d SET NOT NULL, ADD CONSTRAINT c_nn NOT NULL c, ALTER COLUMN b SET NOT NULL;');
+ok(!is_table_verified($output), 'no table scan for tp');
+ok(is_indexscan_veritify_notnull($output),
+ 'using indexscan to verify not-null constraints on column b, c, d, no need table scan');
+
+# test with non-partitioned table and other non-Btree index.
+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); ');
+
+# ALTER TABLE VALIDATE CONSTRAINT can not use indexscan,
+# because it only hold ShareUpdateExclusiveLock lock.
+run_sql_command('ALTER TABLE t1 ADD CONSTRAINT nn_t1_f2 NOT NULL f2 NOT VALID;');
+
+$output = run_sql_command(
+ 'ALTER TABLE t1 VALIDATE CONSTRAINT nn_t1_f2;'
+);
+ok(!is_indexscan_veritify_notnull($output), 'can not use indexscan for ALTER TABLE VALIDATE CONSTRAINT command');
+run_sql_command('ALTER TABLE t1 DROP CONSTRAINT nn_t1_f2;');
+
+$output = run_sql_command(
+ 'ALTER TABLE t1 ALTER COLUMN f1 SET NOT NULL, ALTER f1 type bigint;'
+);
+ok(!is_indexscan_veritify_notnull($output), 'table t1 will rewrite, can not use indexscan to validate not-null constraints on t1');
+
+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 new not-null constraints on relation "t1" have been validated by using 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 validate not-null constraint on t1');
+
+# 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 constraints on t1');
+
+$node->stop('fast');
+
+done_testing();
--
2.34.1
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: using index to speedup add not null constraints to a table
@ 2026-02-06 04:58 jian he <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: jian he @ 2026-02-06 04:58 UTC (permalink / raw)
To: Álvaro Herrera <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On Wed, Feb 4, 2026 at 8:52 PM jian he <[email protected]> wrote:
>
> hi.
>
> v11 is attached.
> Mainly fixes regress test failures.
I have tested locally on https://cirrus-ci.com/build/5064590561640448
this time, the attachment should have no regress test failure.
--
jian
https://www.enterprisedb.com/
Attachments:
[text/x-patch] v12-0001-using-indexscan-to-speedup-add-not-null-constraints.patch (21.0K, 2-v12-0001-using-indexscan-to-speedup-add-not-null-constraints.patch)
download | inline diff:
From 7363acf27135b0caad04703af27e5bae75fdcbe7 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Fri, 6 Feb 2026 11:05:30 +0800
Subject: [PATCH v12 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 happens in phase3 ATRewriteTable
1. collect all not-null constraints.
2. For each not-null constraint, check whether there is a corresponding index
available for validation. If not, then cannot use indexscan mechanism to verify not-null
constraints.
3. If any of the following conditions are true
* table scan or rewrite is required,
* table wasn't locked with `AccessExclusiveLock`
* the NOT NULL constraint applies to a virtual generated column
then index scan cannot be used for fast validation.
If all conditions are satisfied, attempt to use indexscan to verify whether the
column contains any NULL values.
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]
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+hw@mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/5444
---
src/backend/commands/tablecmds.c | 285 +++++++++++++++++++++-
src/test/regress/expected/constraints.out | 65 +++++
src/test/regress/sql/constraints.sql | 48 ++++
3 files changed, 392 insertions(+), 6 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index f976c0e5c7e..49dbc690e90 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -210,16 +210,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;
@@ -745,6 +748,7 @@ static void ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation
static void ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab,
Relation rel, PartitionCmd *cmd,
AlterTableUtilityContext *context);
+static bool index_check_notnull(Relation relation, List *notnull_attnums);
/* ----------------------------------------------------------------
* DefineRelation
@@ -6217,6 +6221,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;
@@ -6270,10 +6277,78 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
wholeatt->attnum);
}
}
- if (notnull_attrs || notnull_virtual_attrs)
- needscan = true;
}
+ /*
+ * The conditions for using indexscan mechanism fast verifying not-null
+ * constraints are quite strict. All of the following conditions must be
+ * met.
+ *
+ * 1. AlteredTableInfo->verify_new_notnull is true.
+ *
+ * 2. If table scan (for other reason, like check constraint verification)
+ * 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. This applies to plain relations only.
+ *
+ * 5. To prevent possible concurrency issues, the table must already be
+ * locked with an AccessExclusiveLock, which is the lock obtained during
+ * ALTER TABLE SET NOT NULL.
+ */
+ if (!needscan &&
+ newrel == NULL &&
+ tab->verify_new_notnull &&
+ notnull_virtual_attrs == NIL &&
+ oldrel->rd_rel->relkind == RELKIND_RELATION &&
+ CheckRelationLockedByMe(oldrel, AccessExclusiveLock, false))
+ {
+ List *notnull_attnums = NIL;
+
+ Assert(!tab->rewrite);
+
+ foreach(l, tab->constraints)
+ {
+ Form_pg_attribute attr;
+ NewConstraint *con = lfirst(l);
+
+ /*
+ * This may be a FOREIGN KEY (even if not a CHECK constraint),
+ * therefore we cannot just raise error for non-NOT-NULL
+ * constraint.
+ */
+ if (con->contype != CONSTR_NOTNULL)
+ continue;
+
+ attr = TupleDescAttr(newTupDesc, con->attnum - 1);
+
+ if (attr->attisdropped)
+ continue;
+
+ Assert(attr->attnotnull);
+ Assert(attr->attnum == con->attnum);
+ Assert(attr->attgenerated != ATTRIBUTE_GENERATED_VIRTUAL);
+
+ notnull_attnums = list_append_unique_int(notnull_attnums,
+ attr->attnum);
+ }
+
+ if (notnull_attnums != NIL)
+ {
+ if (index_check_notnull(oldrel, notnull_attnums))
+ ereport(DEBUG1,
+ errmsg_internal("all new not-null constraints on relation \"%s\" have been validated by using index scan",
+ RelationGetRelationName(oldrel)));
+ else
+ needscan = true;
+ }
+ }
+ else if (notnull_attrs || notnull_virtual_attrs)
+ needscan = true;
+
if (newrel || needscan)
{
ExprContext *econtext;
@@ -7949,6 +8024,7 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
CookedConstraint *ccon;
List *cooked;
bool is_no_inherit = false;
+ AlteredTableInfo *tab = ATGetQueueEntry(wqueue, rel);;
/* Guard against stack overflow due to overly deep inheritance tree. */
check_stack_depth();
@@ -8077,6 +8153,25 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
cooked = AddRelationNewConstraints(rel, NIL, list_make1(constraint),
false, !recursing, false, NULL);
ccon = linitial(cooked);
+
+ Assert(ccon->contype == CONSTR_NOTNULL);
+
+ /*
+ * We may using indexscan mechanism to vertify this not-null constraint in
+ * Phase3. Add this to-be-validated not-null constraint to Phase 3's
+ * queue.
+ */
+ if (!ccon->skip_validation)
+ {
+ NewConstraint *newcon = palloc0_object(NewConstraint);
+
+ newcon->name = ccon->name;
+ newcon->contype = CONSTR_NOTNULL;
+ newcon->attnum = ccon->attnum;
+
+ tab->constraints = lappend(tab->constraints, newcon);
+ }
+
ObjectAddressSet(address, ConstraintRelationId, ccon->conoid);
InvokeObjectPostAlterHook(RelationRelationId,
@@ -9983,13 +10078,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 = palloc0_object(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);
@@ -13258,6 +13355,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);
@@ -13321,7 +13419,19 @@ 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. ALTER TABLE SET NOT NULL adds a NOT NULL
+ * constraint to AlteredTableInfo->constraints, for consistency, do the
+ * same here.
+ */
+ newcon = palloc0_object(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;
/*
@@ -23340,3 +23450,166 @@ ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, Relation rel,
/* Restore the userid and security context. */
SetUserIdAndSecContext(save_userid, save_sec_context);
}
+
+/*
+ * notnull_attnums: list of attribute numbers for all newly added NOT NULL
+ * constraints.
+ *
+ * For each NOT NULL attribute, first check whether the relation has a suitable
+ * index to fetch that attribute content. If so, using indexscan to verify that
+ * attribute contains no NULL values.
+ *
+ * Returning true indicates that all new NOT NULL constraints have been
+ * validated and that no additional table scan is required.
+ */
+static bool
+index_check_notnull(Relation relation, List *notnull_attnums)
+{
+ SysScanDesc indscan;
+ ScanKeyData skey;
+ HeapTuple htup;
+ List *idxs = NIL;
+ List *attnums = NIL;
+ bool all_not_null = true;
+ ListCell *lc,
+ *lc2;
+
+ Relation pg_index = table_open(IndexRelationId, AccessShareLock);
+
+ /* Prepare to scan pg_index for entries having indrelid = this rel */
+ ScanKeyInit(&skey,
+ Anum_pg_index_indrelid,
+ BTEqualStrategyNumber,
+ F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(relation)));
+
+ indscan = systable_beginscan(pg_index, IndexIndrelidIndexId, true,
+ NULL, 1, &skey);
+
+ while (HeapTupleIsValid(htup = systable_getnext(indscan)))
+ {
+ Form_pg_attribute attr;
+ Relation index_rel;
+
+ Form_pg_index index = (Form_pg_index) GETSTRUCT(htup);
+
+ /*
+ * We only use non-deferred, valid, and alive btree index to vertify
+ * not-null constraints
+ */
+ if (!index->indimmediate || !index->indisvalid || !index->indislive)
+ continue;
+
+ /* cannot 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;
+
+ index_rel = index_open(index->indexrelid, AccessShareLock);
+
+ if (index_rel->rd_rel->relam != BTREE_AM_OID)
+ {
+ index_close(index_rel, AccessShareLock);
+
+ continue;
+ }
+
+ attr = TupleDescAttr(RelationGetDescr(relation), (index->indkey.values[0] - 1));
+
+ if (list_member_int(notnull_attnums, attr->attnum) &&
+ !list_member_int(attnums, attr->attnum))
+ {
+ attnums = lappend_int(attnums, attr->attnum);
+ idxs = lappend_oid(idxs, index->indexrelid);
+ }
+
+ index_close(index_rel, NoLock);
+ }
+ systable_endscan(indscan);
+ table_close(pg_index, AccessShareLock);
+
+ /*
+ * Each NOT NULL constraint requires a suitable index for verification. If
+ * no such index exists, fall back to a regular table scan to verify the
+ * constraints.
+ */
+ if (attnums == NIL ||
+ list_length(notnull_attnums) != list_length(attnums))
+ return false;
+
+ foreach_int(attno, notnull_attnums)
+ {
+ if (!list_member_int(attnums, attno))
+ return false;
+ }
+
+ forboth(lc, attnums, lc2, idxs)
+ {
+ SnapshotData DirtySnapshot;
+ IndexScanDesc index_scan;
+ ScanKeyData scankeys[INDEX_MAX_KEYS];
+ AttrNumber sk_attno = -1;
+ AttrNumber attno = lfirst_int(lc);
+ Oid indexoid = lfirst_oid(lc2);
+
+ Relation idxrel = index_open(indexoid, NoLock);
+ IndexInfo *indexInfo = BuildIndexInfo(idxrel);
+ int indnkeyatts = IndexRelationGetNumberOfKeyAttributes(idxrel);
+
+ TupleTableSlot *existing_slot = table_slot_create(relation, NULL);
+
+ /*
+ * Search the tuples that are in the index for any violations,
+ * including tuples that aren't visible yet.
+ */
+ InitDirtySnapshot(DirtySnapshot);
+
+ if (indexInfo->ii_IndexAttrNumbers[0] == attno)
+ sk_attno = 1;
+ else
+ elog(ERROR, "cache lookup failed for attribute number %d on index %u",
+ 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,
+ idxrel,
+ &DirtySnapshot,
+ NULL,
+ indnkeyatts,
+ 0);
+ index_rescan(index_scan, scankeys, indnkeyatts, NULL, 0);
+
+ while (index_getnext_slot(index_scan, ForwardScanDirection, existing_slot))
+ {
+ /*
+ * At this point, we have found that some attribute value is NULL.
+ * btree indexes are never lossy, we don't need recheck the
+ * condition.
+ */
+ all_not_null = false;
+ break;
+ }
+
+ index_endscan(index_scan);
+ index_close(idxrel, NoLock);
+ ExecDropSingleTupleTableSlot(existing_slot);
+
+ /* exit earlier */
+ if (!all_not_null)
+ return false;
+ }
+
+ return true;
+}
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index ebc892a2a42..fc6f5457370 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -1717,3 +1717,68 @@ COMMENT ON CONSTRAINT inv_ck ON DOMAIN constraint_comments_dom IS 'comment on in
CREATE TABLE regress_notnull1 (a integer);
CREATE TABLE regress_notnull2 () INHERITS (regress_notnull1);
ALTER TABLE ONLY regress_notnull2 ALTER COLUMN a SET NOT NULL;
+CREATE TABLE tp_notnull (a int, b int, c int, d int, f1 int default 10, f2 int default 11) PARTITION BY range(a);
+CREATE TABLE tp_notnull_1 partition of tp_notnull for values FROM ( 1 ) to (10);
+CREATE TABLE tp_notnull_2 partition of tp_notnull for values FROM ( 10 ) to (21);
+CREATE INDEX tp_notnull_idx_b ON tp_notnull(b);
+CREATE INDEX tp_notnull_idx_c ON tp_notnull(c);
+CREATE INDEX tp_notnull_idx_d ON tp_notnull(d);
+CREATE INDEX tp_notnull_idx_d_include ON tp_notnull(d) include(f1);
+CREATE INDEX tp_notnull_idx_f2 ON tp_notnull USING hash (f2 int4_ops);
+INSERT INTO tp_notnull SELECT g, g + 10, g + 15, g + random(min=>1::int, max=>10::int) FROM generate_series(8,19) g;
+SET log_statement to NONE;
+SET client_min_messages TO 'debug1';
+--debug message should contain "have been validated by using index scan"
+ALTER TABLE tp_notnull ALTER COLUMN b SET NOT NULL;
+DEBUG: all new not-null constraints on relation "tp_notnull_1" have been validated by using index scan
+DEBUG: all new not-null constraints on relation "tp_notnull_2" have been validated by using index scan
+ALTER TABLE tp_notnull ALTER COLUMN b DROP NOT NULL;
+--debug message should contain "have been validated by using index scan"
+ALTER TABLE tp_notnull_1 ALTER COLUMN b SET NOT NULL;
+DEBUG: all new not-null constraints on relation "tp_notnull_1" have been validated by using index scan
+--debug message should contain "have been validated by using index scan"
+ALTER TABLE tp_notnull_2 ALTER COLUMN b SET NOT NULL;
+DEBUG: all new not-null constraints on relation "tp_notnull_2" have been validated by using index scan
+ALTER TABLE tp_notnull_2 ADD CONSTRAINT nn_d NOT NULL d NOT VALID;
+--debug message should not contain "have been validated by using index scan"
+ALTER TABLE tp_notnull_2 VALIDATE CONSTRAINT nn_d;
+DEBUG: verifying table "tp_notnull_2"
+--debug message should not contain "have been validated by using index scan"
+ALTER TABLE tp_notnull ADD CONSTRAINT nnf1 NOT NULL f1;
+DEBUG: verifying table "tp_notnull_1"
+DEBUG: verifying table "tp_notnull_2"
+--debug message should not contain "have been validated by using index scan"
+ALTER TABLE tp_notnull ADD CONSTRAINT nnf2 NOT NULL f2;
+DEBUG: verifying table "tp_notnull_1"
+DEBUG: verifying table "tp_notnull_2"
+--debug message should not contain "have been validated by using index scan"
+ALTER TABLE tp_notnull ALTER COLUMN a SET NOT NULL, ADD CONSTRAINT c_nn NOT NULL c;
+DEBUG: verifying table "tp_notnull_1"
+DEBUG: verifying table "tp_notnull_2"
+--debug message should contain "have been validated by using index scan"
+ALTER TABLE tp_notnull ALTER COLUMN d SET NOT NULL, ADD CONSTRAINT c_nn NOT NULL c, ALTER COLUMN b SET NOT NULL;
+DEBUG: all new not-null constraints on relation "tp_notnull_1" have been validated by using index scan
+ALTER TABLE tp_notnull ALTER COLUMN b DROP NOT NULL;
+--debug message should not contain "have been validated by using index scan"
+ALTER TABLE tp_notnull ALTER COLUMN b SET NOT NULL, ALTER COLUMN b SET DATA TYPE BIGINT;
+DEBUG: rewriting table "tp_notnull_1"
+DEBUG: building index "tp_notnull_1_c_idx" on table "tp_notnull_1" serially
+DEBUG: index "tp_notnull_1_c_idx" can safely use deduplication
+DEBUG: building index "tp_notnull_1_d_idx" on table "tp_notnull_1" serially
+DEBUG: index "tp_notnull_1_d_idx" can safely use deduplication
+DEBUG: building index "tp_notnull_1_d_f1_idx" on table "tp_notnull_1" serially
+DEBUG: building index "tp_notnull_1_f2_idx" on table "tp_notnull_1" serially
+DEBUG: building index "tp_notnull_1_b_idx" on table "tp_notnull_1" serially
+DEBUG: index "tp_notnull_1_b_idx" can safely use deduplication
+DEBUG: rewriting table "tp_notnull_2"
+DEBUG: building index "tp_notnull_2_c_idx" on table "tp_notnull_2" serially
+DEBUG: index "tp_notnull_2_c_idx" can safely use deduplication
+DEBUG: building index "tp_notnull_2_d_idx" on table "tp_notnull_2" serially
+DEBUG: index "tp_notnull_2_d_idx" can safely use deduplication
+DEBUG: building index "tp_notnull_2_d_f1_idx" on table "tp_notnull_2" serially
+DEBUG: building index "tp_notnull_2_f2_idx" on table "tp_notnull_2" serially
+DEBUG: building index "tp_notnull_2_b_idx" on table "tp_notnull_2" serially
+DEBUG: index "tp_notnull_2_b_idx" can safely use deduplication
+RESET client_min_messages;
+RESET log_statement;
+DROP TABLE tp_notnull;
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 1e9989698b6..3e6c8a1120a 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -1059,3 +1059,51 @@ COMMENT ON CONSTRAINT inv_ck ON DOMAIN constraint_comments_dom IS 'comment on in
CREATE TABLE regress_notnull1 (a integer);
CREATE TABLE regress_notnull2 () INHERITS (regress_notnull1);
ALTER TABLE ONLY regress_notnull2 ALTER COLUMN a SET NOT NULL;
+
+CREATE TABLE tp_notnull (a int, b int, c int, d int, f1 int default 10, f2 int default 11) PARTITION BY range(a);
+CREATE TABLE tp_notnull_1 partition of tp_notnull for values FROM ( 1 ) to (10);
+CREATE TABLE tp_notnull_2 partition of tp_notnull for values FROM ( 10 ) to (21);
+CREATE INDEX tp_notnull_idx_b ON tp_notnull(b);
+CREATE INDEX tp_notnull_idx_c ON tp_notnull(c);
+CREATE INDEX tp_notnull_idx_d ON tp_notnull(d);
+CREATE INDEX tp_notnull_idx_d_include ON tp_notnull(d) include(f1);
+CREATE INDEX tp_notnull_idx_f2 ON tp_notnull USING hash (f2 int4_ops);
+INSERT INTO tp_notnull SELECT g, g + 10, g + 15, g + random(min=>1::int, max=>10::int) FROM generate_series(8,19) g;
+
+SET log_statement to NONE;
+SET client_min_messages TO 'debug1';
+
+--debug message should contain "have been validated by using index scan"
+ALTER TABLE tp_notnull ALTER COLUMN b SET NOT NULL;
+ALTER TABLE tp_notnull ALTER COLUMN b DROP NOT NULL;
+
+--debug message should contain "have been validated by using index scan"
+ALTER TABLE tp_notnull_1 ALTER COLUMN b SET NOT NULL;
+
+--debug message should contain "have been validated by using index scan"
+ALTER TABLE tp_notnull_2 ALTER COLUMN b SET NOT NULL;
+
+ALTER TABLE tp_notnull_2 ADD CONSTRAINT nn_d NOT NULL d NOT VALID;
+--debug message should not contain "have been validated by using index scan"
+ALTER TABLE tp_notnull_2 VALIDATE CONSTRAINT nn_d;
+
+--debug message should not contain "have been validated by using index scan"
+ALTER TABLE tp_notnull ADD CONSTRAINT nnf1 NOT NULL f1;
+
+--debug message should not contain "have been validated by using index scan"
+ALTER TABLE tp_notnull ADD CONSTRAINT nnf2 NOT NULL f2;
+
+--debug message should not contain "have been validated by using index scan"
+ALTER TABLE tp_notnull ALTER COLUMN a SET NOT NULL, ADD CONSTRAINT c_nn NOT NULL c;
+
+--debug message should contain "have been validated by using index scan"
+ALTER TABLE tp_notnull ALTER COLUMN d SET NOT NULL, ADD CONSTRAINT c_nn NOT NULL c, ALTER COLUMN b SET NOT NULL;
+
+ALTER TABLE tp_notnull ALTER COLUMN b DROP NOT NULL;
+--debug message should not contain "have been validated by using index scan"
+ALTER TABLE tp_notnull ALTER COLUMN b SET NOT NULL, ALTER COLUMN b SET DATA TYPE BIGINT;
+
+RESET client_min_messages;
+RESET log_statement;
+
+DROP TABLE tp_notnull;
--
2.34.1
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: using index to speedup add not null constraints to a table
@ 2026-04-07 07:39 jian he <[email protected]>
parent: jian he <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: jian he @ 2026-04-07 07:39 UTC (permalink / raw)
To: Álvaro Herrera <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On Fri, Feb 6, 2026 at 12:58 PM jian he <[email protected]> wrote:
>
+-- should produce a debug message containing "have been validated by
using index scan"
+ ALTER TABLE tp_notnull ALTER COLUMN b SET NOT NULL, ALTER COLUMN b
SET DATA TYPE BIGINT;
DEBUG: rehashing catalog cache id 64 for pg_class; 257 tups, 128 buckets
The above DEBUG message make me realize that
+SET client_min_messages TO 'debug1';
+--debug message should contain "have been validated by using index scan"
+ALTER TABLE tp_notnull ALTER COLUMN b SET NOT NULL;
+DEBUG: all new not-null constraints on relation "tp_notnull_1" have
been validated by using index scan
+DEBUG: all new not-null constraints on relation "tp_notnull_2" have
been validated by using index scan
Using the above regression test to check if certain
``ereport(DEBUG1,`` is reached is unstable.
Therefore, we fall back to the previously used TAP tests to ensure
ereport(DEBUG1,
errmsg_internal("all new not-null constraints on relation
\"%s\" have been validated by using index scan",
RelationGetRelationName(oldrel)));
is being reached.
Rebased and made some minor comment adjustments; no big change.
--
jian
https://www.enterprisedb.com/
Attachments:
[text/x-patch] v13-0001-using-indexscan-to-speedup-add-not-null-constraints.patch (19.5K, 2-v13-0001-using-indexscan-to-speedup-add-not-null-constraints.patch)
download | inline diff:
From 5a07e49e06eff375968ec111b38d6580174e8cca Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Tue, 7 Apr 2026 15:26:15 +0800
Subject: [PATCH v13 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 happens in phase3 ATRewriteTable
1. collect all not-null constraints.
2. For each not-null constraint, check whether there is a corresponding index
available for validation. If not, then cannot use indexscan mechanism to verify not-null
constraints.
3. If any of the following conditions are true
* table scan or rewrite is required,
* table wasn't locked with `AccessExclusiveLock`
* the NOT NULL constraint applies to a virtual generated column
then index scan cannot be used for fast validation.
If all conditions are satisfied, attempt to use indexscan to verify whether the
column contains any NULL values.
concurrency concern:
ALTER TABLE SET NOT NULL will take an ACCESS EXCLUSIVE lock, so there is less
variant of racing issue can occur. see[2]
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+hw@mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/5444
---
src/backend/commands/tablecmds.c | 282 +++++++++++++++++-
src/test/modules/test_misc/meson.build | 1 +
.../t/012_indexscan_validate_notnull.pl | 118 ++++++++
3 files changed, 396 insertions(+), 5 deletions(-)
create mode 100644 src/test/modules/test_misc/t/012_indexscan_validate_notnull.pl
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index eec09ba1ded..7b88d0882bd 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -211,16 +211,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;
@@ -760,6 +763,7 @@ static void ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation
static void ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab,
Relation rel, PartitionCmd *cmd,
AlterTableUtilityContext *context);
+static bool index_check_notnull(Relation relation, List *notnull_attnums);
/* ----------------------------------------------------------------
* DefineRelation
@@ -6251,6 +6255,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;
@@ -6304,9 +6311,74 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
wholeatt->attnum);
}
}
- if (notnull_attrs || notnull_virtual_attrs)
+ }
+
+ /*
+ * The conditions for using indexscan mechanism fast verifying not-null
+ * constraints are quite strict. All of the following conditions must be
+ * met.
+ *
+ * 1. AlteredTableInfo->verify_new_notnull is true.
+ *
+ * 2. No table scan (e.g., for CHECK constraint verification) or table
+ * rewrite is expected later, if one is, using indexscan would just wastes
+ * cycles.
+ *
+ * 3. Indexes cannot be created on virtual generated columns, so fast
+ * checking not-null constraints is not applicable to them.
+ *
+ * 4. The relation must be a plain table.
+ *
+ * 5. To prevent possible concurrency issues, the table must already be
+ * locked with an AccessExclusiveLock, which is the lock obtained during
+ * ALTER TABLE SET NOT NULL.
+ */
+ if (!needscan &&
+ newrel == NULL &&
+ tab->verify_new_notnull &&
+ notnull_virtual_attrs == NIL &&
+ oldrel->rd_rel->relkind == RELKIND_RELATION &&
+ CheckRelationLockedByMe(oldrel, AccessExclusiveLock, false))
+ {
+ List *notnull_attnums = NIL;
+
+ Assert(!tab->rewrite);
+
+ foreach(l, tab->constraints)
+ {
+ Form_pg_attribute attr;
+ NewConstraint *con = lfirst(l);
+
+ /*
+ * Skip any constraint that is not a NOT NULL constraint. We
+ * cannot simply raise an error here, because this might be a
+ * FOREIGN KEY (even if it isn't a CHECK constraint).
+ */
+ if (con->contype != CONSTR_NOTNULL)
+ continue;
+
+ attr = TupleDescAttr(newTupDesc, con->attnum - 1);
+
+ if (attr->attisdropped)
+ continue;
+
+ Assert(attr->attnotnull);
+ Assert(attr->attnum == con->attnum);
+ Assert(attr->attgenerated != ATTRIBUTE_GENERATED_VIRTUAL);
+
+ notnull_attnums = list_append_unique_int(notnull_attnums,
+ attr->attnum);
+ }
+
+ if (notnull_attnums != NIL && index_check_notnull(oldrel, notnull_attnums))
+ ereport(DEBUG1,
+ errmsg_internal("all new not-null constraints on relation \"%s\" have been validated by using index scan",
+ RelationGetRelationName(oldrel)));
+ else
needscan = true;
}
+ else if (notnull_attrs || notnull_virtual_attrs)
+ needscan = true;
if (newrel || needscan)
{
@@ -8009,6 +8081,7 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
CookedConstraint *ccon;
List *cooked;
bool is_no_inherit = false;
+ AlteredTableInfo *tab = ATGetQueueEntry(wqueue, rel);;
/* Guard against stack overflow due to overly deep inheritance tree. */
check_stack_depth();
@@ -8137,6 +8210,25 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
cooked = AddRelationNewConstraints(rel, NIL, list_make1(constraint),
false, !recursing, false, NULL);
ccon = linitial(cooked);
+
+ Assert(ccon->contype == CONSTR_NOTNULL);
+
+ /*
+ * We may using indexscan mechanism to vertify this not-null constraint in
+ * Phase3. Add this to-be-validated not-null constraint to Phase 3's
+ * queue.
+ */
+ if (!ccon->skip_validation)
+ {
+ NewConstraint *newcon = palloc0_object(NewConstraint);
+
+ newcon->name = ccon->name;
+ newcon->contype = CONSTR_NOTNULL;
+ newcon->attnum = ccon->attnum;
+
+ tab->constraints = lappend(tab->constraints, newcon);
+ }
+
ObjectAddressSet(address, ConstraintRelationId, ccon->conoid);
/* Mark pg_attribute.attnotnull for the column and queue validation */
@@ -10030,13 +10122,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 = palloc0_object(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);
@@ -13474,6 +13568,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);
@@ -13537,7 +13632,21 @@ 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. ALTER TABLE SET NOT NULL adds a NOT NULL
+ * constraint (NewConstraint) to AlteredTableInfo->constraints; for
+ * consistency, we do the same here. This setup allows phase 3
+ * (ATRewriteTable) to quickly validate the column's NULL status using an
+ * index scan, if all conditions are met.
+ */
+ newcon = palloc0_object(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;
/*
@@ -23602,3 +23711,166 @@ ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, Relation rel,
/* Restore the userid and security context. */
SetUserIdAndSecContext(save_userid, save_sec_context);
}
+
+/*
+ * notnull_attnums: list of attribute numbers for all newly added NOT NULL
+ * constraints.
+ *
+ * For each NOT NULL attribute, first check whether the relation has a suitable
+ * index to fetch that attribute content. If so, using indexscan to verify that
+ * attribute contains no NULL values.
+ *
+ * Returning true means the new NOT NULL constraints are fully verified and no
+ * extra table scans are necessary.
+ */
+static bool
+index_check_notnull(Relation relation, List *notnull_attnums)
+{
+ SysScanDesc indscan;
+ ScanKeyData skey;
+ HeapTuple htup;
+ List *idxs = NIL;
+ List *attnums = NIL;
+ bool all_not_null = true;
+ ListCell *lc,
+ *lc2;
+
+ Relation pg_index = table_open(IndexRelationId, AccessShareLock);
+
+ /* Prepare to scan pg_index for entries having indrelid = this rel */
+ ScanKeyInit(&skey,
+ Anum_pg_index_indrelid,
+ BTEqualStrategyNumber,
+ F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(relation)));
+
+ indscan = systable_beginscan(pg_index, IndexIndrelidIndexId, true,
+ NULL, 1, &skey);
+
+ while (HeapTupleIsValid(htup = systable_getnext(indscan)))
+ {
+ Form_pg_attribute attr;
+ Relation index_rel;
+
+ Form_pg_index index = (Form_pg_index) GETSTRUCT(htup);
+
+ /*
+ * We only use non-deferred, valid, and live b-tree indexes to verify
+ * NOT NULL constraints.
+ */
+ if (!index->indimmediate || !index->indisvalid || !index->indislive)
+ continue;
+
+ /* cannot 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;
+
+ index_rel = index_open(index->indexrelid, AccessShareLock);
+
+ if (index_rel->rd_rel->relam != BTREE_AM_OID)
+ {
+ index_close(index_rel, AccessShareLock);
+
+ continue;
+ }
+
+ attr = TupleDescAttr(RelationGetDescr(relation), (index->indkey.values[0] - 1));
+
+ if (list_member_int(notnull_attnums, attr->attnum) &&
+ !list_member_int(attnums, attr->attnum))
+ {
+ attnums = lappend_int(attnums, attr->attnum);
+ idxs = lappend_oid(idxs, index->indexrelid);
+ }
+
+ index_close(index_rel, NoLock);
+ }
+ systable_endscan(indscan);
+ table_close(pg_index, AccessShareLock);
+
+ /*
+ * Verify NOT NULL constraints using a suitable index, falling back to a
+ * full table scan if a suitable index is not present.
+ */
+ if (attnums == NIL ||
+ list_length(notnull_attnums) != list_length(attnums))
+ return false;
+
+ foreach_int(attno, notnull_attnums)
+ {
+ if (!list_member_int(attnums, attno))
+ return false;
+ }
+
+ forboth(lc, attnums, lc2, idxs)
+ {
+ SnapshotData DirtySnapshot;
+ IndexScanDesc index_scan;
+ ScanKeyData scankeys[INDEX_MAX_KEYS];
+ AttrNumber sk_attno = -1;
+ AttrNumber attno = lfirst_int(lc);
+ Oid indexoid = lfirst_oid(lc2);
+
+ Relation idxrel = index_open(indexoid, NoLock);
+ IndexInfo *indexInfo = BuildIndexInfo(idxrel);
+ int indnkeyatts = IndexRelationGetNumberOfKeyAttributes(idxrel);
+
+ TupleTableSlot *existing_slot = table_slot_create(relation, NULL);
+
+ /*
+ * Search the tuples that are in the index for any violations,
+ * including tuples that aren't visible yet.
+ */
+ InitDirtySnapshot(DirtySnapshot);
+
+ if (indexInfo->ii_IndexAttrNumbers[0] == attno)
+ sk_attno = 1;
+ else
+ elog(ERROR, "cache lookup failed for attribute number %d on index %u",
+ 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,
+ idxrel,
+ &DirtySnapshot,
+ NULL,
+ indnkeyatts,
+ 0,
+ SO_NONE);
+ index_rescan(index_scan, scankeys, indnkeyatts, NULL, 0);
+
+ while (index_getnext_slot(index_scan, ForwardScanDirection, existing_slot))
+ {
+ /*
+ * At this point, we have found that some attribute value is NULL.
+ * btree indexes are never lossy, we don't need recheck the
+ * condition.
+ */
+ all_not_null = false;
+ break;
+ }
+
+ index_endscan(index_scan);
+ index_close(idxrel, NoLock);
+ ExecDropSingleTupleTableSlot(existing_slot);
+
+ /* exit earlier */
+ if (!all_not_null)
+ return false;
+ }
+
+ return true;
+}
diff --git a/src/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build
index 1b25d98f7f3..e02a2cb0c42 100644
--- a/src/test/modules/test_misc/meson.build
+++ b/src/test/modules/test_misc/meson.build
@@ -20,6 +20,7 @@ tests += {
't/009_log_temp_files.pl',
't/010_index_concurrently_upsert.pl',
't/011_lock_stats.pl',
+ 't/012_indexscan_validate_notnull.pl',
],
# The injection points are cluster-wide, so disable installcheck
'runningcheck': false,
diff --git a/src/test/modules/test_misc/t/012_indexscan_validate_notnull.pl b/src/test/modules/test_misc/t/012_indexscan_validate_notnull.pl
new file mode 100644
index 00000000000..53e38588740
--- /dev/null
+++ b/src/test/modules/test_misc/t/012_indexscan_validate_notnull.pl
@@ -0,0 +1,118 @@
+# Copyright (c) 2026, 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;
+use Time::HiRes qw(usleep);
+
+# 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;
+}
+
+# Verify that run_sql_command output confirms an index scan is used for the NOT NULL check.
+sub is_indexscan_veritify_notnull
+{
+ my $output = shift;
+ return index($output, 'DEBUG: all new 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_notnull (a int, b int, c int, d int, f1 int default 1, f2 int default 2, f3 int default 3) PARTITION BY range(a);
+ CREATE TABLE tp_notnull_1 partition of tp_notnull for values from ( 1 ) to (10);
+ CREATE TABLE tp_notnull_2 partition of tp_notnull for values from ( 10 ) to (21);
+ INSERT INTO tp_notnull(a,b,c,d) SELECT g, g + 10, g + 15, g FROM generate_series(1,19) g;
+ CREATE INDEX ON tp_notnull(b);
+ CREATE INDEX ON tp_notnull(c);
+ CREATE INDEX ON tp_notnull(d);
+ CREATE INDEX ON tp_notnull(f1) INCLUDE (f2);
+ CREATE INDEX ON tp_notnull USING hash (f3 int4_ops);
+ ');
+
+# Using index scan for NOT NULL verification requires every not-null column must possess a suitable index
+$output = run_sql_command(
+ 'ALTER TABLE tp_notnull ALTER COLUMN a SET NOT NULL, ADD CONSTRAINT c_nn NOT NULL c');
+ok(!is_indexscan_veritify_notnull($output), 'cannot use indexscan to verify not-null constraints on table tp_notnull');
+
+# All columns have index on it, OK
+$output = run_sql_command(
+ 'ALTER TABLE tp_notnull ALTER COLUMN d SET NOT NULL, ALTER COLUMN c SET NOT NULL, ALTER COLUMN b SET NOT NULL;');
+ok(is_indexscan_veritify_notnull($output),
+ 'using indexscan to verify not-null constraints on column b, c, d');
+ok( $output =~
+ m/all new not-null constraints on relation "tp_notnull_1" have been validated by using index scan/,
+ 'all newly added constraints proved by indexscan');
+ok( $output =~
+ m/all new not-null constraints on relation "tp_notnull_2" have been validated by using index scan/,
+ 'all newly added constraints proved by indexscan');
+
+run_sql_command('ALTER TABLE tp_notnull ALTER COLUMN b DROP NOT NULL;');
+# Now using index scans on partitions to quickly verify NOT NULL constraints
+$output = run_sql_command('ALTER TABLE tp_notnull_1 ALTER COLUMN b SET NOT NULL;');
+ok(is_indexscan_veritify_notnull($output), 'index scan verifies NOT NULL for column b');
+
+run_sql_command('ALTER TABLE tp_notnull ALTER COLUMN b DROP NOT NULL;');
+# Cannot use indexscan to verify not-null constraint if table rewrite happens
+$output = run_sql_command(
+ 'ALTER TABLE tp_notnull ALTER COLUMN b SET NOT NULL, ALTER COLUMN b SET DATA TYPE bigint;'
+);
+ok(!is_indexscan_veritify_notnull($output), 'table rewrite on tp_notnull, cannot use indexscan to verify not-null constraints on it');
+
+run_sql_command('ALTER TABLE tp_notnull ALTER COLUMN d DROP NOT NULL');
+
+# Cannot use indexscan because ALTER TABLE VALIDATE CONSTRAINT only hold
+# ShareUpdateExclusiveLock lock on the table
+run_sql_command('ALTER TABLE tp_notnull ADD CONSTRAINT tp_notnull_d NOT NULL d NOT VALID;');
+$output = run_sql_command(
+ 'ALTER TABLE tp_notnull VALIDATE CONSTRAINT tp_notnull_d;'
+);
+ok(!is_indexscan_veritify_notnull($output), 'cannot use indexscan for ALTER TABLE VALIDATE CONSTRAINT command');
+
+run_sql_command('ALTER TABLE tp_notnull ALTER COLUMN d DROP NOT NULL;
+ ALTER TABLE tp_notnull ADD CONSTRAINT tp_notnull_d NOT NULL d NOT VALID;');
+
+# However this will be OK, since ALTER COLUMN SET NOT NULL will acquire AccessExclusiveLock
+$output = run_sql_command(
+ 'ALTER TABLE tp_notnull VALIDATE CONSTRAINT tp_notnull_d, ALTER COLUMN b SET NOT NULL;'
+);
+ok(is_indexscan_veritify_notnull($output), 'using indexscan to verify not-null constraints on column b, c, d');
+
+# Indexscan mechanism to verify NOT NULL constraints cannot apply to index INCLUDE column
+$output = run_sql_command(
+ 'ALTER TABLE tp_notnull ADD CONSTRAINT nnf4 NOT NULL f2;'
+);
+ok(!is_indexscan_veritify_notnull($output), 'cannot use indexscan to validate not-null constraint on tp_notnull');
+
+# Indexscan mechanism to verify NOT NULL constraints cannot apply to non-Btree index
+$output = run_sql_command('ALTER TABLE tp_notnull ALTER COLUMN f3 SET NOT NULL;');
+ok(!is_indexscan_veritify_notnull($output), 'cannot use indexscan to validate not-null constraints on tp_notnull');
+
+$node->stop('fast');
+
+done_testing();
\ No newline at end of file
--
2.34.1
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: using index to speedup add not null constraints to a table
@ 2026-04-07 10:02 Álvaro Herrera <[email protected]>
parent: jian he <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Álvaro Herrera @ 2026-04-07 10:02 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
Hello,
On 2026-Apr-07, jian he wrote:
> Rebased and made some minor comment adjustments; no big change.
I think you may have not looked at my review at
https://postgr.es/m/[email protected]
because as far as I can tell, this new version has essentially the same
structure as before that review.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: using index to speedup add not null constraints to a table
@ 2026-04-08 01:45 jian he <[email protected]>
parent: Álvaro Herrera <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: jian he @ 2026-04-08 01:45 UTC (permalink / raw)
To: Álvaro Herrera <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On Tue, Apr 7, 2026 at 6:02 PM Álvaro Herrera <[email protected]> wrote:
>
> Hello,
>
> I think you may have not looked at my review at
> https://postgr.es/m/[email protected]
> because as far as I can tell, this new version has essentially the same
> structure as before that review.
>
Hi.
I guess the point I haven't addressed yet is:
> However, if we do this, then I think computing notnull_attrs is
> pointless. So we should only change the order: do this new check first,
> and if we find that any new not-null column is on a generated column,
> then we compute both notnull_virtual_attrs and notnull_attrs. No?
>
The attached patch implements this reordering,
first do index_check_notnull then compute both notnull_virtual_attrs
and notnull_attrs.
--
jian
https://www.enterprisedb.com/
Attachments:
[text/x-patch] v14-0001-using-indexscan-to-speedup-add-not-null-constraints.patch (20.1K, 2-v14-0001-using-indexscan-to-speedup-add-not-null-constraints.patch)
download | inline diff:
From b1dab79bb0c81e668811bd124360d602b5c4674d Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Wed, 8 Apr 2026 09:33:56 +0800
Subject: [PATCH v14 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 happens in phase3 ATRewriteTable
1. collect all not-null constraints.
2. For each not-null constraint, check whether there is a corresponding index
available for validation. If not, then cannot use indexscan mechanism to verify not-null
constraints.
3. If any of the following conditions are true
* table scan or rewrite is required,
* table wasn't locked with `AccessExclusiveLock`
* the NOT NULL constraint applies to a virtual generated column
then index scan cannot be used for fast validation.
If all conditions are satisfied, attempt to use indexscan to verify whether the
column contains any NULL values.
concurrency concern:
ALTER TABLE SET NOT NULL will take an ACCESS EXCLUSIVE lock, so there is less
variant of racing issue can occur. see[2]
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+hw@mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/5444
---
src/backend/commands/tablecmds.c | 295 +++++++++++++++++-
src/test/modules/test_misc/meson.build | 1 +
.../t/012_indexscan_validate_notnull.pl | 118 +++++++
3 files changed, 409 insertions(+), 5 deletions(-)
create mode 100644 src/test/modules/test_misc/t/012_indexscan_validate_notnull.pl
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index eec09ba1ded..178ac5f2a9b 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -211,16 +211,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;
@@ -760,6 +763,7 @@ static void ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation
static void ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab,
Relation rel, PartitionCmd *cmd,
AlterTableUtilityContext *context);
+static bool index_check_notnull(Relation relation, List *notnull_attnums);
/* ----------------------------------------------------------------
* DefineRelation
@@ -6251,6 +6255,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;
@@ -6278,6 +6285,78 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
notnull_attrs = notnull_virtual_attrs = NIL;
if (newrel || tab->verify_new_notnull)
{
+ bool null_vertified = false;
+
+ /*
+ * The conditions for using indexscan mechanism fast verifying
+ * not-null constraints are quite strict. All of the following
+ * conditions must be met.
+ *
+ * 1. AlteredTableInfo->verify_new_notnull is true.
+ *
+ * 2. No table scan (e.g., for CHECK constraint verification) or table
+ * rewrite is expected later, if one is, using indexscan would just
+ * wastes cycles.
+ *
+ * 3. Indexes cannot be created on virtual generated columns, so fast
+ * checking not-null constraints is not applicable to them.
+ *
+ * 4. The relation must be a plain table.
+ *
+ * 5. To prevent possible concurrency issues, the table must already
+ * be locked with an AccessExclusiveLock, which is the lock obtained
+ * during ALTER TABLE SET NOT NULL.
+ */
+ if (!needscan &&
+ newrel == NULL &&
+ oldrel->rd_rel->relkind == RELKIND_RELATION &&
+ CheckRelationLockedByMe(oldrel, AccessExclusiveLock, false))
+ {
+ List *notnull_attnums = NIL;
+
+ Assert(!tab->rewrite);
+
+ foreach(l, tab->constraints)
+ {
+ Form_pg_attribute attr;
+ NewConstraint *con = lfirst(l);
+
+ if (con->contype != CONSTR_NOTNULL)
+ continue;
+
+ attr = TupleDescAttr(newTupDesc, con->attnum - 1);
+
+ if (attr->attisdropped)
+ continue;
+
+ Assert(attr->attnotnull);
+ Assert(attr->attnum == con->attnum);
+
+ if (attr->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ {
+ needscan = true;
+ break;
+ }
+
+ notnull_attnums = list_append_unique_int(notnull_attnums,
+ attr->attnum);
+ }
+
+ if (!needscan)
+ {
+ if (index_check_notnull(oldrel, notnull_attnums))
+ {
+ null_vertified = true;
+
+ ereport(DEBUG1,
+ errmsg_internal("all new not-null constraints on relation \"%s\" have been validated by using index scan",
+ RelationGetRelationName(oldrel)));
+ }
+ else
+ needscan = true;
+ }
+ }
+
/*
* If we are rebuilding the tuples OR if we added any new but not
* verified not-null constraints, check all *valid* not-null
@@ -6288,7 +6367,7 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
* not-null constraints over virtual generated columns; instead, they
* are collected in notnull_virtual_attrs for verification elsewhere.
*/
- for (i = 0; i < newTupDesc->natts; i++)
+ for (i = 0; !null_vertified && i < newTupDesc->natts; i++)
{
CompactAttribute *attr = TupleDescCompactAttr(newTupDesc, i);
@@ -6306,6 +6385,9 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
}
if (notnull_attrs || notnull_virtual_attrs)
needscan = true;
+
+ if (null_vertified)
+ Assert(needscan == false);
}
if (newrel || needscan)
@@ -8009,6 +8091,7 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
CookedConstraint *ccon;
List *cooked;
bool is_no_inherit = false;
+ AlteredTableInfo *tab = ATGetQueueEntry(wqueue, rel);;
/* Guard against stack overflow due to overly deep inheritance tree. */
check_stack_depth();
@@ -8137,6 +8220,25 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
cooked = AddRelationNewConstraints(rel, NIL, list_make1(constraint),
false, !recursing, false, NULL);
ccon = linitial(cooked);
+
+ Assert(ccon->contype == CONSTR_NOTNULL);
+
+ /*
+ * We may using indexscan mechanism to vertify this not-null constraint in
+ * Phase3. Add this to-be-validated not-null constraint to Phase 3's
+ * queue.
+ */
+ if (!ccon->skip_validation)
+ {
+ NewConstraint *newcon = palloc0_object(NewConstraint);
+
+ newcon->name = ccon->name;
+ newcon->contype = CONSTR_NOTNULL;
+ newcon->attnum = ccon->attnum;
+
+ tab->constraints = lappend(tab->constraints, newcon);
+ }
+
ObjectAddressSet(address, ConstraintRelationId, ccon->conoid);
/* Mark pg_attribute.attnotnull for the column and queue validation */
@@ -10030,13 +10132,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 = palloc0_object(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);
@@ -13474,6 +13578,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);
@@ -13537,7 +13642,21 @@ 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. ALTER TABLE SET NOT NULL adds a NOT NULL
+ * constraint (NewConstraint) to AlteredTableInfo->constraints; for
+ * consistency, we do the same here. This setup allows phase 3
+ * (ATRewriteTable) to quickly validate the column's NULL status using an
+ * index scan, if all conditions are met.
+ */
+ newcon = palloc0_object(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;
/*
@@ -23602,3 +23721,169 @@ ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, Relation rel,
/* Restore the userid and security context. */
SetUserIdAndSecContext(save_userid, save_sec_context);
}
+
+/*
+ * notnull_attnums: list of attribute numbers for all newly added NOT NULL
+ * constraints.
+ *
+ * For each NOT NULL attribute, first check whether the relation has a suitable
+ * index to fetch that attribute content. If so, using indexscan to verify that
+ * attribute contains no NULL values.
+ *
+ * Returning true means the new NOT NULL constraints are fully verified and no
+ * extra table scans are necessary.
+ */
+static bool
+index_check_notnull(Relation relation, List *notnull_attnums)
+{
+ SysScanDesc indscan;
+ ScanKeyData skey;
+ HeapTuple htup;
+ List *idxs = NIL;
+ List *attnums = NIL;
+ bool all_not_null = true;
+ ListCell *lc,
+ *lc2;
+
+ Relation pg_index = table_open(IndexRelationId, AccessShareLock);
+
+ if (notnull_attnums == NIL)
+ return false;
+
+ /* Prepare to scan pg_index for entries having indrelid = this rel */
+ ScanKeyInit(&skey,
+ Anum_pg_index_indrelid,
+ BTEqualStrategyNumber,
+ F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(relation)));
+
+ indscan = systable_beginscan(pg_index, IndexIndrelidIndexId, true,
+ NULL, 1, &skey);
+
+ while (HeapTupleIsValid(htup = systable_getnext(indscan)))
+ {
+ Form_pg_attribute attr;
+ Relation index_rel;
+
+ Form_pg_index index = (Form_pg_index) GETSTRUCT(htup);
+
+ /*
+ * We only use non-deferred, valid, and live b-tree indexes to verify
+ * NOT NULL constraints.
+ */
+ if (!index->indimmediate || !index->indisvalid || !index->indislive)
+ continue;
+
+ /* cannot 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;
+
+ index_rel = index_open(index->indexrelid, AccessShareLock);
+
+ if (index_rel->rd_rel->relam != BTREE_AM_OID)
+ {
+ index_close(index_rel, AccessShareLock);
+
+ continue;
+ }
+
+ attr = TupleDescAttr(RelationGetDescr(relation), (index->indkey.values[0] - 1));
+
+ if (list_member_int(notnull_attnums, attr->attnum) &&
+ !list_member_int(attnums, attr->attnum))
+ {
+ attnums = lappend_int(attnums, attr->attnum);
+ idxs = lappend_oid(idxs, index->indexrelid);
+ }
+
+ index_close(index_rel, NoLock);
+ }
+ systable_endscan(indscan);
+ table_close(pg_index, AccessShareLock);
+
+ /*
+ * Verify NOT NULL constraints using a suitable index, falling back to a
+ * full table scan if a suitable index is not present.
+ */
+ if (attnums == NIL ||
+ list_length(notnull_attnums) != list_length(attnums))
+ return false;
+
+ foreach_int(attno, notnull_attnums)
+ {
+ if (!list_member_int(attnums, attno))
+ return false;
+ }
+
+ forboth(lc, attnums, lc2, idxs)
+ {
+ SnapshotData DirtySnapshot;
+ IndexScanDesc index_scan;
+ ScanKeyData scankeys[INDEX_MAX_KEYS];
+ AttrNumber sk_attno = -1;
+ AttrNumber attno = lfirst_int(lc);
+ Oid indexoid = lfirst_oid(lc2);
+
+ Relation idxrel = index_open(indexoid, NoLock);
+ IndexInfo *indexInfo = BuildIndexInfo(idxrel);
+ int indnkeyatts = IndexRelationGetNumberOfKeyAttributes(idxrel);
+
+ TupleTableSlot *existing_slot = table_slot_create(relation, NULL);
+
+ /*
+ * Search the tuples that are in the index for any violations,
+ * including tuples that aren't visible yet.
+ */
+ InitDirtySnapshot(DirtySnapshot);
+
+ if (indexInfo->ii_IndexAttrNumbers[0] == attno)
+ sk_attno = 1;
+ else
+ elog(ERROR, "cache lookup failed for attribute number %d on index %u",
+ 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,
+ idxrel,
+ &DirtySnapshot,
+ NULL,
+ indnkeyatts,
+ 0,
+ SO_NONE);
+ index_rescan(index_scan, scankeys, indnkeyatts, NULL, 0);
+
+ while (index_getnext_slot(index_scan, ForwardScanDirection, existing_slot))
+ {
+ /*
+ * At this point, we have found that some attribute value is NULL.
+ * btree indexes are never lossy, we don't need recheck the
+ * condition.
+ */
+ all_not_null = false;
+ break;
+ }
+
+ index_endscan(index_scan);
+ index_close(idxrel, NoLock);
+ ExecDropSingleTupleTableSlot(existing_slot);
+
+ /* exit earlier */
+ if (!all_not_null)
+ return false;
+ }
+
+ return true;
+}
diff --git a/src/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build
index 1b25d98f7f3..e02a2cb0c42 100644
--- a/src/test/modules/test_misc/meson.build
+++ b/src/test/modules/test_misc/meson.build
@@ -20,6 +20,7 @@ tests += {
't/009_log_temp_files.pl',
't/010_index_concurrently_upsert.pl',
't/011_lock_stats.pl',
+ 't/012_indexscan_validate_notnull.pl',
],
# The injection points are cluster-wide, so disable installcheck
'runningcheck': false,
diff --git a/src/test/modules/test_misc/t/012_indexscan_validate_notnull.pl b/src/test/modules/test_misc/t/012_indexscan_validate_notnull.pl
new file mode 100644
index 00000000000..53e38588740
--- /dev/null
+++ b/src/test/modules/test_misc/t/012_indexscan_validate_notnull.pl
@@ -0,0 +1,118 @@
+# Copyright (c) 2026, 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;
+use Time::HiRes qw(usleep);
+
+# 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;
+}
+
+# Verify that run_sql_command output confirms an index scan is used for the NOT NULL check.
+sub is_indexscan_veritify_notnull
+{
+ my $output = shift;
+ return index($output, 'DEBUG: all new 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_notnull (a int, b int, c int, d int, f1 int default 1, f2 int default 2, f3 int default 3) PARTITION BY range(a);
+ CREATE TABLE tp_notnull_1 partition of tp_notnull for values from ( 1 ) to (10);
+ CREATE TABLE tp_notnull_2 partition of tp_notnull for values from ( 10 ) to (21);
+ INSERT INTO tp_notnull(a,b,c,d) SELECT g, g + 10, g + 15, g FROM generate_series(1,19) g;
+ CREATE INDEX ON tp_notnull(b);
+ CREATE INDEX ON tp_notnull(c);
+ CREATE INDEX ON tp_notnull(d);
+ CREATE INDEX ON tp_notnull(f1) INCLUDE (f2);
+ CREATE INDEX ON tp_notnull USING hash (f3 int4_ops);
+ ');
+
+# Using index scan for NOT NULL verification requires every not-null column must possess a suitable index
+$output = run_sql_command(
+ 'ALTER TABLE tp_notnull ALTER COLUMN a SET NOT NULL, ADD CONSTRAINT c_nn NOT NULL c');
+ok(!is_indexscan_veritify_notnull($output), 'cannot use indexscan to verify not-null constraints on table tp_notnull');
+
+# All columns have index on it, OK
+$output = run_sql_command(
+ 'ALTER TABLE tp_notnull ALTER COLUMN d SET NOT NULL, ALTER COLUMN c SET NOT NULL, ALTER COLUMN b SET NOT NULL;');
+ok(is_indexscan_veritify_notnull($output),
+ 'using indexscan to verify not-null constraints on column b, c, d');
+ok( $output =~
+ m/all new not-null constraints on relation "tp_notnull_1" have been validated by using index scan/,
+ 'all newly added constraints proved by indexscan');
+ok( $output =~
+ m/all new not-null constraints on relation "tp_notnull_2" have been validated by using index scan/,
+ 'all newly added constraints proved by indexscan');
+
+run_sql_command('ALTER TABLE tp_notnull ALTER COLUMN b DROP NOT NULL;');
+# Now using index scans on partitions to quickly verify NOT NULL constraints
+$output = run_sql_command('ALTER TABLE tp_notnull_1 ALTER COLUMN b SET NOT NULL;');
+ok(is_indexscan_veritify_notnull($output), 'index scan verifies NOT NULL for column b');
+
+run_sql_command('ALTER TABLE tp_notnull ALTER COLUMN b DROP NOT NULL;');
+# Cannot use indexscan to verify not-null constraint if table rewrite happens
+$output = run_sql_command(
+ 'ALTER TABLE tp_notnull ALTER COLUMN b SET NOT NULL, ALTER COLUMN b SET DATA TYPE bigint;'
+);
+ok(!is_indexscan_veritify_notnull($output), 'table rewrite on tp_notnull, cannot use indexscan to verify not-null constraints on it');
+
+run_sql_command('ALTER TABLE tp_notnull ALTER COLUMN d DROP NOT NULL');
+
+# Cannot use indexscan because ALTER TABLE VALIDATE CONSTRAINT only hold
+# ShareUpdateExclusiveLock lock on the table
+run_sql_command('ALTER TABLE tp_notnull ADD CONSTRAINT tp_notnull_d NOT NULL d NOT VALID;');
+$output = run_sql_command(
+ 'ALTER TABLE tp_notnull VALIDATE CONSTRAINT tp_notnull_d;'
+);
+ok(!is_indexscan_veritify_notnull($output), 'cannot use indexscan for ALTER TABLE VALIDATE CONSTRAINT command');
+
+run_sql_command('ALTER TABLE tp_notnull ALTER COLUMN d DROP NOT NULL;
+ ALTER TABLE tp_notnull ADD CONSTRAINT tp_notnull_d NOT NULL d NOT VALID;');
+
+# However this will be OK, since ALTER COLUMN SET NOT NULL will acquire AccessExclusiveLock
+$output = run_sql_command(
+ 'ALTER TABLE tp_notnull VALIDATE CONSTRAINT tp_notnull_d, ALTER COLUMN b SET NOT NULL;'
+);
+ok(is_indexscan_veritify_notnull($output), 'using indexscan to verify not-null constraints on column b, c, d');
+
+# Indexscan mechanism to verify NOT NULL constraints cannot apply to index INCLUDE column
+$output = run_sql_command(
+ 'ALTER TABLE tp_notnull ADD CONSTRAINT nnf4 NOT NULL f2;'
+);
+ok(!is_indexscan_veritify_notnull($output), 'cannot use indexscan to validate not-null constraint on tp_notnull');
+
+# Indexscan mechanism to verify NOT NULL constraints cannot apply to non-Btree index
+$output = run_sql_command('ALTER TABLE tp_notnull ALTER COLUMN f3 SET NOT NULL;');
+ok(!is_indexscan_veritify_notnull($output), 'cannot use indexscan to validate not-null constraints on tp_notnull');
+
+$node->stop('fast');
+
+done_testing();
\ No newline at end of file
--
2.34.1
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2026-04-08 01:45 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-05-13 13:37 ` Andres Freund <[email protected]>
2025-05-14 09:16 ` jian he <[email protected]>
2025-10-16 19:57 ` Álvaro Herrera <[email protected]>
2025-10-20 06:26 ` jian he <[email protected]>
2026-02-06 04:58 Re: using index to speedup add not null constraints to a table jian he <[email protected]>
2026-04-07 07:39 ` jian he <[email protected]>
2026-04-07 10:02 ` Álvaro Herrera <[email protected]>
2026-04-08 01:45 ` jian he <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox