On Wed, May 28, 2025 at 7:59 PM Álvaro Herrera <[email protected]> wrote:
>
> On 2025-May-28, jian he wrote:
>
> > hi.
> >
> > create table t(a int, constraint cc check(a = 1));
> > ALTER TABLE t ALTER CONSTRAINT cc not valid;
> > ERROR: FOREIGN KEY constraints cannot be marked NOT VALID
> > LINE 1: ALTER TABLE t ALTER CONSTRAINT cc not valid;
> > ^
> >
> > the error message seems misleading,
>
> We discussed this already, didn't we? There's a thread with IIRC three
> proposed patches for this. I think I liked this one the most:
>
> https://postgr.es/m/caaj_b97hd-jmts7ajgu6tdbczdx_kyukxg+k-dtymoieg+g...@mail.gmail.com
>
for ALTER CONSTRAINT,
we already handled most error cases in ATExecAlterConstraint.
if (cmdcon->alterDeferrability && currcon->contype != CONSTRAINT_FOREIGN)
ereport(ERROR,
(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)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("cannot alter enforceability of constraint
\"%s\" of relation \"%s\"",
cmdcon->conname, RelationGetRelationName(rel))));
if (cmdcon->alterInheritability &&
currcon->contype != CONSTRAINT_NOTNULL)
ereport(ERROR,
errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("constraint \"%s\" of relation \"%s\" is not a
not-null constraint",
cmdcon->conname, RelationGetRelationName(rel)));
but ATExecAlterConstraint didn't handle "ALTER CONSTRAINT NOT VALID",
it was handled in processCASbits.
so the attached minimum patch (extract from v2-0001-trial.patch)
is fine for PG18, IMHO.
From 4f5f32073d0bbb03effbaae2420f616f70362050 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 2 Jun 2025 10:23:13 +0800
Subject: [PATCH v1 1/1] disallow ALTER CONSTRAINT NOT VALID
discussion: https://postgr.es/m/
---
src/backend/parser/gram.y | 9 ++++++++-
src/test/regress/expected/foreign_key.out | 2 +-
2 files changed, 9 insertions(+), 2 deletions(-)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0b5652071d1..a1fe5fab1fa 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -2669,7 +2669,14 @@ alter_table_cmd:
c->alterDeferrability = true;
if ($4 & CAS_NO_INHERIT)
c->alterInheritability = true;
- processCASbits($4, @4, "FOREIGN KEY",
+
+ if ($4 & CAS_NOT_VALID)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter constraint validity"),
+ parser_errposition(@4));
+
+ processCASbits($4, @4, NULL,
&c->deferrable,
&c->initdeferred,
&c->is_enforced,
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 4f3f280a439..60c617e4fe4 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -1359,7 +1359,7 @@ LINE 1: ...e ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE INITIALLY ...
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NO INHERIT;
ERROR: constraint "fktable_fk_fkey" of relation "fktable" is not a not-null constraint
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT VALID;
-ERROR: FOREIGN KEY constraints cannot be marked NOT VALID
+ERROR: cannot alter constraint validity
LINE 1: ...ER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT VALID;
^
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey ENFORCED NOT ENFORCED;
--
2.34.1
