Jim Nasby <jim.na...@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 (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers