From ba246abdb3df94e3e4f44d4301596890f59d413f Mon Sep 17 00:00:00 2001 From: jian he Date: Tue, 16 Dec 2025 12:47:41 +0800 Subject: [PATCH v6 2/2] Add support for altering CHECK constraint enforceability This expands the capability of ALTER TABLE ALTER CONSTRAINT to include CHECK constraints; previously, altering constraint enforceability was only supported for foreign key constraints (as of commit eec0040). Altering a CHECK constraint from NOT ENFORCED to ENFORCED not only update catalog information, also perform a full table scan to validate existing data. Conversely, changing an ENFORCED CHECK constraint to NOT ENFORCED does not require a table scan, as it will only update catalog information. Author: jian he Reviewed-by: Amul Sul Reviewed-by: Kirill Reshke , Reviewed-by: Robert Treat (XXX delete this line later) commitfest: https://commitfest.postgresql.org/patch/5796 discussion: https://postgr.es/m/CACJufxHCh_FU-FsEwsCvg9mN6-5tzR6H9ntn+0KUgTCaerDOmg@mail.gmail.com --- doc/src/sgml/ref/alter_table.sgml | 4 +- src/backend/commands/tablecmds.c | 192 +++++++++++++++++++++- src/test/regress/expected/constraints.out | 80 +++++++++ src/test/regress/expected/inherit.out | 62 +++++++ src/test/regress/sql/constraints.sql | 52 ++++++ src/test/regress/sql/inherit.sql | 43 +++++ 6 files changed, 423 insertions(+), 10 deletions(-) diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 9abd8037f28..58fcbfb33c0 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -575,8 +575,8 @@ WITH ( MODULUS numeric_literal, REM 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 FOREIGN KEY and CHECK + constraints may be altered in this fashion, but see below. diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index dc906a16489..5fb5fa6c111 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -404,6 +404,10 @@ static bool ATExecAlterFKConstrEnforceability(List **wqueue, ATAlterConstraint * 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, @@ -422,6 +426,10 @@ static void AlterFKConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint 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, @@ -12217,7 +12225,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. @@ -12279,11 +12287,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)))); + cmdcon->conname, RelationGetRelationName(rel)), + errhint("Only foreign key and check constraints can change enforceability."))); if (cmdcon->alterInheritability && currcon->contype != CONSTRAINT_NOTNULL) ereport(ERROR, @@ -12389,12 +12399,21 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon, * dropping the trigger, during which the deferrability setting will be * adjusted automatically. */ - if (cmdcon->alterEnforceability && - ATExecAlterFKConstrEnforceability(wqueue, cmdcon, conrel, tgrel, - currcon->conrelid, currcon->confrelid, - contuple, lockmode, InvalidOid, - InvalidOid, InvalidOid, InvalidOid)) + if (cmdcon->alterEnforceability) + { + if (currcon->contype == CONSTRAINT_FOREIGN) + ATExecAlterFKConstrEnforceability(wqueue, cmdcon, conrel, tgrel, + currcon->conrelid, + currcon->confrelid, + contuple, lockmode, + InvalidOid, InvalidOid, + InvalidOid, InvalidOid); + else if (currcon->contype == CONSTRAINT_CHECK) + ATExecAlterCheckConstrEnforceability(wqueue, cmdcon, conrel, + contuple, recurse, false, + lockmode); changed = true; + } else if (cmdcon->alterDeferrability && ATExecAlterConstrDeferrability(wqueue, cmdcon, conrel, tgrel, rel, @@ -12573,6 +12592,163 @@ ATExecAlterFKConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon, return changed; } + /* + * Returns true if the CHECK constraint's enforceability is altered. + */ +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); + + /* + * Parent relation already locked by caller, children will be locked by + * find_all_inheritors. So NoLock is fine here. + */ + rel = table_open(currcon->conrelid, NoLock); + + if (currcon->conenforced != cmdcon->is_enforced) + { + AlterConstrUpdateConstraintEntry(cmdcon, conrel, contuple); + changed = true; + } + + /* + * 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. + */ + 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 change constraint enforceability + * on the parent unless we have changed enforceability for all + * child. + */ + if (!recurse) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("constraint must be altered on child tables too"), + errhint("Do not specify the ONLY keyword.")); + + AlterCheckConstrEnforceabilityRecurse(wqueue, cmdcon, conrel, + childoid, false, true, + lockmode); + } + } + + /* + * Tell Phase 3 to check that the constraint is satisfied by existing + * rows. We only need do this when altering the constraint from NOT + * ENFORCED to ENFORCED. + */ + if (rel->rd_rel->relkind == RELKIND_RELATION && + !currcon->conenforced && + cmdcon->is_enforced) + { + AlteredTableInfo *tab; + NewConstraint *newcon; + Datum val; + char *conbin; + + newcon = palloc0_object(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; +} + +/* + * Invokes ATExecAlterCheckConstrEnforceability for each CHECK constraint that + * is a child of the specified constraint. + * + * We rely on the parent and child tables having identical CHECK constraint + * names to retrieve the child's pg_constraint tuple. + * + * The arguments to this function have the same meaning as the arguments to + * ATExecAlterCheckConstrEnforceability. + */ +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 constraint's deferrability is altered. * diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out index 1bbf59cca02..3850e9925c0 100644 --- a/src/test/regress/expected/constraints.out +++ b/src/test/regress/expected/constraints.out @@ -390,6 +390,84 @@ SELECT * FROM COPY_TBL; 6 | OK | 4 (2 rows) +-- +-- CHECK constraints +-- ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED +create table parted_ch( + a int, b int, + constraint cc check (a > 10) not enforced, + constraint cc_1 check (b < 17) not enforced +) partition by range(a); +create table parted_ch_1 partition of parted_ch for values from (0) to (10) partition by list(b); +create table parted_ch_11 partition of parted_ch_1 for values in (0, 1, 22,4); +create table parted_ch_12 partition of parted_ch_1 for values in (2); +create table parted_ch_2(b int, a int, + constraint cc check (a > 10) not enforced, + constraint cc_1 check (b < 17) enforced, + constraint cc_2 check( a < 15) not enforced +); +alter table parted_ch attach partition parted_ch_2 for values from (10) to (20); +insert into parted_ch values (1, 22), (9, 1), (16, 16); +alter table parted_ch alter constraint cc_1 enforced; --error +ERROR: check constraint "cc_1" of relation "parted_ch_11" is violated by some row +update parted_ch set b = 4 where b = 22; +alter table parted_ch alter constraint cc_1 enforced; --ok +create or replace 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 conname, conrelid::regclass::text collate "C"; +alter table parted_ch alter constraint cc not enforced; --no-op +alter table parted_ch alter constraint cc enforced; --error +ERROR: check constraint "cc" of relation "parted_ch_11" is violated by some row +delete from parted_ch where a = 1; +alter table parted_ch alter constraint cc enforced; --error +ERROR: check constraint "cc" of relation "parted_ch_11" is violated by some row +delete from parted_ch where a = 9; +alter table parted_ch alter constraint cc enforced; +--check these CHECK constraint status +select * from check_constraint_status; + conname | conrelid | conenforced | convalidated +---------+--------------+-------------+-------------- + cc | parted_ch | t | t + cc | parted_ch_1 | t | t + cc | parted_ch_11 | t | t + cc | parted_ch_12 | t | t + cc | parted_ch_2 | t | t + cc_1 | parted_ch | t | t + cc_1 | parted_ch_1 | t | t + cc_1 | parted_ch_11 | t | t + cc_1 | parted_ch_12 | t | t + cc_1 | parted_ch_2 | t | t + cc_2 | parted_ch_2 | f | f +(11 rows) + +alter table parted_ch_2 alter constraint cc_2 enforced; --error +ERROR: check constraint "cc_2" of relation "parted_ch_2" is violated by some row +delete from parted_ch where a = 16; +alter table parted_ch_2 alter constraint cc_2 enforced; +alter table parted_ch_2 alter constraint cc not enforced; +alter table parted_ch_2 alter constraint cc_1 not enforced; +alter table parted_ch_2 alter constraint cc_2 not enforced; +--check these CHECK constraint status again +select * from check_constraint_status; + conname | conrelid | conenforced | convalidated +---------+--------------+-------------+-------------- + cc | parted_ch | t | t + cc | parted_ch_1 | t | t + cc | parted_ch_11 | t | t + cc | parted_ch_12 | t | t + cc | parted_ch_2 | f | f + cc_1 | parted_ch | t | t + cc_1 | parted_ch_1 | t | t + cc_1 | parted_ch_11 | t | t + cc_1 | parted_ch_12 | t | t + cc_1 | parted_ch_2 | f | f + cc_2 | parted_ch_2 | f | f +(11 rows) + +drop table parted_ch; +drop view check_constraint_status; -- -- Primary keys -- @@ -746,8 +824,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 and check constraints 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 and check constraints 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 0490a746555..36f81f39265 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1421,11 +1421,54 @@ order by 1, 2; p1_c3 | inh_check_constraint9 | f | 2 | t | t (38 rows) +-- +-- CHECK constraints +-- ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED +alter table p1 drop constraint inh_check_constraint1; +alter table p1_c1 drop constraint inh_check_constraint1; +alter table only p1 alter constraint inh_check_constraint3 enforced; --error +ERROR: constraint must be altered on child tables too +HINT: Do not specify the ONLY keyword. +alter table only p1 alter constraint inh_check_constraint3 not enforced; --error +ERROR: constraint must be altered on child tables too +HINT: Do not specify the ONLY keyword. +insert into p1_c1 values(-2); +insert into p1_c3 values(-3); +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::text collate "C"; + 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 @@ -1434,6 +1477,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::text collate "C"; + 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 733a1dbccfe..9609a67dda0 100644 --- a/src/test/regress/sql/constraints.sql +++ b/src/test/regress/sql/constraints.sql @@ -266,6 +266,58 @@ COPY COPY_TBL FROM :'filename'; SELECT * FROM COPY_TBL; +-- +-- CHECK constraints +-- ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED +create table parted_ch( + a int, b int, + constraint cc check (a > 10) not enforced, + constraint cc_1 check (b < 17) not enforced +) partition by range(a); +create table parted_ch_1 partition of parted_ch for values from (0) to (10) partition by list(b); +create table parted_ch_11 partition of parted_ch_1 for values in (0, 1, 22,4); +create table parted_ch_12 partition of parted_ch_1 for values in (2); +create table parted_ch_2(b int, a int, + constraint cc check (a > 10) not enforced, + constraint cc_1 check (b < 17) enforced, + constraint cc_2 check( a < 15) not enforced +); +alter table parted_ch attach partition parted_ch_2 for values from (10) to (20); + +insert into parted_ch values (1, 22), (9, 1), (16, 16); + +alter table parted_ch alter constraint cc_1 enforced; --error +update parted_ch set b = 4 where b = 22; +alter table parted_ch alter constraint cc_1 enforced; --ok + +create or replace 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 conname, conrelid::regclass::text collate "C"; + +alter table parted_ch alter constraint cc not enforced; --no-op +alter table parted_ch alter constraint cc enforced; --error +delete from parted_ch where a = 1; +alter table parted_ch alter constraint cc enforced; --error +delete from parted_ch where a = 9; +alter table parted_ch alter constraint cc enforced; + +--check these CHECK constraint status +select * from check_constraint_status; + +alter table parted_ch_2 alter constraint cc_2 enforced; --error +delete from parted_ch where a = 16; +alter table parted_ch_2 alter constraint cc_2 enforced; +alter table parted_ch_2 alter constraint cc not enforced; +alter table parted_ch_2 alter constraint cc_1 not enforced; +alter table parted_ch_2 alter constraint cc_2 not enforced; + +--check these CHECK constraint status again +select * from check_constraint_status; +drop table parted_ch; +drop view check_constraint_status; + -- -- Primary keys -- diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql index 699e8ac09c8..8f986904389 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -510,6 +510,38 @@ select conrelid::regclass::text as relname, conname, conislocal, coninhcount, co from pg_constraint where conname like 'inh\_check\_constraint%' order by 1, 2; +-- +-- CHECK constraints +-- ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED +alter table p1 drop constraint inh_check_constraint1; +alter table p1_c1 drop constraint inh_check_constraint1; + +alter table only p1 alter constraint inh_check_constraint3 enforced; --error +alter table only p1 alter constraint inh_check_constraint3 not enforced; --error + +insert into p1_c1 values(-2); +insert into p1_c3 values(-3); + +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::text collate "C"; +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 +552,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::text collate "C"; drop table p1 cascade; create table p1(f1 int constraint p1_a_check check (f1 > 0) enforced); -- 2.34.1