Re: Problems with ENUM type manipulation in 9.1

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Excerpts from depstein's message of mi sep 28 07:21:17 -0300 2011:
>> ALTER TYPE ... ADD VALUE does not work inside transaction blocks, period, whether they are executed as a multi-command string or one query at a time. Try it:

> The reason it is not allowed is because it breaks stuff (I cannot
> remember what). Inconvenient, yes. "Broken", perhaps. But it's
> working as designed. If you're interested, you could examine the old
> threads that led to this behavior and see if it can be improved. But
> just removing the check won't do.

The comment beside the code says what it breaks:

case T_AlterEnumStmt: /* ALTER TYPE (enum) */

/*
* We disallow this in transaction blocks, because we can't cope
* with enum OID values getting into indexes and then having their
* defining pg_enum entries go away.
*/
PreventTransactionChain(isTopLevel, "ALTER TYPE ... ADD");
AlterEnum((AlterEnumStmt *) parsetree);
break;

As Merlin says, this is not a bug. It's a design compromise that we
made after quite some careful consideration, and we're unlikely to
reconsider it unless someone thinks of an actually better solution.
You might care to review the "WIP: extensible enums" thread in
pgsql-hackers during October 2010 to see the issues and alternatives
that were considered.

BTW, I imagine that the reason that manually adding rows to pg_enum no
longer works with any reliability at all is that the manual procedure
isn't cognizant of the new rules about even vs odd OIDs in pg_enum.
Not that it really worked before --- once the OID counter wrapped
around, you'd be pretty well screwed. As Alvaro says, manual
alterations of the system catalogs never have been supported, meaning
that we will never offer a guarantee that something that (more or less)
worked in a previous release will still work in newer ones.

regards, tom lane