On Tue, Sep 27, 2011 at 5:06 AM, <depst...@alliedtesting.com> wrote: > 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.
you can manually delete from pg_enum. this is dangerous; if you delete an enum value that is in use anywhere, behavior is undefined. > 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. > 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. merlin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
