Re: enum types and binary queries - Mailing list pgsql-hackers

Decibel! wrote:
> On Fri, Aug 31, 2007 at 01:41:47PM -0400, Andrew Dunstan wrote:
>   
>> The technique of using a lookup table that you seem to refer to doesn't 
>> need any special support from the catalogs or the type system. It's used 
>> today in millions of applications. But it can have quite a high cost in 
>> extra joins required to get the labels and extra application complexity. 
>> For a case where the values in the domain of labels are truly fixed, 
>> enums offer a much more performant and much simpler way to go.
>>     
>  
> AIUI, in C code it's easiest to deal with the int value that a C enum
> gets, rather than dealing with a label coming back from the database. I
> know that's what we did where I worked; the enum column stored the
> corresponding C int, and that's what was used as the PK in the lookup
> table.
>
> ISTM it'd be good if we could do the same with our enums (pass the int
> value back instead of a label).
>   


Jim, you and I have had this discussion before. The answer is the same 
as the last time you asked about this: because it breaks the abstraction.

If the C code doesn't need the label then why store it at all? You can 
just make the database field an int.

If you want to get the ordering offset of a particular enum value you 
can write a function in about 10 lines of C that will give it to you.


>   
>>> I also like the idea
>>> of having a fixed ordering to the labels in an enum.
>>>  
>>>       
>> I do not understand what this sentence means. The ordering *is* fixed - 
>> it is defined by the order in which the labels are given in the create 
>> type statement. And the oids that are assigned to the values in pg_enum 
>> are sorted before being assigned to the labels precisely so that they 
>> reflect this ordering. So rest assured that a given enum type will have 
>> a fixed ordering, and it will be consistent across a dump/restore. What 
>> will not necessarily be consistent is the actual oids used, making the 
>> oids unsuitable for use in binary output as noted upthread.
>>     
>
> What if the OID counter wraps in the middle of adding the labels? (IE:
> create a 4 label ENUM when the OID counter is 1 number away from
> wrapping).
>   

It will not be a problem. I have just explained that we sort them first. 
This is a furfy that has been raised before and explained before. See 
pg_enum.c starting around line 52. In particular:
         /* sort them, just in case counter wrapped from high to low */         qsort(oids, n, sizeof(Oid), oid_cmp);


> If we ever add support for adding additional labels to enums this could
> be an issue too.
>   

I doubt we will be doing it. You can get the effect by defining a new 
type and using the old labels.

>   
>> Maybe you need to read 
>> http://developer.postgresql.org/pgdocs/postgres/functions-enum.html to 
>> see info we have made available. We fully expect this list of functions 
>> to grow as we discover how enums are used in practice.
>>     
>
> Looks good... should still be a psql command, imo. Perhaps as part of
> \dT...
>   

We'd have to special case enums, or provide a special \d command to 
handle them. Not sure either is worth it when we have the functions anyway.

cheers

andrew