Re: Problems with ENUM type manipulation in 9.1

On Wed, Sep 28, 2011 at 10:40 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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.

Yeah -- also it's good to point out even/odd issue with pg_enum. just
about everyone hacked pg_enum previously, and it's good to spread the
word this no longer works :-(. That said, the new enum enhancements
(oid wraparound issue aside) ISTM I can't help but see as a somewhat
of a regression, since previously you could (hackily) work on them
in-transaction, and now you basically can't. No use in crying now, but
in the future I think any DDL that doesn't support in-transaction use
should be regarded with a great deal of skepticism.

merlin