Location via proxy:   [ UP ]  
[Report a bug]   [Manage cookies]                
Skip to content
Permalink

Comparing changes

Choose two branches to see what’s changed or to start a new pull request. If you need to, you can also or learn more about diff comparisons.

Open a pull request

Create a new pull request by comparing changes across two branches. If you need to, you can also . Learn more about diff comparisons here.
base repository: postgresql-cfbot/postgresql
Failed to load repositories. Confirm that selected base ref is valid, then try again.
Loading
base: cf/5444~1
Choose a base ref
...
head repository: postgresql-cfbot/postgresql
Failed to load repositories. Confirm that selected head ref is valid, then try again.
Loading
compare: cf/5444
Choose a head ref
  • 2 commits
  • 8 files changed
  • 2 contributors

Commits on May 25, 2025

  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
    @jianhe-fun
    jianhe-fun authored and Commitfest Bot committed May 25, 2025
    Configuration menu
    Copy the full SHA
    f61d923 View commit details
    Browse the repository at this point in the history

  2. [CF 5444] v6 - using index to speedup add not null constraints to a t… 

    …able

This branch was automatically generated by a robot using patches from an
email thread registered at:

https://commitfest.postgresql.org/patch/5444

The branch will be overwritten each time a new patch version is posted to
the thread, and also periodically to check for bitrot caused by changes
on the master branch.

Patch(es): https://www.postgresql.org/message-id/CACJufxHNpusqZOhckVZxGAE2zOOWU0YTJvs3nob38XL=8zArpw@mail.gmail.com
Author(s): Jian He
    Commitfest Bot committed May 25, 2025
    Configuration menu
    Copy the full SHA
    dbcff68 View commit details
    Browse the repository at this point in the history
Loading