Re: Problems with ENUM type manipulation in 9.1

On Wed, Sep 28, 2011 at 10:40 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
>> Excerpts from depstein's message of mi=E9 sep 28 07:21:17 -0300 2011:
>>> ALTER TYPE ... ADD VALUE does not work inside transaction blocks, perio=
d, whether they are executed as a multi-command string or one query at a ti=
me. Try it:
>
>> The reason it is not allowed is because it breaks stuff (I cannot
>> remember what). =A0Inconvenient, yes. =A0"Broken", perhaps. =A0But it's
>> working as designed. =A0If you're interested, you could examine the old
>> threads that led to this behavior and see if it can be improved. =A0But
>> just removing the check won't do.
>
> The comment beside the code says what it breaks:
>
> =A0 =A0 =A0 =A0case T_AlterEnumStmt: =A0 =A0/* ALTER TYPE (enum) */
>
> =A0 =A0 =A0 =A0 =A0 =A0/*
> =A0 =A0 =A0 =A0 =A0 =A0 * We disallow this in transaction blocks, because=
 we can't cope
> =A0 =A0 =A0 =A0 =A0 =A0 * with enum OID values getting into indexes and t=
hen having their
> =A0 =A0 =A0 =A0 =A0 =A0 * defining pg_enum entries go away.
> =A0 =A0 =A0 =A0 =A0 =A0 */
> =A0 =A0 =A0 =A0 =A0 =A0PreventTransactionChain(isTopLevel, "ALTER TYPE ..=
. ADD");
> =A0 =A0 =A0 =A0 =A0 =A0AlterEnum((AlterEnumStmt *) parsetree);
> =A0 =A0 =A0 =A0 =A0 =A0break;
>
> As Merlin says, this is not a bug. =A0It'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. =A0As 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