Re: Problems with ENUM type manipulation in 9.1

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

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

It's weird. Sometimes it works when executing commands in separate transactions. And sometimes the same commands don't work even after restarting Postgresql. Completely unpredictable.

The reason I regard these issues as bugs is because the new version broke some functionality that worked in the previous version. But if this goes under feature requests, I'll move the discussion over to general.

Dmitry Epstein | Developer

Allied Testing

www.alliedtesting.com
We Deliver Quality.