Alter or rename enum value

On 03/09/2016 09:56 AM, Matthias Kurz wrote:

Hi!

Right now it is not possible to rename an enum value.
Are there plans to implement this anytime soon?
I had a bit of a discussion on the IRC channel and it seems it
shouldn't be that hard to implement this.
Again, I am talking about renaming the values, not the enum itself.

I don't know of any plans, but it would be a useful thing. I agree it
wouldn't be too hard. The workaround is to do an update on pg_enum
directly, but proper SQL support would be much nicer.

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Andrew Dunstan <andrew@dunslane.net> writes:

On 03/09/2016 09:56 AM, Matthias Kurz wrote:

Right now it is not possible to rename an enum value.
Are there plans to implement this anytime soon?

I don't know of any plans, but it would be a useful thing. I agree it
wouldn't be too hard. The workaround is to do an update on pg_enum
directly, but proper SQL support would be much nicer.

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.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 03/09/2016 11:07 AM, Tom Lane wrote:

Andrew Dunstan <andrew@dunslane.net> writes:

On 03/09/2016 09:56 AM, Matthias Kurz wrote:

Right now it is not possible to rename an enum value.
Are there plans to implement this anytime soon?

I don't know of any plans, but it would be a useful thing. I agree it
wouldn't be too hard. The workaround is to do an update on pg_enum
directly, but proper SQL support would be much nicer.

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?

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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:
/messages/by-id/29F36C7C98AB09499B1A209D48EAA615B7653DBC8A@mail2a.alliedtesting.com
/messages/by-id/50324F26.3090809@dunslane.net
/messages/by-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:

On 9 March 2016 at 20:19, Matthias Kurz <m.kurz@irregular.at> wrote:

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:

/messages/by-id/29F36C7C98AB09499B1A209D48EAA615B7653DBC8A@mail2a.alliedtesting.com
/messages/by-id/50324F26.3090809@dunslane.net

/messages/by-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

Is there a way or a procedure we can go through to make the these ALTER
TYPE enhancements a higher priority? How do you choose which
features/enhancements to implement (next)?

Matthias Kurz <m.kurz@irregular.at> writes:

[altering and dropping enum values]

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

Is there a way or a procedure we can go through to make the these ALTER
TYPE enhancements a higher priority? How do you choose which
features/enhancements to implement (next)?

I was bored and thought "how hard could it be?", and a few hours'
hacking later, I have something that seems to work. It doesn't do IF
NOT EXISTS yet, and the error messaging could do with some improvement,
and there are no docs. The patch is attached, as well as at
https://github.com/ilmari/postgres/commit/enum-alter-value

ilmari@ilmari.org (Dagfinn Ilmari Mannsåker) writes:

I was bored and thought "how hard could it be?", and a few hours'
hacking later, I have something that seems to work. It doesn't do IF
NOT EXISTS yet, and the error messaging could do with some improvement,
and there are no docs. The patch is attached, as well as at
https://github.com/ilmari/postgres/commit/enum-alter-value

I've added it to the 2016-09 commitfest as well:
https://commitfest.postgresql.org/10/588/

--
"A disappointingly low fraction of the human race is,
at any given time, on fire." - Stig Sandbeck Mathisen

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

ilmari@ilmari.org (Dagfinn Ilmari Mannsåker) writes:

I was bored and thought "how hard could it be?", and a few hours'
hacking later, I have something that seems to work. It doesn't do IF
NOT EXISTS yet, and the error messaging could do with some improvement,
and there are no docs. The patch is attached, as well as at
https://github.com/ilmari/postgres/commit/enum-alter-value

I've added it to the 2016-09 commitfest as well:
https://commitfest.postgresql.org/10/588/

Nice! Thank you!

Actually you still miss a "DROP VALUE" action. Also please make sure this
also works when altering an existing enum within a new transaction -
otherwise it does not really make sense (Usually someone wants to alter
existing enums, not ones that have just been created).

As a result a script like this should pass without problems:
-- ### script start
CREATE TYPE bogus AS ENUM('dog');

-- TEST 1:
BEGIN;
ALTER TYPE bogus ADD VALUE 'cat'; -- fails in 9.5 because of the
transaction but should work in future
COMMIT;

