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

From: Andrew Dunstan <andrew(at)dunslane(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Данил Столповских <danil(dot)stolpovskikh(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org, o(dot)tselebrovskiy(at)postgrespro(dot)ru, d(dot)frolov(at)postgrespro(dot)ru
Subject: Re: Allow deleting enumerated values from an existing enumerated data type
Date: 2023-09-29 20:50:07
Message-ID: a5e0c460-f514-27b8-05bf-4e03e44eebbb@dunslane.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Lakshmi Narayana Velayudam 2023-09-29 20:56:57 Implementing LRU cache for postgresql extension
Previous Message Peter Geoghegan 2023-09-29 20:05:39 Re: [DOCS] HOT - correct claim about indexes not referencing old line pointers