Re: Problems with ENUM type manipulation in 9.1 - Mailing list pgsql-bugs

> -----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.
>=20
> 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 contr=
asts with the general practice of disallowing the removal of objects that a=
re 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/dkla=
b_postgresql_enum/) does the necessary checks before removing enum values.

>=20
> > 2. "ALTER TYPE ... ADD cannot be executed from a function or multi-
> command string" (or from a transaction block), which makes it quite usele=
ss
> for our purposes. =A0We update our databases using SQL patches. =A0Patche=
s are
> applied in a single transaction, so that any failure during execution cau=
ses 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, becau=
se
> 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 atte=
mpt
> to add an existing value.
>=20
> sql patches work fine.  sql script !=3D multi command string.  The differ=
ence is
> that you are trying to send several commands in a single round trip (PQex=
ec)
> 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, w=
hether 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;=20
commit;

Whether you send the above one query at a time or as a script in psql, it w=
on't work.

What you call a "minor inconvenience" makes enum management effectively bro=
ken, at least in an industrial environment.

>=20
> > 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: =A0"enum value 41983 not found in cache for enum [.=
..]". Is
> it possible to reset this cache after altering the pg_enum table?
>=20
> 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 transact=
ions. And sometimes the same commands don't work even after restarting Post=
gresql. Completely unpredictable.

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


Dmitry Epstein | Developer
=20
Allied Testing

www.alliedtesting.com
We Deliver Quality.