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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
