Re: Alter or rename enum value

Besides not being able to rename enum values there are two other
limitations regarding enums which would be nice to get finally fixed:

1) There is also no possibility to drop a value.

2) Quoting the docs (
http://www.postgresql.org/docs/9.5/static/sql-altertype.html):
"ALTER TYPE ... ADD VALUE (the form that adds a new value to an enum type)
cannot be executed inside a transaction block." Example:
# CREATE TYPE bogus AS ENUM('good');
CREATE TYPE
# BEGIN;
BEGIN
# ALTER TYPE bogus ADD VALUE 'bad';
ERROR: ALTER TYPE ... ADD cannot run inside a transaction block

To summarize it:
For enums to finally be really usable it would nice if we would have (or
similiar):
ALTER TYPE name DROP VALUE [ IF EXISTS ] enum_value
and
ALTER TYPE name RENAME VALUE [ IF EXISTS ] old_enum_value_name TO
new_enum_value_name

And all of the operations (adding, renaming, dropping) should also work
when done within a new transaction on an enum that existed before that
transaction.

I did some digging and maybe following commits are useful in this context:
7b90469b71761d240bf5efe3ad5bbd228429278e
c9e2e2db5c2090a880028fd8c1debff474640f50

Also there are these discussions where some of the messages contain some
useful information:
http://www.postgresql.org/message-id/29F36C7C98AB09499B1A209D48EAA615B7653DBC8A@mail2a.alliedtesting.com
http://www.postgresql.org/message-id/50324F26.3090809@dunslane.net
http://www.postgresql.org/message-id/20130819122938.GB8558@alap2.anarazel.de

Also have a look at this workaround:
http://en.dklab.ru/lib/dklab_postgresql_enum/

How high is the chance that given the above information someone will tackle
these 3 issues/requests in the near future? It seems there were some
internal chances since the introduction of enums in 8.x so maybe this
changes wouldn't be that disruptive anymore?

Regards,
Matthias

On 9 March 2016 at 18:13, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> > On 03/09/2016 11:07 AM, Tom Lane wrote:
> >> I have a vague recollection that we discussed this at the time the enum
> >> stuff went in, and there are concurrency issues? Don't recall details
> >> though.
>
> > Rings a vague bell, but should it be any worse than adding new labels?
>
> I think what I was recalling is the hazards discussed in the comments for
> RenumberEnumType. However, the problem there is that a backend could make
> inconsistent ordering decisions due to seeing two different pg_enum rows
> under different snapshots. Updating a single row to change its name
> doesn't seem to have a comparable hazard, and it wouldn't affect ordering
> anyway. So it's probably no worse than any other object-rename situation.
>
> regards, tom lane
>