hi. 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.
From 486cf69121b0ea37a5c050bf1ceaa67196acd682 Mon Sep 17 00:00:00 2001 From: jian he <jian.universal...@gmail.com> Date: Mon, 2 Jun 2025 21:54:53 +0800 Subject: [PATCH v1 1/1] alter check constraint enforceability context: https://git.postgresql.org/cgit/postgresql.git/commit/?id=ca87c415e2fccf81cec6fd45698dde9fae0ab570 discussion: https://postgr.es/m/ --- doc/src/sgml/ref/alter_table.sgml | 4 +- src/backend/commands/tablecmds.c | 190 ++++++++++++++++++++-- src/test/regress/expected/constraints.out | 54 ++++++ src/test/regress/expected/inherit.out | 63 +++++++ src/test/regress/sql/constraints.sql | 37 +++++ src/test/regress/sql/inherit.sql | 45 +++++ 6 files changed, 375 insertions(+), 18 deletions(-) diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index d63f3a621ac..afb7b04aa69 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 acf11e83c04..9c1466041f9 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -403,6 +403,10 @@ static bool ATExecAlterConstrEnforceability(List **wqueue, ATAlterConstraint *cm Oid ReferencedParentUpdTrigger, Oid ReferencingParentInsTrigger, Oid ReferencingParentUpdTrigger); +static bool ATExecAlterCheckConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon, + Relation conrel, Oid conrelid, + 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 +425,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, @@ -431,7 +438,7 @@ static ObjectAddress ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName, bool recurse, bool recursing, LOCKMODE lockmode); static void QueueFKConstraintValidation(List **wqueue, Relation conrel, Relation rel, - HeapTuple contuple, LOCKMODE lockmode); + HeapTuple contuple, LOCKMODE lockmode, bool recursing); static void QueueCheckConstraintValidation(List **wqueue, Relation conrel, Relation rel, char *constrName, HeapTuple contuple, bool recurse, bool recursing, LOCKMODE lockmode); @@ -11867,7 +11874,7 @@ AttachPartitionForeignKey(List **wqueue, /* Use the same lock as for AT_ValidateConstraint */ QueueFKConstraintValidation(wqueue, conrel, partition, partcontup, - ShareUpdateExclusiveLock); + ShareUpdateExclusiveLock, false); ReleaseSysCache(partcontup); table_close(conrel, RowExclusiveLock); } @@ -12151,7 +12158,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. @@ -12213,11 +12220,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, @@ -12319,16 +12328,21 @@ 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, + currcon->conrelid, + contuple, recurse, false, lockmode)) + changed = true; else if (cmdcon->alterDeferrability && ATExecAlterConstrDeferrability(wqueue, cmdcon, conrel, tgrel, rel, @@ -12359,7 +12373,148 @@ 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 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, Oid conrelid, + 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); + + rel = table_open(currcon->conrelid, lockmode); + + /* + * 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); + + /* + * For CHECK constraints, we must ensure that we only mark the constraint as + * enforced on the parent if it's already enforced on the children. + * + * We recurse before changing enforceability on the parent, to reduce risk + * of deadlocks. + */ + 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; + newcon->refrelid = InvalidOid; + newcon->refindid = InvalidOid; + newcon->conid = currcon->oid; + + 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); + } + + if (currcon->conenforced != cmdcon->is_enforced) + { + AlterConstrUpdateConstraintEntry(cmdcon, conrel, contuple); + changed = true; + } + + 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, + conrelid, 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. @@ -12919,7 +13074,7 @@ ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName, { if (con->contype == CONSTRAINT_FOREIGN) { - QueueFKConstraintValidation(wqueue, conrel, rel, tuple, lockmode); + QueueFKConstraintValidation(wqueue, conrel, rel, tuple, lockmode, false); } else if (con->contype == CONSTRAINT_CHECK) { @@ -12953,7 +13108,7 @@ ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName, */ static void QueueFKConstraintValidation(List **wqueue, Relation conrel, Relation rel, - HeapTuple contuple, LOCKMODE lockmode) + HeapTuple contuple, LOCKMODE lockmode, bool recursing) { Form_pg_constraint con; AlteredTableInfo *tab; @@ -12983,8 +13138,11 @@ QueueFKConstraintValidation(List **wqueue, Relation conrel, Relation rel, newcon->qual = (Node *) fkconstraint; /* Find or create work queue entry for this table */ - tab = ATGetQueueEntry(wqueue, rel); - tab->constraints = lappend(tab->constraints, newcon); + if (!recursing) + { + tab = ATGetQueueEntry(wqueue, rel); + tab->constraints = lappend(tab->constraints, newcon); + } } /* @@ -13024,7 +13182,7 @@ QueueFKConstraintValidation(List **wqueue, Relation conrel, Relation rel, childrel = table_open(childcon->conrelid, lockmode); QueueFKConstraintValidation(wqueue, conrel, childrel, childtup, - lockmode); + lockmode, true); table_close(childrel, NoLock); } diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out index ad6aaab7385..8e25ca4858c 100644 --- a/src/test/regress/expected/constraints.out +++ b/src/test/regress/expected/constraints.out @@ -390,6 +390,58 @@ SELECT * FROM COPY_TBL; 6 | OK | 4 (2 rows) +-- +-- CHECK constraints +-- ALTER TABLE ALTER CONSTRAINT [NO] 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); +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 conname, conrelid::regclass, conenforced, convalidated +from pg_constraint +where conrelid::regclass::text ~* '^parted_ch' and conname = 'cc' +order by conrelid::regclass, conname; + 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 +(5 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 +select conname, conrelid::regclass, conenforced, convalidated +from pg_constraint +where conrelid::regclass::text ~* '^parted_ch' and contype = 'c' +order by conrelid::regclass, conname; + 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 +798,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 DROP TABLE unique_tbl; -- -- EXCLUDE constraints diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index f9b0c415cfd..a24fe7962d5 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1418,11 +1418,55 @@ order by 1, 2; p1_c3 | inh_check_constraint9 | f | 2 | t | t (38 rows) +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 +--ALTER TABLE ALTER CONSTRAINT +--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); +insert into p1 values(-1); +alter table p1 alter constraint p1_a_check not enforced; --ok +alter table p1 alter constraint p1_a_check enforced; --error +ERROR: check constraint "p1_a_check" of relation "p1" is violated by some row +delete from only p1 where f1 = -1; +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 +1475,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 337baab7ced..4a540120005 100644 --- a/src/test/regress/sql/constraints.sql +++ b/src/test/regress/sql/constraints.sql @@ -266,6 +266,43 @@ COPY COPY_TBL FROM :'filename'; SELECT * FROM COPY_TBL; +-- +-- CHECK constraints +-- ALTER TABLE ALTER CONSTRAINT [NO] 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); + +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 conname, conrelid::regclass, conenforced, convalidated +from pg_constraint +where conrelid::regclass::text ~* '^parted_ch' and conname = 'cc' +order by conrelid::regclass, conname; + +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 +select conname, conrelid::regclass, conenforced, convalidated +from pg_constraint +where conrelid::regclass::text ~* '^parted_ch' and contype = 'c' +order by conrelid::regclass, conname; + +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..0f505eadc85 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -510,6 +510,40 @@ select conrelid::regclass::text as relname, conname, conislocal, coninhcount, co from pg_constraint where conname like 'inh\_check\_constraint%' order by 1, 2; + +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; + +--ALTER TABLE ALTER CONSTRAINT +--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); +insert into p1 values(-1); +alter table p1 alter constraint p1_a_check not enforced; --ok +alter table p1 alter constraint p1_a_check enforced; --error +delete from only p1 where f1 = -1; +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 +554,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
