Re: ALTER TABLE ALTER CONSTRAINT misleading error message - Mailing list pgsql-hackers

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