I noticed that enums are not available to be queried as binary through
the protocol. Is this a known issue? Too late to fix for 8.3? This
is kind of a pain, because it forces any query that returns an enum to
return the entire result as text. afaik, enums are the only POD type
to behave this way.

postgres=# create type foo as enum('foo');
CREATE TYPE

postgres=# copy (select 'foo'::foo) to '/home/postgres/foo.txt' binary;
ERROR: no binary output function available for type foo

merlin

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

Merlin Moncure wrote:
> I noticed that enums are not available to be queried as binary through
> the protocol. Is this a known issue? Too late to fix for 8.3? This
> is kind of a pain, because it forces any query that returns an enum to
> return the entire result as text. afaik, enums are the only POD type
> to behave this way.
>
> postgres=# create type foo as enum('foo');
> CREATE TYPE
>
> postgres=# copy (select 'foo'::foo) to '/home/postgres/foo.txt' binary;
> ERROR: no binary output function available for type foo
>


The trouble is that an enum doesn't have an immutable internal binary value.

I guess we could say that the binary value is the integer offset of the
value in the enum ordering, and translate it back on input. Providing
the binary IO funcs shouldn't be too hard if we do that, unless I'm
missing something.

cheers

andrew



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

"Merlin Moncure" <mmoncure@gmail.com> writes:
> I noticed that enums are not available to be queried as binary through
> the protocol.

What do you think the binary representation ought to be? Copying OIDs
seems pretty useless.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Andrew Dunstan <andrew@dunslane.net> writes:
> Merlin Moncure wrote:
>> I noticed that enums are not available to be queried as binary through
>> the protocol.

> The trouble is that an enum doesn't have an immutable internal binary value.

> I guess we could say that the binary value is the integer offset of the
> value in the enum ordering, and translate it back on input. Providing
> the binary IO funcs shouldn't be too hard if we do that, unless I'm
> missing something.

That would be expensive to get, wouldn't it? The main point of binary
I/O is to be fast, so I'm not excited about an expensive conversion.

What if we just defined the binary format as being identical to the text
format, ie, the enum label? A bit silly but at least it would eliminate
the gotcha that binary I/O fails.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

> What if we just defined the binary format as being identical to the text
> format, ie, the enum label? A bit silly but at least it would eliminate
> the gotcha that binary I/O fails.
>
Seems like it would make a lot more sense to the client application that
way too...

-- Korry

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Tom Lane wrote:
> What if we just defined the binary format as being identical to the text
> format, ie, the enum label? A bit silly but at least it would eliminate
> the gotcha that binary I/O fails.
>
>
>

So we should pretty much mimic text_send/text_recv? If so I can probably
get that done fairly quickly.

cheers

andrew

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On 8/30/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Merlin Moncure" <mmoncure@gmail.com> writes:
> > I noticed that enums are not available to be queried as binary through
> > the protocol.
>
> What do you think the binary representation ought to be? Copying OIDs
> seems pretty useless.

I'm on the fence on this one.

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.

One other very small observation: afaict, there appears to be no way
to list enum contents in psql (although you can list the enums
themselves in the type listing). Maybe this should be possible? I'm
willing to take a stab at these things if Andrew is busy.

merlin

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Merlin Moncure wrote:
>
> One other very small observation: afaict, there appears to be no way
> to list enum contents in psql (although you can list the enums
> themselves in the type listing).

select enum_range(null::myenumtype);

> I'm willing to take a stab at these things if Andrew is busy.
>
>
>

I should have a cut of binary I/O done today, which I will send to you
for testing and TomD for comment. We need to get it in by beta as it's a
catalog change.

cheers

andrew

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

"Merlin Moncure" <mmoncure@gmail.com> writes:
> On 8/30/07, Tom Lane <tgl@sss.pgh.pa.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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

"Merlin Moncure" <mmoncure@gmail.com> writes:

> On 8/30/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> "Merlin Moncure" <mmoncure@gmail.com> writes:
>> > I noticed that enums are not available to be queried as binary through
>> > the protocol.
>>
>> What do you think the binary representation ought to be? Copying OIDs
>> seems pretty useless.
>
> I'm on the fence on this one.
>
> 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.

I think it would be ok only if a pg_dump/pg_restore reliably restored the same
oid->enum value mapping. Otherwise a binary dump is useless. But as I
understand it that's the case currently, is it?

You would also have to have some way for a client to simply look up the enum
mapping. Something like an SRF that returned setof(oid,name).

My first instinct was to just use the enum name but I'm starting to think this
would be better. It is more in line with the promise of enums which is that
the names are just labels and the data internally is fixed size.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly