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

On Wed, Feb 5, 2025 at 4:24 PM jian he <jian.universality@gmail.com> wrote:
>
> rebased new patch attached.
> I also did some cosmetic changes. comments refined.
> make sure using index_scan mechanism to fast check column not-null can
> only be used via btree index.
> isolation tests are simplified.

I realized that my previous patch was quite wrong,
we should not do indexscan verify individual not-null constraints on phase2.

So a new patch is attached,
the main idea is Phase2 collects all to be added not-null constraints
to AlteredTableInfo->constraints.
then in Phase3  check, can we use index to fast check not-null
constraint or not.

To minimize concurrency issues, using an index scan to quickly validate
NOT NULL constraints requires strict conditions in Phase3:
* No table rewrite
* No table scan
* Each NOT NULL constraint must have a suitable supporting index for
fast checking
* The table must already hold an AccessExclusiveLock
* The DDL must not involve creating any new indexes

I don't have any good ideas to do the regress tests.
I use
                ereport(NOTICE,
                        errmsg("all not-null constraints on relation
\"%s\" are validated by index scan",
                                RelationGetRelationName(oldrel)));
to do the tests.

for example:
create temp table t2 (x int, y int, z int, primary key (x, y));
create unique index t2_z_uidx on t2(z);

alter table t2 alter column z set not null;
NOTICE:  all not-null constraints on relation "t2" are validated by index scan
ALTER TABLE