On 2023-09-28 Th 14:46, Tom Lane wrote:
Andrew Dunstan <and...@dunslane.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
