On Mon, Dec 15, 2025 at 7:49 PM Amul Sul <[email protected]> wrote: > > > v5-0002: alter check constraint enforceability > > > The patch also looks good, but I have a minor comment for the test -- > you created the check_constraint_status view, which is not dropped, it > should be dropped at the end. Also, instead of a view, I think you > could use the \set psql-meta-command; for example, see the > init_range_parted or show_data tests in update.sql > > Also, run pgindent on both patches. >
i have tried using \set, but it seems to require the query within a single line. since the view check_constraint_status definition is quite longer, \set would make it less readable, so I choose to use view. previously I use + newcon = (NewConstraint *) palloc0(sizeof(NewConstraint)); now use + newcon = palloc0_object(NewConstraint); v6-0001, v6-0002 both indented properly via pgindent, also polished the commit messages. -- jian https://www.enterprisedb.com/
From 93d679d9f2b9fe7bdbec511d19211158312ff4f3 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Tue, 16 Dec 2025 12:28:46 +0800 Subject: [PATCH v6 1/2] rename alter constraint enforceability related functions The functions AlterConstrEnforceabilityRecurse and ATExecAlterConstrEnforceability are being renamed to AlterFKConstrEnforceabilityRecurse and ATExecAlterFKConstrEnforceability, respectively. The current alter constraint functions only handle Foreign Key constraints. Renaming them to be more explicit about the constraint type is necessary; otherwise, it will cause confusion when we later introduce the ability to alter the enforceability of other constraints. Author: jian he <[email protected]> Reviewed-by: Amul Sul <[email protected]> Reviewed-by: Kirill Reshke <[email protected]>, Reviewed-by: Robert Treat <[email protected]> (XXX delete this line later) commitfest: https://commitfest.postgresql.org/patch/5796 discussion: https://postgr.es/m/cacjufxhch_fu-fsewscvg9mn6-5tzr6h9ntn+0kugtcaerd...@mail.gmail.com --- src/backend/commands/tablecmds.c | 119 ++++++++++++++++--------------- 1 file changed, 60 insertions(+), 59 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 6b1a00ed477..dc906a16489 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -396,14 +396,14 @@ 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 ATExecAlterConstrDeferrability(List **wqueue, ATAlterConstraint *cmdcon, Relation conrel, Relation tgrel, Relation rel, HeapTuple contuple, bool recurse, @@ -414,14 +414,14 @@ 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 AlterConstrDeferrabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon, Relation conrel, Relation tgrel, Relation rel, HeapTuple contuple, bool recurse, @@ -12385,15 +12385,15 @@ 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 - * automatically. + * 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)) + ATExecAlterFKConstrEnforceability(wqueue, cmdcon, conrel, tgrel, + currcon->conrelid, currcon->confrelid, + contuple, lockmode, InvalidOid, + InvalidOid, InvalidOid, InvalidOid)) changed = true; else if (cmdcon->alterDeferrability && @@ -12425,7 +12425,7 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon, } /* - * Returns true if the constraint's enforceability is altered. + * 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. @@ -12437,14 +12437,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; @@ -12480,10 +12480,10 @@ 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, - fkrelid, pkrelid, contuple, - lockmode, InvalidOid, InvalidOid, - InvalidOid, InvalidOid); + AlterFKConstrEnforceabilityRecurse(wqueue, cmdcon, conrel, tgrel, + fkrelid, pkrelid, contuple, + lockmode, InvalidOid, InvalidOid, + InvalidOid, InvalidOid); /* Drop all the triggers */ DropForeignKeyConstraintTriggers(tgrel, conoid, InvalidOid, InvalidOid); @@ -12559,12 +12559,13 @@ 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, - fkrelid, pkrelid, contuple, - lockmode, ReferencedDelTriggerOid, - ReferencedUpdTriggerOid, - ReferencingInsTriggerOid, - ReferencingUpdTriggerOid); + AlterFKConstrEnforceabilityRecurse(wqueue, cmdcon, conrel, tgrel, + fkrelid, pkrelid, contuple, + lockmode, + ReferencedDelTriggerOid, + ReferencedUpdTriggerOid, + ReferencingInsTriggerOid, + ReferencingUpdTriggerOid); } table_close(rel, NoLock); @@ -12777,25 +12778,25 @@ AlterConstrTriggerDeferrability(Oid conoid, Relation tgrel, Relation rel, } /* - * Invokes ATExecAlterConstrEnforceability for each constraint that is a child of - * the specified constraint. + * Invokes ATExecAlterFKConstrEnforceability for each foreign key constraint + * that is a child of the specified constraint. * * Note that this doesn't handle recursion the normal way, viz. by scanning the * list of child relations and recursing; instead it uses the conparentid * 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; @@ -12815,12 +12816,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); } -- 2.34.1
From ba246abdb3df94e3e4f44d4301596890f59d413f Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> 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 <[email protected]> Reviewed-by: Amul Sul <[email protected]> Reviewed-by: Kirill Reshke <[email protected]>, Reviewed-by: Robert Treat <[email protected]> (XXX delete this line later) commitfest: https://commitfest.postgresql.org/patch/5796 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 | 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 <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 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
