Postgres Pro
Downloads
Home   >   mailing lists

Thread: drop column name conflict

drop column name conflict

From
Joseph Koshakow
Date:
Hi all,

There's a rare edge case in `alter table` that can prevent users from
dropping a column as shown below

    # create table atacc1(a int, "........pg.dropped.1........" int);
    CREATE TABLE
    # alter table atacc1 drop column a;
    ERROR:  duplicate key value violates unique constraint "pg_attribute_relid_attnam_index"
    DETAIL:  Key (attrelid, attname)=(16407, ........pg.dropped.1........) already exists.

It seems a bit silly and unlikely that anyone would ever find
themselves in this scenario, but it also seems easy enough to fix as
shown by the attached patch.

Does anyone think this is worth fixing? If so I can submit it to the
current commitfest.

Thanks,
Joe Koshakow
Attachment

Re: drop column name conflict

From
Tom Lane
Date:
Joseph Koshakow <koshy44@gmail.com> writes:
> There's a rare edge case in `alter table` that can prevent users from
> dropping a column as shown below

>     # create table atacc1(a int, "........pg.dropped.1........" int);
>     CREATE TABLE
>     # alter table atacc1 drop column a;
>     ERROR:  duplicate key value violates unique constraint
> "pg_attribute_relid_attnam_index"
>     DETAIL:  Key (attrelid, attname)=(16407, ........pg.dropped.1........)
> already exists.

I think we intentionally did not bother with preventing this,
on the grounds that if you were silly enough to name a column
that way then you deserve any ensuing problems.

If we were going to expend any code on the scenario, I'd prefer
to make it be checks in column addition/renaming that disallow
naming a column this way.  What you propose here doesn't remove
the fundamental tension about whether this is valid user namespace
or not, it just makes things less predictable.

            regards, tom lane

Re: drop column name conflict

From
Joseph Koshakow
Date:
On Sat, May 4, 2024 at 11:29 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I think we intentionally did not bother with preventing this,
> on the grounds that if you were silly enough to name a column
> that way then you deserve any ensuing problems.

Fair enough.

> If we were going to expend any code on the scenario, I'd prefer
> to make it be checks in column addition/renaming that disallow
> naming a column this way.

Is there any interest in making this change? The attached patch could
use some cleanup, but seems to accomplish what's described. It's
definitely more involved than the previous one and may not be worth the
effort. If you feel that it's worth it I can clean it up, otherwise
I'll drop it.

Thanks,
Joe Koshakow
Attachment