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

On Fri, Feb 6, 2026 at 12:58 PM jian he <jian(dot)universality(at)gmail(dot)com> 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/