Re: ALTER TABLE ALTER CONSTRAINT misleading error message

From: Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com>
To: jian he <jian(dot)universality(at)gmail(dot)com>, Álvaro Herrera <alvherre(at)kurilemu(dot)de>
Cc: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: ALTER TABLE ALTER CONSTRAINT misleading error message
Date: 2025-06-11 14:20:04
Message-ID: 0fd9bc9f-db03-4f0b-9d99-5a5d229b7325@oss.nttdata.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On 2025/06/02 12:13, jian he wrote:
> 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,

I also ran into this issue while testing constraints with NOT VALID.

>> 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.

+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter constraint validity"),

Since ALTER TABLE ... ALTER CONSTRAINT ... NOT VALID isn't supported,
how about making the error message more specific? For example:

"ALTER TABLE ... ALTER CONSTRAINT ... NOT VALID is not supported"

This would make it clearer to users what exactly isn't allowed.

Regards,

--
Fujii Masao
NTT DATA Japan Corporation

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Nathan Bossart 2025-06-11 14:23:44 Re: add function for creating/attaching hash table in DSM registry
Previous Message Rahila Syed 2025-06-11 14:18:50 Re: add function for creating/attaching hash table in DSM registry