public inbox for [email protected]
help / color / mirror / Atom feedFrom: jian he <[email protected]>
To: PostgreSQL-development <[email protected]>
Subject: alter check constraint enforceability
Date: Mon, 2 Jun 2025 21:57:33 +0800
Message-ID: <CACJufxHCh_FU-FsEwsCvg9mN6-5tzR6H9ntn+0KUgTCaerDOmg@mail.gmail.com> (raw)
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.
Attachments:
[text/x-patch] v1-0001-alter-check-constraint-enforceability.patch (25.2K, 2-v1-0001-alter-check-constraint-enforceability.patch)
download | inline diff:
From 486cf69121b0ea37a5c050bf1ceaa67196acd682 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
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
view thread (13+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: alter check constraint enforceability
In-Reply-To: <CACJufxHCh_FU-FsEwsCvg9mN6-5tzR6H9ntn+0KUgTCaerDOmg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox