Thread: forcing returned values to be binary

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

Dave

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:
>> 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
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

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

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.