-- TEST 2:
BEGIN;
ALTER TYPE bogus RENAME TO bogon;
ALTER TYPE bogon ADD VALUE 'horse'; -- fails in 9.5 because of the
transaction but should work in future
COMMIT;

-- TEST 3:
BEGIN;
ALTER TYPE bogon ALTER VALUE 'dog' TO 'pig'; -- not implemented in 9.5 but
should work in future
ROLLBACK;

-- TEST 4:
BEGIN;
ALTER TYPE bogon DROP VALUE 'cat'; -- not implemented in 9.5 but should
work in future
ROLLBACK;
-- ### script end

End result of enum "bogon" (which was named "bogus" at the beginning of the
script):
-- ###
pig
horse
-- ###

Thank you!

On 3/24/16 2:00 PM, Matthias Kurz wrote:

ALTER TYPE bogon DROP VALUE 'cat'; -- not implemented in 9.5 but should
work in future
ROLLBACK;

Dropping a value is significantly harder because that value could be in use.

I'm certain there's a really good reason adding new values isn't allowed
inside of a transaction. It's probably documented in the code.

To answer your question about "what goes into a release", there's really
no process for that. What goes into a release is what someone was
interested enough in to get community approval for the idea, write the
patch, and shepard the patch through the review process. So if you want
these features added, you need to either: do it yourself, convince
someone else to do it for free, or pay someone to do it for you.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Jim Nasby <Jim.Nasby@BlueTreble.com> writes:

I'm certain there's a really good reason adding new values isn't allowed
inside of a transaction. It's probably documented in the code.

Yes, see AlterEnum():

* Ordinarily we disallow adding values within transaction blocks, because
* we can't cope with enum OID values getting into indexes and then having
* their defining pg_enum entries go away. However, it's okay if the enum
* type was created in the current transaction, since then there can be no
* such indexes that wouldn't themselves go away on rollback. (We support
* this case because pg_dump --binary-upgrade needs it.)

Deleting an enum value is similarly problematic. Let's assume you're
willing to take out sufficiently widespread locks to prevent entry of
any new rows containing the doomed enum value (which, in reality, is
pretty much unworkable in production situations). Let's assume that
you're willing to hold those locks long enough to VACUUM away every
existing dead row containing that value (see previous parenthetical
comment, squared). You're still screwed, because there might be
instances of the to-be-deleted value sitting in upper levels of btree
indexes (or other index types). There is no mechanism for getting
rid of those, short of a full index rebuild; and you cannot remove
the pg_enum entry without breaking such indexes.

It's conceivable that we could do something like adding an "isdead"
column to pg_enum and making enum_in reject new values that're marked
isdead. But I can't see that we'd ever be able to support true
removal of an enum value at reasonable cost. And I'm not really sure
where the use-case argument is for working hard on it.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

It's conceivable that we could do something like adding an "isdead"
column to pg_enum and making enum_in reject new values that're marked
isdead. But I can't see that we'd ever be able to support true
removal of an enum value at reasonable cost. And I'm not really sure
where the use-case argument is for working hard on it.

Thanks for all your explanation!
We work a lot with enums and sometimes there are cases where we would like
to be able to add a new value or rename an existing value (in a new
transaction) - dropping isn't needed that much, but would be a bonus.
Right now you have to know which enum values you will use at the time
creating a table - but as many things change also requirements for a
database/schema/table/enum definition change. At the moment we have to use
ugly hacks to make renaming/dropping work.
I didn't know implementing these features would be that complex. As I am
not familiar with the code and don't have time to dig into it I won't be
able to provide a patch myself unfortunately.
Hopefully at the commitfest at least the transaction limitation will/could
be tackled - that would help us a lot already.

Thanks for your time!

On 03/25/2016 04:13 AM, Matthias Kurz wrote:

Hopefully at the commitfest at least the transaction limitation
will/could be tackled - that would help us a lot already.

I don't believe anyone knows how to do that safely. Enums pose special
problems here exactly because unlike all other types the set of valid
values is mutable.

cheers

andre

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 3/24/16 10:27 PM, Tom Lane wrote:

It's conceivable that we could do something like adding an "isdead"
column to pg_enum and making enum_in reject new values that're marked
isdead. But I can't see that we'd ever be able to support true
removal of an enum value at reasonable cost. And I'm not really sure
where the use-case argument is for working hard on it.

