Re: Problems with ENUM type manipulation in 9.1

On Wed, Sep 28, 2011 at 5:21 AM, <depstein(at)alliedtesting(dot)com> wrote:
>> -----Original Message-----
>> From: Merlin Moncure [mailto:mmoncure(at)gmail(dot)com]
>> Sent: Tuesday, September 27, 2011 10:31 PM
>> > 1. We can use ALTER TYPE to add enum values, but there is no matching
>> command to remove values, which makes this an incomplete solution.
>>
>> you can manually delete from pg_enum.  this is dangerous; if you delete an
>> enum value that is in use anywhere, behavior is undefined.
>
> True: Postgres doesn't do any checks when deleting enum values, which contrasts with the general practice of disallowing the removal of objects that are still referenced elsewhere in the database.  That seems like a bug to me.  Anyway, the procedure that we used (based on http://en.dklab.ru/lib/dklab_postgresql_enum/) does the necessary checks before removing enum values.
>
>>
>> > 2. "ALTER TYPE ... ADD cannot be executed from a function or multi-
>> command string" (or from a transaction block), which makes it quite useless
>> for our purposes.  We update our databases using SQL patches.  Patches are
>> applied in a single transaction, so that any failure during execution causes the
>> entire patch to be rolled back. This command cannot be made part of such a
>> patch. Even if that wasn't an issue, we would still have a problem, because
>> the command cannot be used in a DO block. Why would we want to do that?
>> In order to check first what values are already in the ENUM, lest we attempt
>> to add an existing value.
>>
>> sql patches work fine.  sql script != multi command string.  The difference is
>> that you are trying to send several commands in a single round trip (PQexec)
>> vs sending one query at a time which is the way you are supposed to do it
>> (and this works perfectly fine with transactions).  ALTER/ADD not working in-
>> function is a minor annoying inconvience I'll admit.
>
> 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:
>
> begin;
> create type test_enum as enum ('ONE', 'TWO');
> alter type test_enum add value 'THREE';
> drop type test_enum;
> commit;
>
> Whether you send the above one query at a time or as a script in psql, it won't work.
>
> What you call a "minor inconvenience" makes enum management effectively broken, at least in an industrial environment.

hm, I have to unfortunately agree -- what a PITB. this is however not a bug.

merlin