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

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

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

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.



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Reply via email to