Re: extensible enum types

Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>
>> Robert Haas wrote:
>>
>>> I like the idea of being able to modify enums on the fly, but I'm
>>> skeptical of an implementation that won't always work. Maybe it's
>>> still better than what we have now, but it seems grotty.
>>>
>
>
>> I'd be perfectly happy to hear a reasonable alternative.
>>
>
> Insert a sort order column into pg_enum, and rearrange the values in
> that whenever the user wants to add a new value in a particular place.
> You give up cheap comparisons in exchange for flexibility. I think lots
> of people would accept that tradeoff, especially if they could make it
> per-datatype.
>
> One point here is that you'd have to restrict the rearrangements so that
> the relative sort order of existing values never changes, else you break
> (for example) indexes on columns of that type.
>
>
>

Hmm. Yes, that could work. The assumption in my proposal was that
existing values would not be reordered anyway.

But I'm not happy about giving up cheap comparison. And how would it be
per data-type? That part isn't clear to me. Would we mark a given enum
type as having its oids in order? It would also be sensible to quantify
how much more expensive comparisons would become. If the sort order data
were kept in the syscache the extra cost might get very small.

What I actually like most about this suggestion is that we would be able
to apply it cleanly to existing enum types without inventing anything
much new.

cheers

cheers