Thread: PQexecParams with binary resultFormat vs BINARY CURSOR
Hi, Considering query for binary data stored directly in tables using libpq API, I'm trying to understand what is the difference between specifying binary format in functions like PQexecParams and use of BINARY CURSOR. For example, with query like this: SELECT large_image FROM tbl; where large_image is a custom type, is there a big difference between binary format specified to libpq and use of BINARY CURSOR? Is it client-side binary vs server-side binary processing? Simply, I'd like to avoid textual<->binary conversions at any stage. (Endianness is not an issue here.) Best regards, -- Mateusz Loskot, http://mateusz.loskot.net
Hey Mateusz,
--
// Dmitriy.
2011/11/11 Mateusz Łoskot <mateusz@loskot.net>
Hi,
Considering query for binary data stored directly in tables
using libpq API, I'm trying to understand what is the difference
between specifying binary format in functions like
PQexecParams and use of BINARY CURSOR.
For example, with query like this:
SELECT large_image FROM tbl;
where large_image is a custom type,
is there a big difference between binary format specified
to libpq and use of BINARY CURSOR?
Is it client-side binary vs server-side binary processing?
Simply, I'd like to avoid textual<->binary conversions at any stage.
(Endianness is not an issue here.)
Best regards,
"...The concept of a binary cursor as such is thus obsolete when using extended query protocol — any cursor can be treated as either text or binary. ..."
from
http://www.postgresql.org/docs/9.1/static/sql-declare.html
from
http://www.postgresql.org/docs/9.1/static/sql-declare.html
--
// Dmitriy.
Hi Dmitriy, 2011/11/11 Dmitriy Igrishin <dmitigr@gmail.com>: > 2011/11/11 Mateusz Łoskot <mateusz@loskot.net> >> >> Considering query for binary data stored directly in tables >> using libpq API, I'm trying to understand what is the difference >> between specifying binary format in functions like >> PQexecParams and use of BINARY CURSOR. >> >> For example, with query like this: >> >> SELECT large_image FROM tbl; >> >> where large_image is a custom type, >> is there a big difference between binary format specified >> to libpq and use of BINARY CURSOR? >> Is it client-side binary vs server-side binary processing? >> >> Simply, I'd like to avoid textual<->binary conversions at any stage. >> >> (Endianness is not an issue here.) >> >> Best regards, > > "...The concept of a binary cursor as such is thus obsolete when using > extended query protocol — any cursor can be treated as either text or > binary. ..." from > http://www.postgresql.org/docs/9.1/static/sql-declare.html Thanks, this is interesting. I've been reading more about this and the picture seems to be clear: "Note: The choice between text and binary output is determined by the format codes given in Bind, regardless of the SQL command involved." http://www.postgresql.org/docs/9.1/static/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY However, I'm not sure how I can utilise this feature of the extended query protocol with libpq API. Concretely, how to translate the binding with format specification here "choice between text and binary output is determined by the format codes given in Bind" Does it mean the protocol automagically switches between text/binary depending on format code (0|1) specified to PQexecParams and friends? Side question, is this new feature of the extended query protocol in 9.x line? Best regards, -- Mateusz Loskot, http://mateusz.loskot.net
2011/11/11 Mateusz Łoskot <mateusz@loskot.net>
-- Hi Dmitriy,
2011/11/11 Dmitriy Igrishin <dmitigr@gmail.com>:Thanks, this is interesting.> 2011/11/11 Mateusz Łoskot <mateusz@loskot.net>
>>
>> Considering query for binary data stored directly in tables
>> using libpq API, I'm trying to understand what is the difference
>> between specifying binary format in functions like
>> PQexecParams and use of BINARY CURSOR.
>>
>> For example, with query like this:
>>
>> SELECT large_image FROM tbl;
>>
>> where large_image is a custom type,
>> is there a big difference between binary format specified
>> to libpq and use of BINARY CURSOR?
>> Is it client-side binary vs server-side binary processing?
>>
>> Simply, I'd like to avoid textual<->binary conversions at any stage.
>>
>> (Endianness is not an issue here.)
>>
>> Best regards,
>
> "...The concept of a binary cursor as such is thus obsolete when using
> extended query protocol — any cursor can be treated as either text or
> binary. ..." from
> http://www.postgresql.org/docs/9.1/static/sql-declare.html
I've been reading more about this and the picture seems to be clear:
"Note: The choice between text and binary output is determined by the format
codes given in Bind, regardless of the SQL command involved."
http://www.postgresql.org/docs/9.1/static/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY
However, I'm not sure how I can utilise this feature of the extended
query protocol
with libpq API. Concretely, how to translate the binding with format
specification here
"choice between text and binary output is determined by the format
codes given in Bind"
Does it mean the protocol automagically switches between text/binary
depending on
format code (0|1) specified to PQexecParams and friends?
The Bind(F) message contains array with
the parameter format codes. So, yes, all you need
is to pass array with format codes to ::PQexecParams
and libpq will let it go.
the parameter format codes. So, yes, all you need
is to pass array with format codes to ::PQexecParams
and libpq will let it go.
Side question, is this new feature of the extended query protocol in 9.x line?
The "extended query" sub-protocol introduced in
protocol version 3.0.
protocol version 3.0.
// Dmitriy.
2011/11/11 Dmitriy Igrishin <dmitigr@gmail.com>: > 2011/11/11 Mateusz Łoskot <mateusz@loskot.net> >> >> Does it mean the protocol automagically switches between text/binary >> depending on >> format code (0|1) specified to PQexecParams and friends? > > The Bind(F) message contains array with > the parameter format codes. So, yes, all you need > is to pass array with format codes to ::PQexecParams > and libpq will let it go. Dmitriy, This is the answer I was looking for. Thanks! I'm glad I don't have to fiddle with cursors directly. Best regards, -- Mateusz Loskot, http://mateusz.loskot.net