Re: extensible enum types

On Fri, Jun 18, 2010 at 6:17 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
>
> Tom Lane wrote:
>>
>> 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.

+1 I was going to say exactly the same thing.

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

I think you would need a syscache or something like it. My first
instinct was to load the whole enum value->sort order mapping into a
hash table the first time you're asked to compare two values in a
given type. Then your comparison operator amounts to "look
up/initialize hash table for this enum type, look up both sort orders
in hash table, return comparison". You might need something like a
syscache for the hash tables so that you don't keep the hash tables
around forever.

Using a syscache for the individual sort values would be slower to
initially load if you're sorting a list since you would be doing a lot
of retail lookups of individual values. But then perhaps it's a cheap
way to protect against very large enums which using a hash table per
enum type would be fragile against.

--
greg