Skip site navigation (1) Skip section navigation (2)

Re: enum types and binary queries

From: Andrew Dunstan <andrew(at)dunslane(dot)net>
To: "Decibel!" <decibel(at)decibel(dot)org>
Cc: Merlin Moncure <mmoncure(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: enum types and binary queries
Date: 2007-08-31 18:26:01
Message-ID: 46D85D39.7060208@dunslane.net (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches

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


In response to

Responses

pgsql-hackers by date

Next:From: Merlin MoncureDate: 2007-08-31 18:32:34
Subject: Re: [HACKERS] enum types and binary queries
Previous:From: Gregory StarkDate: 2007-08-31 18:07:40
Subject: Re: Password requirement in windows installer

pgsql-patches by date

Next:From: Merlin MoncureDate: 2007-08-31 18:32:34
Subject: Re: [HACKERS] enum types and binary queries
Previous:From: Decibel!Date: 2007-08-31 17:58:36
Subject: Re: enum types and binary queries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group