I wonder if we could handle this by allowing foreign keys on enum
columns back to pg_enum. Presumably that means we'd have to treat
pg_enum as a regular table and not a catalog table. Due to locking
concerns I don't think we'd want to put the FKs in place by default either.

I've certainly heard people avoiding ENUMs because of their limitations,
so it'd be nice if there was a way to lift them.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 26/03/16 08:17, Jim Nasby wrote:

On 3/24/16 10:27 PM, Tom Lane wrote:

It's conceivable that we could do something like adding an "isdead"
column to pg_enum and making enum_in reject new values that're marked
isdead. But I can't see that we'd ever be able to support true
removal of an enum value at reasonable cost. And I'm not really sure
where the use-case argument is for working hard on it.

I wonder if we could handle this by allowing foreign keys on enum
columns back to pg_enum. Presumably that means we'd have to treat
pg_enum as a regular table and not a catalog table. Due to locking
concerns I don't think we'd want to put the FKs in place by default
either.

I've certainly heard people avoiding ENUMs because of their
limitations, so it'd be nice if there was a way to lift them.

Well, I use Enums extensively in Java.

However, I totally avoid using ENUMs in pg, due to their inflexibility!

Cheers,
Gavin

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mar 25, 2016, at 11:50 AM, Andrew Dunstan <andrew@dunslane.net> wrote:

I don't believe anyone knows how to do that safely.

The core issue, for me, is that not being able to modify enum values in a transaction breaks a very wide variety of database migration tools. Even a very brutal solution like marking indexes containing the altered type invalid on a ROLLBACK would be preferable to the current situation.

--
-- Christophe Pettus
xof@thebuild.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Andrew Dunstan <andrew@dunslane.net> writes:

On 03/25/2016 04:13 AM, Matthias Kurz wrote:

Hopefully at the commitfest at least the transaction limitation
will/could be tackled - that would help us a lot already.

I don't believe anyone knows how to do that safely. Enums pose special
problems here exactly because unlike all other types the set of valid
values is mutable.

However, this problem (and the one described in the comments of
AlterEnum()) doesn't apply to altering the name, since that doesn't
affect the OID or the ordering. Attached is version 2 of the patch,
which allows ALTER TYPE ... ALTER VALUE inside a transaction.

It still needs documentation, and possibly support for IF (NOT) EXISTS,
if people think that's useful.

On 3/25/16 2:22 PM, Gavin Flower wrote:

I've certainly heard people avoiding ENUMs because of their
limitations, so it'd be nice if there was a way to lift them.

Well, I use Enums extensively in Java.

However, I totally avoid using ENUMs in pg, due to their inflexibility!

Possibly related to this, for a long time I've also wanted a way to
better integrate FKs, probably via some kind of a pseudotype or maybe a
special operator. The idea being that instead of manually specifying
joins, you could treat a FK field in a table as a pointer and do things
like:

CREATE TABLE invoice(customer int NOT NULL REFERENCES(customer));

SELECT invoice.*, customer->first_name, customer->last_name, ...
FROM invoice;

If we had that capability, there would be less need for ENUMs.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 03/25/2016 03:22 PM, Christophe Pettus wrote:

On Mar 25, 2016, at 11:50 AM, Andrew Dunstan <andrew@dunslane.net> wrote:

I don't believe anyone knows how to do that safely.

The core issue, for me, is that not being able to modify enum values in a transaction breaks a very wide variety of database migration tools. Even a very brutal solution like marking indexes containing the altered type invalid on a ROLLBACK would be preferable to the current situation.

Well, let's see if we can think harder about when it will be safe to add
new enum values and how we can better handle unsafe situations.

The danger AIUI is that the new value value will get into an upper level
page of an index, and that we can't roll that back if the transaction
aborts.

First, if there isn't an existing index using the type we should be safe
- an index created in the current transaction is not a problem because
if the transaction aborts the whole index will disappear.

Even if there is an existing index, if it isn't touched by the current
transaction the we should still be safe. However, if it is touched we
could end up with a corrupted index if the transaction aborts. Maybe we
could provide an option to reindex those indexes if they have been
touched in the transaction and it aborts. And if it's not present we
could instead abort the transaction as soon as it detects something that
touches the index.

I quite understand that this is all hand-wavy, but I'm trying to get a
discussion started that goes beyond acknowledging the pain that the
current situation involves.

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers