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

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