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@sss.pgh.pa.us> wrote:

Andrew Dunstan <andrew@dunslane.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