On Mon, Jun 2, 2025 at 9:57 PM jian he <jian.universal...@gmail.com> wrote: > > Currently in pg18, we can add not enforced check constraints. > but we can not do ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED > for check constraint. > > The attached patch is implementation of changing enforceability of > check constraint.
hi. rebase and minor refactoring.
From ba604766c43e474aa6b08ba613dfb04854ec7f40 Mon Sep 17 00:00:00 2001 From: jian he <jian.universal...@gmail.com> Date: Fri, 4 Jul 2025 19:51:43 +0800 Subject: [PATCH v2 1/1] alter check constraint enforceability syntax: ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED context: https://git.postgresql.org/cgit/postgresql.git/commit/?id=ca87c415e2fccf81cec6fd45698dde9fae0ab570 discussion: https://postgr.es/m/cacjufxhch_fu-fsewscvg9mn6-5tzr6h9ntn+0kugtcaerd...@mail.gmail.com --- doc/src/sgml/ref/alter_table.sgml | 4 +- src/backend/commands/tablecmds.c | 164 ++++++++++++++++++++-- src/test/regress/expected/constraints.out | 55 ++++++++ src/test/regress/expected/inherit.out | 58 ++++++++ src/test/regress/sql/constraints.sql | 36 +++++ src/test/regress/sql/inherit.sql | 40 ++++++ 6 files changed, 346 insertions(+), 11 deletions(-) diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 1e4f26c13f6..5f6504711c7 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -559,8 +559,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM <listitem> <para> This form alters the attributes of a constraint that was previously - created. Currently only foreign key constraints may be altered in - this fashion, but see below. + created. Currently <literal>FOREIGN KEY</literal> and + <literal>CHECK</literal> constraints may be altered in this fashion, but see below. </para> </listitem> </varlistentry> diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index cb811520c29..5ffbb9d6b5a 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -403,6 +403,9 @@ static bool ATExecAlterConstrEnforceability(List **wqueue, ATAlterConstraint *cm Oid ReferencedParentUpdTrigger, Oid ReferencingParentInsTrigger, Oid ReferencingParentUpdTrigger); +static bool ATExecAlterCheckConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon, + Relation conrel, HeapTuple contuple, + bool recurse, bool recursing, LOCKMODE lockmode); static bool ATExecAlterConstrDeferrability(List **wqueue, ATAlterConstraint *cmdcon, Relation conrel, Relation tgrel, Relation rel, HeapTuple contuple, bool recurse, @@ -421,6 +424,9 @@ static void AlterConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *c Oid ReferencedParentUpdTrigger, Oid ReferencingParentInsTrigger, Oid ReferencingParentUpdTrigger); +static void AlterCheckConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon, + Relation conrel, Oid conrelid, + bool recurse, bool recursing, LOCKMODE lockmode); static void AlterConstrDeferrabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon, Relation conrel, Relation tgrel, Relation rel, HeapTuple contuple, bool recurse, @@ -12153,7 +12159,7 @@ GetForeignKeyCheckTriggers(Relation trigrel, * * Update the attributes of a constraint. * - * Currently only works for Foreign Key and not null constraints. + * Currently works for Foreign Key, CHECK, and not null constraints. * * If the constraint is modified, returns its address; otherwise, return * InvalidObjectAddress. @@ -12215,11 +12221,13 @@ ATExecAlterConstraint(List **wqueue, Relation rel, ATAlterConstraint *cmdcon, (errcode(ERRCODE_WRONG_OBJECT_TYPE), errmsg("constraint \"%s\" of relation \"%s\" is not a foreign key constraint", cmdcon->conname, RelationGetRelationName(rel)))); - if (cmdcon->alterEnforceability && currcon->contype != CONSTRAINT_FOREIGN) + if (cmdcon->alterEnforceability && + (currcon->contype != CONSTRAINT_FOREIGN && currcon->contype != CONSTRAINT_CHECK)) ereport(ERROR, - (errcode(ERRCODE_WRONG_OBJECT_TYPE), - errmsg("cannot alter enforceability of constraint \"%s\" of relation \"%s\"", - cmdcon->conname, RelationGetRelationName(rel)))); + errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("cannot alter enforceability of constraint \"%s\" of relation \"%s\"", + cmdcon->conname, RelationGetRelationName(rel)), + errhint("Only foreign key, check constraint can change enforceability")); if (cmdcon->alterInheritability && currcon->contype != CONSTRAINT_NOTNULL) ereport(ERROR, @@ -12321,16 +12329,20 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon, * enforceability, we don't need to explicitly update multiple entries in * pg_trigger related to deferrability. * - * Modifying enforceability involves either creating or dropping the - * trigger, during which the deferrability setting will be adjusted + * Modifying foreign key enforceability involves either creating or dropping + * the trigger, during which the deferrability setting will be adjusted * automatically. */ - if (cmdcon->alterEnforceability && + if (cmdcon->alterEnforceability && currcon->contype == CONSTRAINT_FOREIGN && ATExecAlterConstrEnforceability(wqueue, cmdcon, conrel, tgrel, currcon->conrelid, currcon->confrelid, contuple, lockmode, InvalidOid, InvalidOid, InvalidOid, InvalidOid)) changed = true; + else if (cmdcon->alterEnforceability && currcon->contype == CONSTRAINT_CHECK && + ATExecAlterCheckConstrEnforceability(wqueue, cmdcon, conrel, contuple, + recurse, false, lockmode)) + changed = true; else if (cmdcon->alterDeferrability && ATExecAlterConstrDeferrability(wqueue, cmdcon, conrel, tgrel, rel, @@ -12361,7 +12373,141 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon, } /* - * Returns true if the constraint's enforceability is altered. + * Returns true if the CHECK constraint's enforceability is altered. + * + * Note that we must recurse even when trying to change a check constraint to + * not enforced if it is already not enforced, in case descendant constraints + * might be enforced and need to be changed to not enforced. Conversely, we + * should do nothing if a constraint is being set to enforced and is already + * enforced, as descendant constraints cannot be different in that case. + */ +static bool +ATExecAlterCheckConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon, + Relation conrel, HeapTuple contuple, + bool recurse, bool recursing, LOCKMODE lockmode) +{ + Form_pg_constraint currcon; + Relation rel; + bool changed = false; + List *children = NIL; + + /* Since this function recurses, it could be driven to stack overflow */ + check_stack_depth(); + + Assert(cmdcon->alterEnforceability); + + currcon = (Form_pg_constraint) GETSTRUCT(contuple); + + Assert(currcon->contype == CONSTRAINT_CHECK); + + rel = table_open(currcon->conrelid, lockmode); + if (currcon->conenforced != cmdcon->is_enforced) + { + AlterConstrUpdateConstraintEntry(cmdcon, conrel, contuple); + changed = true; + } + + if (!cmdcon->is_enforced || changed) + { + /* + * If we're recursing, the parent has already done this, so skip it. + * Also, if the constraint is a NO INHERIT constraint, we shouldn't try + * to look for it in the children. + */ + if (!recursing && !currcon->connoinherit) + children = find_all_inheritors(RelationGetRelid(rel), + lockmode, NULL); + + foreach_oid(childoid, children) + { + if (childoid == RelationGetRelid(rel)) + continue; + + /* + * If we are told not to recurse, there had better not be any child + * tables, because we can't changing constraint enforceability on + * the parent unless we have chaned enforceability for all child + * tables. + */ + if (!recurse) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("constraint must be altered on child tables too")); + + AlterCheckConstrEnforceabilityRecurse(wqueue, cmdcon, conrel, childoid, false, true, lockmode); + } + } + + /* + * Tell Phase 3 to check that the constraint is satisfied by existing rows. + * We do this only when alter the constraint from not enforced to enforced. + */ + if (rel->rd_rel->relkind == RELKIND_RELATION && + cmdcon->is_enforced && + !currcon->conenforced) + { + AlteredTableInfo *tab; + NewConstraint *newcon; + Datum val; + char *conbin; + + newcon = (NewConstraint *) palloc0(sizeof(NewConstraint)); + newcon->name = pstrdup(NameStr(currcon->conname)); + newcon->contype = CONSTR_CHECK; + val = SysCacheGetAttrNotNull(CONSTROID, contuple, + Anum_pg_constraint_conbin); + conbin = TextDatumGetCString(val); + newcon->qual = expand_generated_columns_in_expr(stringToNode(conbin), rel, 1); + + /* Find or create work queue entry for this table */ + tab = ATGetQueueEntry(wqueue, rel); + tab->constraints = lappend(tab->constraints, newcon); + } + + table_close(rel, NoLock); + return changed; +} + +static void +AlterCheckConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon, + Relation conrel, Oid conrelid, + bool recurse, bool recursing, + LOCKMODE lockmode) +{ + SysScanDesc pscan; + HeapTuple childtup; + ScanKeyData skey[3]; + + ScanKeyInit(&skey[0], + Anum_pg_constraint_conrelid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(conrelid)); + ScanKeyInit(&skey[1], + Anum_pg_constraint_contypid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(InvalidOid)); + ScanKeyInit(&skey[2], + Anum_pg_constraint_conname, + BTEqualStrategyNumber, F_NAMEEQ, + CStringGetDatum(cmdcon->conname)); + + pscan = systable_beginscan(conrel, ConstraintRelidTypidNameIndexId, true, + NULL, 3, skey); + + if (!HeapTupleIsValid(childtup = systable_getnext(pscan))) + ereport(ERROR, + errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("constraint \"%s\" of relation \"%s\" does not exist", + cmdcon->conname, get_rel_name(conrelid))); + + ATExecAlterCheckConstrEnforceability(wqueue, cmdcon, conrel, childtup, + recurse, recursing, lockmode); + + systable_endscan(pscan); +} + +/* + * Returns true if the FOREIGN KEY constraint's enforceability is altered. * * Depending on whether the constraint is being set to ENFORCED or NOT * ENFORCED, it creates or drops the trigger accordingly. diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out index ccea883cffd..10075546034 100644 --- a/src/test/regress/expected/constraints.out +++ b/src/test/regress/expected/constraints.out @@ -390,6 +390,59 @@ SELECT * FROM COPY_TBL; 6 | OK | 4 (2 rows) +-- +-- CHECK constraints +-- ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED +create table parted_ch_tbl(a int, constraint cc check (a > 10) not enforced, b int ) partition by range(a); +create table parted_ch_tbl_1 partition of parted_ch_tbl for values from (0) to (10) partition by list(b); +create table parted_ch_tbl_11 partition of parted_ch_tbl_1 for values in (0, 1); +create table parted_ch_tbl_12 partition of parted_ch_tbl_1 for values in (2); +create table parted_ch_tbl_2 partition of parted_ch_tbl for values from (10) to (20); +alter table parted_ch_tbl_2 add constraint cc_2 check( a < 15) not enforced; +insert into parted_ch_tbl values (1, 2), (9, 1), (16, 16); +create view check_constraint_status as +select conname, conrelid::regclass, conenforced, convalidated +from pg_constraint +where conrelid::regclass::text ~* '^parted_ch' and contype = 'c' +order by conrelid::regclass, conname; +alter table parted_ch_tbl alter constraint cc not enforced; --no-op +alter table parted_ch_tbl alter constraint cc enforced; --error +ERROR: check constraint "cc" of relation "parted_ch_tbl_11" is violated by some row +delete from parted_ch_tbl where a = 1; +alter table parted_ch_tbl alter constraint cc enforced; --error +ERROR: check constraint "cc" of relation "parted_ch_tbl_11" is violated by some row +delete from parted_ch_tbl where a = 9; +alter table parted_ch_tbl alter constraint cc enforced; --ok +--check these CHECK constraint status +select * from check_constraint_status; + conname | conrelid | conenforced | convalidated +---------+------------------+-------------+-------------- + cc | parted_ch_tbl | t | t + cc | parted_ch_tbl_1 | t | t + cc | parted_ch_tbl_11 | t | t + cc | parted_ch_tbl_12 | t | t + cc | parted_ch_tbl_2 | t | t + cc_2 | parted_ch_tbl_2 | f | f +(6 rows) + +alter table parted_ch_tbl_2 alter constraint cc_2 enforced; --error +ERROR: check constraint "cc_2" of relation "parted_ch_tbl_2" is violated by some row +delete from parted_ch_tbl where a = 16; +alter table parted_ch_tbl_2 alter constraint cc_2 enforced; --ok +alter table parted_ch_tbl alter constraint cc not enforced; --ok +--check these CHECK constraint status again +select * from check_constraint_status; + conname | conrelid | conenforced | convalidated +---------+------------------+-------------+-------------- + cc | parted_ch_tbl | f | f + cc | parted_ch_tbl_1 | f | f + cc | parted_ch_tbl_11 | f | f + cc | parted_ch_tbl_12 | f | f + cc | parted_ch_tbl_2 | f | f + cc_2 | parted_ch_tbl_2 | t | t +(6 rows) + +drop table parted_ch_tbl; -- -- Primary keys -- @@ -746,8 +799,10 @@ LINE 1: CREATE TABLE UNIQUE_NOTEN_TBL(i int UNIQUE NOT ENFORCED); ^ ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key ENFORCED; ERROR: cannot alter enforceability of constraint "unique_tbl_i_key" of relation "unique_tbl" +HINT: Only foreign key, check constraint can change enforceability ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key NOT ENFORCED; ERROR: cannot alter enforceability of constraint "unique_tbl_i_key" of relation "unique_tbl" +HINT: Only foreign key, check constraint can change enforceability -- can't make an existing constraint NOT VALID ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key NOT VALID; ERROR: constraints cannot be altered to be NOT VALID diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index 78dead65325..92321efb2d4 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1418,11 +1418,50 @@ order by 1, 2; p1_c3 | inh_check_constraint9 | f | 2 | t | t (38 rows) +-- Tests for ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED +alter table p1 drop constraint inh_check_constraint1; +alter table p1_c1 drop constraint inh_check_constraint1; +insert into p1_c1 values(-2); +insert into p1_c3 values(-3); +alter table only p1 alter constraint inh_check_constraint3 enforced; --error +ERROR: constraint must be altered on child tables too +alter table only p1 alter constraint inh_check_constraint3 not enforced; --error +ERROR: constraint must be altered on child tables too +alter table p1 alter constraint inh_check_constraint3 enforced; --error +ERROR: check constraint "inh_check_constraint3" of relation "p1_c1" is violated by some row +delete from only p1_c1 where f1 = -2; +alter table p1_c1 alter constraint inh_check_constraint3 enforced; --error +ERROR: check constraint "inh_check_constraint3" of relation "p1_c3" is violated by some row +delete from only p1_c3 where f1 = -3; +alter table p1 alter constraint inh_check_constraint3 enforced; --ok +alter table p1 alter constraint inh_check_constraint3 not enforced; --ok +select conname, conenforced, convalidated, conrelid::regclass +from pg_constraint +where conname = 'inh_check_constraint3' and contype = 'c' +order by conrelid::regclass; + conname | conenforced | convalidated | conrelid +-----------------------+-------------+--------------+---------- + inh_check_constraint3 | f | f | p1 + inh_check_constraint3 | f | f | p1_c1 + inh_check_constraint3 | f | f | p1_c2 + inh_check_constraint3 | f | f | p1_c3 +(4 rows) + drop table p1 cascade; NOTICE: drop cascades to 3 other objects DETAIL: drop cascades to table p1_c1 drop cascades to table p1_c2 drop cascades to table p1_c3 +--for "no inherit" check constraint, it will not recurse to child table +create table p1(f1 int constraint p1_a_check check (f1 > 0) no inherit not enforced); +create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) not enforced); +alter table p1_c1 inherit p1; +insert into p1_c1 values(-11); +alter table p1 alter constraint p1_a_check enforced; --ok +alter table p1_c1 alter constraint p1_a_check enforced; --error +ERROR: check constraint "p1_a_check" of relation "p1_c1" is violated by some row +drop table p1 cascade; +NOTICE: drop cascades to table p1_c1 -- -- Similarly, check the merging of existing constraints; a parent constraint -- marked as NOT ENFORCED can merge with an ENFORCED child constraint, but the @@ -1431,6 +1470,25 @@ drop cascades to table p1_c3 create table p1(f1 int constraint p1_a_check check (f1 > 0) not enforced); create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) enforced); alter table p1_c1 inherit p1; +insert into p1 values(-1); --ok +insert into p1_c1 values(-1); --error +ERROR: new row for relation "p1_c1" violates check constraint "p1_a_check" +DETAIL: Failing row contains (-1). +alter table p1 alter constraint p1_a_check enforced; --error +ERROR: check constraint "p1_a_check" of relation "p1" is violated by some row +truncate p1; +alter table p1 alter constraint p1_a_check enforced; --ok +alter table p1 alter constraint p1_a_check not enforced; --ok +select conname, conenforced, convalidated, conrelid::regclass +from pg_constraint +where conname = 'p1_a_check' and contype = 'c' +order by conrelid::regclass; + conname | conenforced | convalidated | conrelid +------------+-------------+--------------+---------- + p1_a_check | f | f | p1 + p1_a_check | f | f | p1_c1 +(2 rows) + drop table p1 cascade; NOTICE: drop cascades to table p1_c1 create table p1(f1 int constraint p1_a_check check (f1 > 0) enforced); diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql index 7487723ab84..c9134c91d21 100644 --- a/src/test/regress/sql/constraints.sql +++ b/src/test/regress/sql/constraints.sql @@ -266,6 +266,42 @@ COPY COPY_TBL FROM :'filename'; SELECT * FROM COPY_TBL; +-- +-- CHECK constraints +-- ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED +create table parted_ch_tbl(a int, constraint cc check (a > 10) not enforced, b int ) partition by range(a); +create table parted_ch_tbl_1 partition of parted_ch_tbl for values from (0) to (10) partition by list(b); +create table parted_ch_tbl_11 partition of parted_ch_tbl_1 for values in (0, 1); +create table parted_ch_tbl_12 partition of parted_ch_tbl_1 for values in (2); +create table parted_ch_tbl_2 partition of parted_ch_tbl for values from (10) to (20); +alter table parted_ch_tbl_2 add constraint cc_2 check( a < 15) not enforced; +insert into parted_ch_tbl values (1, 2), (9, 1), (16, 16); + +create view check_constraint_status as +select conname, conrelid::regclass, conenforced, convalidated +from pg_constraint +where conrelid::regclass::text ~* '^parted_ch' and contype = 'c' +order by conrelid::regclass, conname; + +alter table parted_ch_tbl alter constraint cc not enforced; --no-op +alter table parted_ch_tbl alter constraint cc enforced; --error +delete from parted_ch_tbl where a = 1; +alter table parted_ch_tbl alter constraint cc enforced; --error +delete from parted_ch_tbl where a = 9; +alter table parted_ch_tbl alter constraint cc enforced; --ok + +--check these CHECK constraint status +select * from check_constraint_status; + +alter table parted_ch_tbl_2 alter constraint cc_2 enforced; --error +delete from parted_ch_tbl where a = 16; +alter table parted_ch_tbl_2 alter constraint cc_2 enforced; --ok +alter table parted_ch_tbl alter constraint cc not enforced; --ok + +--check these CHECK constraint status again +select * from check_constraint_status; +drop table parted_ch_tbl; + -- -- Primary keys -- diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql index 699e8ac09c8..d8040b36da5 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -510,6 +510,35 @@ select conrelid::regclass::text as relname, conname, conislocal, coninhcount, co from pg_constraint where conname like 'inh\_check\_constraint%' order by 1, 2; +-- Tests for ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED +alter table p1 drop constraint inh_check_constraint1; +alter table p1_c1 drop constraint inh_check_constraint1; + +insert into p1_c1 values(-2); +insert into p1_c3 values(-3); +alter table only p1 alter constraint inh_check_constraint3 enforced; --error +alter table only p1 alter constraint inh_check_constraint3 not enforced; --error + +alter table p1 alter constraint inh_check_constraint3 enforced; --error +delete from only p1_c1 where f1 = -2; +alter table p1_c1 alter constraint inh_check_constraint3 enforced; --error + +delete from only p1_c3 where f1 = -3; +alter table p1 alter constraint inh_check_constraint3 enforced; --ok +alter table p1 alter constraint inh_check_constraint3 not enforced; --ok +select conname, conenforced, convalidated, conrelid::regclass +from pg_constraint +where conname = 'inh_check_constraint3' and contype = 'c' +order by conrelid::regclass; +drop table p1 cascade; + +--for "no inherit" check constraint, it will not recurse to child table +create table p1(f1 int constraint p1_a_check check (f1 > 0) no inherit not enforced); +create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) not enforced); +alter table p1_c1 inherit p1; +insert into p1_c1 values(-11); +alter table p1 alter constraint p1_a_check enforced; --ok +alter table p1_c1 alter constraint p1_a_check enforced; --error drop table p1 cascade; -- @@ -520,6 +549,17 @@ drop table p1 cascade; create table p1(f1 int constraint p1_a_check check (f1 > 0) not enforced); create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) enforced); alter table p1_c1 inherit p1; +insert into p1 values(-1); --ok +insert into p1_c1 values(-1); --error +alter table p1 alter constraint p1_a_check enforced; --error +truncate p1; +alter table p1 alter constraint p1_a_check enforced; --ok +alter table p1 alter constraint p1_a_check not enforced; --ok + +select conname, conenforced, convalidated, conrelid::regclass +from pg_constraint +where conname = 'p1_a_check' and contype = 'c' +order by conrelid::regclass; drop table p1 cascade; create table p1(f1 int constraint p1_a_check check (f1 > 0) enforced); -- 2.34.1
