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.
