On Fri, Nov 7, 2025 at 7:29 AM Robert Treat <[email protected]> wrote: > > > Hi! > > I looked at v3. > > > > Should we rename `ATExecAlterConstrEnforceability` to > > `ATExecAlterFKConstrEnforceability `? > > > > +1 > > Robert Treat > https://xzilla.net
hi. AlterConstrEnforceabilityRecurse renamed to AlterFKConstrEnforceabilityRecurse ATExecAlterConstrEnforceability renamed to ATExecAlterFKConstrEnforceability. There seem to be no tests for cases where a partitioned table’s check constraint is not enforced, but the partition’s constraint is enforced. I’ve added tests for this case. ATExecAlterCheckConstrEnforceability ``rel = table_open(currcon->conrelid, NoLock);`` NoLock is ok, because parent is already locked, obviously, ``find_all_inheritors(RelationGetRelid(rel), lockmode, NULL); `` will lock all the children with lockmode. -- jian https://www.enterprisedb.com
From 97e7ac0688464b637f539b7df43d84ff68109548 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Thu, 4 Dec 2025 14:39:44 +0800 Subject: [PATCH v4 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 | 274 +++++++++++++++++----- src/test/regress/expected/constraints.out | 79 +++++++ src/test/regress/expected/inherit.out | 62 +++++ src/test/regress/sql/constraints.sql | 51 ++++ src/test/regress/sql/inherit.sql | 43 ++++ 6 files changed, 454 insertions(+), 59 deletions(-) diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 9d23ad5a0fb..faa231f93e4 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -569,8 +569,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 07e5b95782e..5be23ab95b2 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -396,14 +396,17 @@ static ObjectAddress ATExecAlterConstraint(List **wqueue, Relation rel, static bool ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon, Relation conrel, Relation tgrel, Relation rel, HeapTuple contuple, bool recurse, LOCKMODE lockmode); -static bool ATExecAlterConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon, - Relation conrel, Relation tgrel, - Oid fkrelid, Oid pkrelid, - HeapTuple contuple, LOCKMODE lockmode, - Oid ReferencedParentDelTrigger, - Oid ReferencedParentUpdTrigger, - Oid ReferencingParentInsTrigger, - Oid ReferencingParentUpdTrigger); +static bool ATExecAlterFKConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon, + Relation conrel, Relation tgrel, + Oid fkrelid, Oid pkrelid, + HeapTuple contuple, LOCKMODE lockmode, + Oid ReferencedParentDelTrigger, + 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, @@ -414,14 +417,17 @@ static bool ATExecAlterConstrInheritability(List **wqueue, ATAlterConstraint *cm static void AlterConstrTriggerDeferrability(Oid conoid, Relation tgrel, Relation rel, bool deferrable, bool initdeferred, List **otherrelids); -static void AlterConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon, - Relation conrel, Relation tgrel, - Oid fkrelid, Oid pkrelid, - HeapTuple contuple, LOCKMODE lockmode, - Oid ReferencedParentDelTrigger, - Oid ReferencedParentUpdTrigger, - Oid ReferencingParentInsTrigger, - Oid ReferencingParentUpdTrigger); +static void AlterFKConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon, + Relation conrel, Relation tgrel, + Oid fkrelid, Oid pkrelid, + HeapTuple contuple, LOCKMODE lockmode, + Oid ReferencedParentDelTrigger, + 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, @@ -12181,7 +12187,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. @@ -12243,11 +12249,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 and check constraints can change enforceability")); if (cmdcon->alterInheritability && currcon->contype != CONSTRAINT_NOTNULL) ereport(ERROR, @@ -12349,17 +12357,27 @@ 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 && - ATExecAlterConstrEnforceability(wqueue, cmdcon, conrel, tgrel, - currcon->conrelid, currcon->confrelid, - contuple, lockmode, InvalidOid, - InvalidOid, InvalidOid, InvalidOid)) - changed = true; - + if (cmdcon->alterEnforceability) + { + if (currcon->contype == CONSTRAINT_FOREIGN) + { + ATExecAlterFKConstrEnforceability(wqueue, cmdcon, conrel, tgrel, + currcon->conrelid, currcon->confrelid, + contuple, lockmode, InvalidOid, + InvalidOid, InvalidOid, InvalidOid); + changed = true; + } + 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, contuple, recurse, &otherrelids, @@ -12389,7 +12407,149 @@ 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. + * + * conrel is the pg_constraint catalog relation. + */ +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 called, 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; + } + + 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. + * This is needed only 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 = (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. @@ -12401,14 +12561,14 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon, * enforced, as descendant constraints cannot be different in that case. */ static bool -ATExecAlterConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon, - Relation conrel, Relation tgrel, - Oid fkrelid, Oid pkrelid, - HeapTuple contuple, LOCKMODE lockmode, - Oid ReferencedParentDelTrigger, - Oid ReferencedParentUpdTrigger, - Oid ReferencingParentInsTrigger, - Oid ReferencingParentUpdTrigger) +ATExecAlterFKConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon, + Relation conrel, Relation tgrel, + Oid fkrelid, Oid pkrelid, + HeapTuple contuple, LOCKMODE lockmode, + Oid ReferencedParentDelTrigger, + Oid ReferencedParentUpdTrigger, + Oid ReferencingParentInsTrigger, + Oid ReferencingParentUpdTrigger) { Form_pg_constraint currcon; Oid conoid; @@ -12444,7 +12604,7 @@ ATExecAlterConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon, */ if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE || get_rel_relkind(currcon->confrelid) == RELKIND_PARTITIONED_TABLE) - AlterConstrEnforceabilityRecurse(wqueue, cmdcon, conrel, tgrel, + AlterFKConstrEnforceabilityRecurse(wqueue, cmdcon, conrel, tgrel, fkrelid, pkrelid, contuple, lockmode, InvalidOid, InvalidOid, InvalidOid, InvalidOid); @@ -12523,7 +12683,7 @@ ATExecAlterConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon, */ if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE || get_rel_relkind(currcon->confrelid) == RELKIND_PARTITIONED_TABLE) - AlterConstrEnforceabilityRecurse(wqueue, cmdcon, conrel, tgrel, + AlterFKConstrEnforceabilityRecurse(wqueue, cmdcon, conrel, tgrel, fkrelid, pkrelid, contuple, lockmode, ReferencedDelTriggerOid, ReferencedUpdTriggerOid, @@ -12741,7 +12901,7 @@ AlterConstrTriggerDeferrability(Oid conoid, Relation tgrel, Relation rel, } /* - * Invokes ATExecAlterConstrEnforceability for each constraint that is a child of + * Invokes ATExecAlterFKConstrEnforceability for each constraint that is a child of * the specified constraint. * * Note that this doesn't handle recursion the normal way, viz. by scanning the @@ -12749,17 +12909,17 @@ AlterConstrTriggerDeferrability(Oid conoid, Relation tgrel, Relation rel, * relationships. This may need to be reconsidered. * * The arguments to this function have the same meaning as the arguments to - * ATExecAlterConstrEnforceability. + * ATExecAlterFKConstrEnforceability. */ static void -AlterConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon, - Relation conrel, Relation tgrel, - Oid fkrelid, Oid pkrelid, - HeapTuple contuple, LOCKMODE lockmode, - Oid ReferencedParentDelTrigger, - Oid ReferencedParentUpdTrigger, - Oid ReferencingParentInsTrigger, - Oid ReferencingParentUpdTrigger) +AlterFKConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon, + Relation conrel, Relation tgrel, + Oid fkrelid, Oid pkrelid, + HeapTuple contuple, LOCKMODE lockmode, + Oid ReferencedParentDelTrigger, + Oid ReferencedParentUpdTrigger, + Oid ReferencingParentInsTrigger, + Oid ReferencingParentUpdTrigger) { Form_pg_constraint currcon; Oid conoid; @@ -12779,12 +12939,12 @@ AlterConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon, true, NULL, 1, &pkey); while (HeapTupleIsValid(childtup = systable_getnext(pscan))) - ATExecAlterConstrEnforceability(wqueue, cmdcon, conrel, tgrel, fkrelid, - pkrelid, childtup, lockmode, - ReferencedParentDelTrigger, - ReferencedParentUpdTrigger, - ReferencingParentInsTrigger, - ReferencingParentUpdTrigger); + ATExecAlterFKConstrEnforceability(wqueue, cmdcon, conrel, tgrel, fkrelid, + pkrelid, childtup, lockmode, + ReferencedParentDelTrigger, + ReferencedParentUpdTrigger, + ReferencingParentInsTrigger, + ReferencingParentUpdTrigger); systable_endscan(pscan); } diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out index 1bbf59cca02..1d206b8512f 100644 --- a/src/test/regress/expected/constraints.out +++ b/src/test/regress/expected/constraints.out @@ -390,6 +390,83 @@ 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; -- -- Primary keys -- @@ -746,8 +823,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..514ef4b2a50 100644 --- a/src/test/regress/sql/constraints.sql +++ b/src/test/regress/sql/constraints.sql @@ -266,6 +266,57 @@ 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; + -- -- 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
