speedup ALTER TABLE ADD CHECK CONSTRAINT.
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t50649psql -h localhost -U postgresBuilt from patchset v1 (message #1), July 27, 2026 at 10:56 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t50649_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t50649_1 && git checkout t50649_1Patchset v1 (message #1) is on t50649_1
hi.
attached patch trying to speedup ALTER TABLE ADD CHECK CONSTRAINT.
demo:
drop table if exists t7;
create table t7 (a int not null, b int, c int);
insert into t7 select g, g+1, g %100 from generate_series(1,1_000_000) g;
create index on t7(a,b);
alter table t7 add check (a > 0);
patch: Time: 4.281 ms
master: Time: 491.689 ms
----------------------------
implementation:
step1. during execute command `ALTER TABLE ADD CHECK CONSTRAINT`
in AddRelationNewConstraints->StoreRelCheck
after StoreRelCheck add a CommandCounterIncrement();
so previously added CHECK pg_constraint can be seen by us.
we need to use pg_get_constraintdef to retrieve the CHECK constraint definition.
step2. check if this relation has any suitable index (not expression
index, not predicate index)
--whole patch hinges on SPI query can use indexscan to quickly
retrieve certain information.
step3: construct and execute these three SPI query:
("set enable_seqscan to off")
(SELECT 1 FROM the_table WHERE NOT (check_constraint_def)
AND check_constraint_def IS NOT NULL LIMIT 1)
("reset enable_seqscan")
the SPI query will be faster, if the qual(check_constraint_def) can be
utilized by indexscan.
if SPI_processed == 0 means we toggle this check constraint as
"already_validated" (bool) is true.
we stored already_validated in CookedConstraint. later pass it to
AlteredTableInfo->constraints.
then phrase 3 within ATRewriteTable, we can do
```
if (constraint->already_validated)
needscan = false;
```
----------------------------
concern:
only certain kinds of check constraint expressions can be used for
this optimization.
i do all the CHECK constraint expressions filter in checkconstraint_expr_walker.
use contain_volatile_functions to filter out volatile expressions,
add (check_constraint_def IS NOT NULL) in the above SPI query, i think
null handling is correct?
ALTER TABLE ADD CHECK CONSTRAINT is using ACCESS EXCLUSIVE lock.
but i need to think more about concurrently related issues.
idea come from this thread:
/messages/by-id/CANWftzK2MZ7Js_56V+ZcLxZyH1utBZx4uEg03P7Cee86K2roCQ@mail.gmail.com
Hello!
Thanks for the patch, but I think using SPI is still not allowed here: /messages/by-id/9878.1511970441@sss.pgh.pa.us
if it uses SPI for sub-operations of ALTER TABLE I think that is sufficient reason to reject it out of hand.
regards, Sergei
Import Notes
Resolved by subject fallback
Sergei Kornilov <sk@zsrv.org> writes:
Hello!
Thanks for the patch, but I think using SPI is still not allowed here: /messages/by-id/9878.1511970441@sss.pgh.pa.us
Yeah, if you want to do this you need to code the catalog lookup in
C. SPI just adds too many variables to what will happen, on top
of being enormously expensive compared to a handwritten lookup.
regards, tom lane
On Mon, Dec 2, 2024 at 12:06 AM Sergei Kornilov <sk@zsrv.org> wrote:
Hello!
Thanks for the patch, but I think using SPI is still not allowed here: /messages/by-id/9878.1511970441@sss.pgh.pa.usif it uses SPI for sub-operations of ALTER TABLE I think that is sufficient reason to reject it out of hand.
Thanks for dropping this link.
i wonder what was your thoughts about this
(/messages/by-id/900056D1-32DF-4927-8251-3E0C0DC407FD@anarazel.de)
at that time?