Re: Alter or rename enum value

Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)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