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