public inbox for [email protected]
help / color / mirror / Atom feedFrom: jian he <[email protected]>
To: Robert Treat <[email protected]>
Cc: Kirill Reshke <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Subject: Re: alter check constraint enforceability
Date: Thu, 4 Dec 2025 14:51:54 +0800
Message-ID: <CACJufxFsSO7yA0orXwwRROXePeW-qSEOuJbRGHFFZLy8e6pLzA@mail.gmail.com> (raw)
In-Reply-To: <CAJSLCQ0rgBK5cgXZzZSHnyBWi95Ngkky8+c1Jf_S0z_x4x6Zrw@mail.gmail.com>
References: <CACJufxHCh_FU-FsEwsCvg9mN6-5tzR6H9ntn+0KUgTCaerDOmg@mail.gmail.com>
<CACJufxFJFpU0V6zW6nic8_K9NOZqu1g5MOuuSRPTJ9xSAmeNrw@mail.gmail.com>
<CABV9wwNF45wmhs3gFH7qcyEVQjREQL1y=2ARzLgY__fNT2tWpw@mail.gmail.com>
<CACJufxFY=GkptsiH8X=VdyYAnNE_=u5j6WyDufTste6CuzRvnw@mail.gmail.com>
<CALdSSPg7x+zfKaCkq_bBbz7q8nYu93G3-tK+3si_kPwOcnU0iw@mail.gmail.com>
<CAJSLCQ0rgBK5cgXZzZSHnyBWi95Ngkky8+c1Jf_S0z_x4x6Zrw@mail.gmail.com>
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
Attachments:
[text/x-patch] v4-0001-alter-check-constraint-enforceability.patch (30.2K, 2-v4-0001-alter-check-constraint-enforceability.patch)
download | inline diff:
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+0KUgTCaerDOmg@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
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], [email protected], [email protected]
Subject: Re: alter check constraint enforceability
In-Reply-To: <CACJufxFsSO7yA0orXwwRROXePeW-qSEOuJbRGHFFZLy8e6pLzA@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