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

On 2023-09-28 Th 14:46, Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>> I wonder if we could have a boolean flag in pg_enum, indicating that
>> setting an enum to that value was forbidden.
> Yeah, but that still offers no coherent solution to the problem of
> what happens if there's a table that already contains such a value.
> It doesn't seem terribly useful to forbid new entries if you can't
> get rid of old ones.
>
> Admittedly, a DISABLE flag would at least offer a chance at a
> race-condition-free scan to verify that no such values remain
> in tables. But as somebody already mentioned upthread, that
> wouldn't guarantee that the value doesn't appear in non-leaf
> index pages. So basically you could never get rid of the pg_enum
> row, short of a full dump and restore.

or a reindex, I think, although getting the timing right would be messy.
I agree the non-leaf index pages are rather pesky in dealing with this.

I guess the alternative would be to create a new enum with the
to-be-deleted value missing, and then alter the column type to the new
enum type. For massive tables that would be painful.

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

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com