Re: Allow deleting enumerated values from an existing enumerated data type

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> On 2023-09-28 Th 14:46, Tom Lane wrote:
>> We went through all these points years ago when the enum feature
>> was first developed, as I recall. Nobody thought that the ability
>> to remove an enum value was worth the amount of complexity it'd
>> entail.

> That's quite true, and I accept my part in this history. But I'm not
> sure we were correct back then.

I think it was the right decision at the time, given that the
alternative was to not add the enum feature at all. The question
is whether we're now prepared to do additional work to support DROP
VALUE. But the tradeoff still looks pretty grim, because the
problems haven't gotten any easier.

I've been trying to convince myself that there'd be some value in
your idea about a DISABLE flag, but I feel like there's something
missing there. The easiest implementation would be to have
enum_in() reject disabled values, while still allowing enum_out()
to print them. But that doesn't seem to lead to nice results:

* You couldn't do, say,
SELECT * FROM my_table WHERE enum_col = 'disabled_value'
to look for rows that you need to clean up. I guess this'd work:
SELECT * FROM my_table WHERE enum_col::text = 'disabled_value'
but it's un-obvious and could not use an index on enum_col.

* If any of the disabled values remain, dump/restore would fail.
Maybe you'd want that to be sure you got rid of them, but it sounds
like a foot-gun. ("What do you mean, our only backup doesn't
restore?") Probably people would wish for two different behaviors:
either don't list disabled values at all in the dumped CREATE TYPE,
or do list them but disable them only after loading data. The latter
approach will still have problems in data-only restores, but there are
comparable hazards with things like foreign keys. (pg_upgrade would
need still a third behavior, perhaps.)

On the whole this is still a long way from a clean easy-to-use DROP
facility, and it adds a lot of complexity of its own for pg_dump.
So I'm not sure we want to build it.

regards, tom lane