forcing returned values to be binary

Dave Cramer <pg@fastcrypt.com> writes:

Is there a way to force select * from foo to use binary values ?

In what context?

There is a provision for that in the V3 extended-query protocol.

regards, tom lane

Yeah,

I finally read it through and found it. There's some anecdotal
evidence of up to 2x performance, so I'm interested in how to
implement this in the jdbc driver. ( I'm a little skeptical about 2x)

I was thinking that it may be necessary to issue a describe before
the execute, but I'm thinking now that the driver can only handle
specific types, so anything outside of what it knows about would be
an error anyway.

I gather it's not possible to mix the return format? For example all
known types would be binary, others would be text ? At this point I'm
not even sure it would help.

Thx,

Dave

On 13-Nov-05, at 10:21 PM, Tom Lane wrote:

Dave Cramer <pg@fastcrypt.com> writes:

I was thinking that it may be necessary to issue a describe before
the execute, but I'm thinking now that the driver can only handle
specific types, so anything outside of what it knows about would be
an error anyway.

I gather it's not possible to mix the return format? For example all
known types would be binary, others would be text ? At this point I'm
not even sure it would help.

You can ask for mixed return formats; see the description of the Bind
message. The sticky spot is that you can't really do that without first
having gotten the list of output columns (via Describe Statement).
Without that, you don't even know how many output columns there are,
let alone which ones have datatypes you understand.

I'm not sure that this is a fatal objection, at least not for
prepared-in-advance statements. You can put a Describe Statement into
the same network packet exchange as the original Parse message, so
there isn't any reason that you can't know the column types. It is
problematic if you want to Parse/Bind/Execute in just one round trip.

regards, tom lane

I've talked to Ken Geis via email. He suggests that there is
considerable overhead to be saved if we go to binary; especially in
date, and timestamp fields

One thing though if the date is 64 bit instead of float, what does
the binary output look like? Are they different ?

If so this would seem to complicate things quite a bit.

Dave
On 14-Nov-05, at 12:12 AM, Tom Lane wrote:

Dave Cramer <pg@fastcrypt.com> writes:

One thing though if the date is 64 bit instead of float, what does
the binary output look like? Are they different ?

Quite, it's int64 instead of double ...

If so this would seem to complicate things quite a bit.

Recent PG versions tell you which representation is in use at connection
startup, see the integer_datetimes parameter.

regards, tom lane

On Tue, Nov 15, 2005 at 13:01:20 -0500,
Dave Cramer <pg@fastcrypt.com> wrote:

I've talked to Ken Geis via email. He suggests that there is
considerable overhead to be saved if we go to binary; especially in
date, and timestamp fields

One thing though if the date is 64 bit instead of float, what does
the binary output look like? Are they different ?

Yes, the integer float representation is different than the floating point
representation.

If so this would seem to complicate things quite a bit.

You probably also need to worry about the floating point representation on
the server being different from that on the client.