On Wed, Sep 28, 2011 at 5:21 AM, <depst...@alliedtesting.com> wrote: >> -----Original Message----- >> From: Merlin Moncure [mailto:mmonc...@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. >> >> 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 > contrasts with the general practice of disallowing the removal of objects > that are 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/dklab_postgresql_enum/) does the necessary checks > before removing enum values. > >> >> > 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. > > ALTER TYPE ... ADD VALUE does not work inside transaction blocks, period, > whether 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; > commit; > > Whether you send the above one query at a time or as a script in psql, it > won't work. > > What you call a "minor inconvenience" makes enum management effectively > broken, at least in an industrial environment.
hm, I have to unfortunately agree -- what a PITB. this is however not a bug. merlin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
