Re: enum types and binary queries

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

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

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

> 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...
--
Decibel!, aka Jim Nasby decibel(at)decibel(dot)org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)