Re: ALTER TABLE ALTER CONSTRAINT misleading error message

On Wed, May 28, 2025 at 7:59 PM Álvaro Herrera <alvherre(at)kurilemu(dot)de> 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+giyQ@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.