Re: enum types and binary queries

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