Re: Problems with ENUM type manipulation in 9.1

Excerpts from depstein's message of mié sep 28 07:21:17 -0300 2011:
> > -----Original Message-----
> > From: Merlin Moncure [mailto:mmoncure(at)gmail(dot)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.

We don't support deleting of enum values, precisely because there's no
easy way to determine if they are in use somewhere. So there's no
reason to think that we should do any checks when "deleting enum
values". Keep in mind that manually fiddling with the system catalogs
is not supported; if you break stuff by doing it, you get to keep both
pieces.

> Anyway, the procedure that we used (based on
> http://en.dklab.ru/lib/dklab_postgresql_enum/) does the necessary
> checks before removing enum values.

Good. But keep in mind this is not a supported procedure.

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

The reason it is not allowed is because it breaks stuff (I cannot
remember what). Inconvenient, yes. "Broken", perhaps. But it's
working as designed. If you're interested, you could examine the old
threads that led to this behavior and see if it can be improved. But
just removing the check won't do.

> > restarting the session should do it -- as I said, manipulating pg_enum is
> > dangerous. agree with Kevin -- these are not bugs.
>
> It's weird. Sometimes it works when executing commands in separate transactions. And sometimes the same commands don't work even after restarting Postgresql. Completely unpredictable.
>
> The reason I regard these issues as bugs is because the new version broke some functionality that worked in the previous version. But if this goes under feature requests, I'll move the discussion over to general.

Well, it's perfectly predictable if you constrain yourself to supported
operations, which updating catalogs by hand is not. And given that it
wasn't supported when this function was written, for 8.3, we have no
responsibility for ensuring that it still works in later versions.

Note that this email contains no opinion of mine. I am only stating
PostgreSQL Global Development Group policy.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support