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

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?

Thanks,
Dmitry

Dmitry Epstein | Developer

Allied Testing
T + 7 495 544 48 69 Ext 417

www.alliedtesting.com
We Deliver Quality.