Problems with ENUM type manipulation in 9.1

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 comma=
nd to remove values, which makes this an incomplete solution.

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 e=
ntire patch to be rolled back. This command cannot be made part of such a p=
atch. 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 t=
o add an existing value.

3. In earlier PostgreSQL versions we used custom procedures (based on proce=
dures developed by Dmitry Koterov http://en.dklab.ru/lib/dklab_postgresql_e=
num/) to add and delete ENUM values. These procedures manipulate pg_enum ta=
ble directly. I've updated them to take into account the new column in pg_e=
num 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 cac=
he for enum [...]". Is it possible to reset this cache after altering the p=
g_enum table?

Thanks,
Dmitry


Dmitry Epstein | Developer
=20
Allied Testing
T + 7 495 544 48 69 Ext 417

www.alliedtesting.com
We Deliver Quality.