Re: Problems with ENUM type manipulation in 9.1

On Wed, Sep 28, 2011 at 5:21 AM,  <depstein@alliedtesting.com> wrote:
>> -----Original Message-----
>> From: Merlin Moncure [mailto:mmoncure@gmail.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. =A0this is dangerous; if you delet=
e an
>> enum value that is in use anywhere, behavior is undefined.
>
> True: Postgres doesn't do any checks when deleting enum values, which con=
trasts with the general practice of disallowing the removal of objects that=
 are still referenced elsewhere in the database. =A0That seems like a bug t=
o me. =A0Anyway, the procedure that we used (based on http://en.dklab.ru/li=
b/dklab_postgresql_enum/) does the necessary checks before removing enum va=
lues.
>
>>
>> > 2. "ALTER TYPE ... ADD cannot be executed from a function or multi-
>> command string" (or from a transaction block), which makes it quite usel=
ess
>> for our purposes. =A0We update our databases using SQL patches. =A0Patch=
es are
>> applied in a single transaction, so that any failure during execution ca=
uses 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, beca=
use
>> 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 att=
empt
>> to add an existing value.
>>
>> sql patches work fine. =A0sql script !=3D multi command string. =A0The d=
ifference is
>> that you are trying to send several commands in a single round trip (PQe=
xec)
>> vs sending one query at a time which is the way you are supposed to do it
>> (and this works perfectly fine with transactions). =A0ALTER/ADD not work=
ing 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 b=
roken, at least in an industrial environment.

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

merlin