Re: Problems with ENUM type manipulation in 9.1

On Tue, Sep 27, 2011 at 5:06 AM, <depstein(at)alliedtesting(dot)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 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.

> 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.

> 3. In earlier PostgreSQL versions we used custom procedures (based on procedures 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 new enums results in errors, such as this:  "enum value 41983 not found in cache for enum [...]". Is it possible to reset this cache after altering the 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