Re: Problems with ENUM type manipulation in 9.1

On Tue, Sep 27, 2011 at 5:06 AM,  <depstein@alliedtesting.com> wrote:
> Hello,
>
> I've encountered some problems with the updated ENUM in PosgreSQL 9.1:
>
> 1. We can use ALTER TYPE to add enum values, but there is no matching com=
mand 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.

> 2. "ALTER TYPE ... ADD cannot be executed from a function or multi-comman=
d string" (or from a transaction block), which makes it quite useless for o=
ur purposes. =A0We update our databases using SQL patches. =A0Patches are a=
pplied 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 su=
ch a patch. Even if that wasn't an issue, we would still have a problem, be=
cause the command cannot be used in a DO block. Why would we want to do tha=
t? 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.  sql script !=3D 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.

> 3. In earlier PostgreSQL versions we used custom procedures (based on pro=
cedures developed by Dmitry Koterov http://en.dklab.ru/lib/dklab_postgresql=
_enum/) to add and delete ENUM values. These procedures manipulate pg_enum =
table directly. I've updated them to take into account the new column in pg=
_enum that was added in 9.1. However, although adding enums this way seems =
to work (new values appear in the pg_enum table), attempting to use these n=
ew enums results in errors, such as this: =A0"enum value 41983 not found in=
 cache for enum [...]". Is it possible to reset this cache after altering t=
he pg_enum table?

restarting the session should do it -- as I said, manipulating pg_enum
is dangerous.  agree with Kevin -- these are not bugs.

merlin