Re: enum types and binary queries

"Merlin Moncure" <mmoncure(at)gmail(dot)com> writes:
> On 8/30/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> What do you think the binary representation ought to be? Copying OIDs
>> seems pretty useless.

> I actually think this would be ok, if you mean pg_enum.oid, or the
> string would be fine too. I agree that binary protocol is supposed to
> be fast, and I can prefetch the pg_enum table to the client and do the
> magic there. Many other binary formats do similarly inscrutable
> things.

Well, inscrutable is one thing and unportable is another. It's supposed
to be possible to reload binary COPY data into a fresh database --- with
maybe some restrictions on the architecture being similar, for the more
machine-specific datatypes such as float. If we emit raw OIDs then this
will never work, since the same type definition made in a fresh database
would have the same OIDs only by awe-inspiring coincidence.

Andrew's idea of using the enum ordinal value would meet that test, but
at least with the current layout of pg_enum it would be quite expensive
to do the conversion in either direction --- you'd have to fetch
multiple catalog rows. I think we'd have to add another column showing
the ordinal value, and put an index on it, to make I/O reasonably fast.
Doesn't really seem worth it.

regards, tom lane