When should parameters be passed as text v. binary?
I'm just trying to understand the trade-offs between sending everything
always as text, all integer parameters as binary, floats as binary, etc.
On 1/4/20 3:54 AM, Paula Kirsch wrote:
I'm just trying to understand the trade-offs between sending everything
always as text, all integer parameters as binary, floats as binary, etc.
From where to where and using what?
--
Adrian Klaver
adrian.klaver@aklaver.com
"Paula" == Paula Kirsch <pl.kirsch@gmail.com> writes:
Paula> I'm just trying to understand the trade-offs between sending
Paula> everything always as text, all integer parameters as binary,
Paula> floats as binary, etc.
For passing data from client to server, there's no particular reason not
to use the binary format for any data type that you understand (and
where you're passing the data type oid explicitly in the query, rather
than just leaving it as unknown).
For results, things are harder, because libpq is currently
all-or-nothing about result type formats, and if you start using
extension types then not all of them even _have_ a binary format. And to
decode a binary result you need to know the type, and have code to
handle every specific type's binary format.
--
Andrew (irc:RhodiumToad)
As noted by Adrian what is the USE CASE
As a general rule one wants to use the format the data is being stored in.
every time data is cast to another type its going to eat those all so
precious CPU cycles. (all the horror of electrons turned into infrared
beams)
converting Bytea type to a string encoded in Base64 adds 30% overhead.
converting an integer tor ASCII can add allot of overhead.
The answer is it depends on the USE CASE if casting adds any benefit. my
gut tells me it will not add any benefiet
On Sat, Jan 4, 2020 at 1:59 PM Andrew Gierth <andrew@tao11.riddles.org.uk>
wrote:
Show quoted text
"Paula" == Paula Kirsch <pl.kirsch@gmail.com> writes:
Paula> I'm just trying to understand the trade-offs between sending
Paula> everything always as text, all integer parameters as binary,
Paula> floats as binary, etc.For passing data from client to server, there's no particular reason not
to use the binary format for any data type that you understand (and
where you're passing the data type oid explicitly in the query, rather
than just leaving it as unknown).For results, things are harder, because libpq is currently
all-or-nothing about result type formats, and if you start using
extension types then not all of them even _have_ a binary format. And to
decode a binary result you need to know the type, and have code to
handle every specific type's binary format.--
Andrew (irc:RhodiumToad)
On 1/4/20 11:25 AM, Paula Kirsch wrote:
Please reply to list also.
Ccing list.
In an extended-query, my understanding is that you can choose to pass
the parameters as text or binary. I am trying to understand
https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY
and
https://www.postgresql.org/docs/current/protocol-message-formats.html.
Thus the question about the benefits/detriments of passing the
parameters as binary or text.I am also having difficulty finding the postgresql documentation for the
data type oid to pass to explicitly type the parameters.
https://www.postgresql.org/docs/11/catalog-pg-type.html
Any suggestions or examples of parse-messages with a couple of
parameters being passed would be appreciated.Thank you.
On Sat, Jan 4, 2020 at 11:18 AM Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:On 1/4/20 3:54 AM, Paula Kirsch wrote:
I'm just trying to understand the trade-offs between sending
everything
always as text, all integer parameters as binary, floats as
binary, etc.
From where to where and using what?
--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
--
Adrian Klaver
adrian.klaver@aklaver.com
Import Notes
Reply to msg id not found: CAHaXFP+iU2Ztg4noFmsj11rwNGsK2g2mSH_fv-2y0LMTi3rVbA@mail.gmail.com
Good point and I loved the way you put it. More low level stuff I need to
learn.
I'm still struggling trying to find the list of data type oids either in
the documentation or in the postgresql source code so that I can specify
the data correctly (assuming, of course, I make sure the binary on both
sides is compatible.
Thank you.
On Sat, Jan 4, 2020 at 3:30 PM Justin <zzzzz.graf@gmail.com> wrote:
Show quoted text
As noted by Adrian what is the USE CASE
As a general rule one wants to use the format the data is being stored
in. every time data is cast to another type its going to eat those all so
precious CPU cycles. (all the horror of electrons turned into infrared
beams)converting Bytea type to a string encoded in Base64 adds 30% overhead.
converting an integer tor ASCII can add allot of overhead.The answer is it depends on the USE CASE if casting adds any benefit. my
gut tells me it will not add any benefietOn Sat, Jan 4, 2020 at 1:59 PM Andrew Gierth <andrew@tao11.riddles.org.uk>
wrote:"Paula" == Paula Kirsch <pl.kirsch@gmail.com> writes:
Paula> I'm just trying to understand the trade-offs between sending
Paula> everything always as text, all integer parameters as binary,
Paula> floats as binary, etc.For passing data from client to server, there's no particular reason not
to use the binary format for any data type that you understand (and
where you're passing the data type oid explicitly in the query, rather
than just leaving it as unknown).For results, things are harder, because libpq is currently
all-or-nothing about result type formats, and if you start using
extension types then not all of them even _have_ a binary format. And to
decode a binary result you need to know the type, and have code to
handle every specific type's binary format.--
Andrew (irc:RhodiumToad)
On 1/4/20 2:13 PM, Paula Kirsch wrote:
Good point and I loved the way you put it. More low level stuff I need
to learn.I'm still struggling trying to find the list of data type oids either in
the documentation or in the postgresql source code so that I can specify
the data correctly (assuming, of course, I make sure the binary on both
sides is compatible.
https://www.postgresql.org/docs/11/catalog-pg-type.html
select oid, typname from pg_type;
If you want the source code version:
Thank you.
On Sat, Jan 4, 2020 at 3:30 PM Justin <zzzzz.graf@gmail.com
<mailto:zzzzz.graf@gmail.com>> wrote:As noted by Adrian what is the USE CASE
As a general rule one wants to use the format the data is being
stored in. every time data is cast to another type its going to eat
those all so precious CPU cycles. (all the horror of electrons
turned into infrared beams)converting Bytea type to a string encoded in Base64 adds 30%
overhead. converting an integer tor ASCII can add allot of overhead.The answer is it depends on the USE CASE if casting adds any
benefit. my gut tells me it will not add any benefietOn Sat, Jan 4, 2020 at 1:59 PM Andrew Gierth
<andrew@tao11.riddles.org.uk <mailto:andrew@tao11.riddles.org.uk>>
wrote:"Paula" == Paula Kirsch <pl.kirsch@gmail.com
<mailto:pl.kirsch@gmail.com>> writes:
Paula> I'm just trying to understand the trade-offs between
sending
Paula> everything always as text, all integer parameters as
binary,
Paula> floats as binary, etc.For passing data from client to server, there's no particular
reason not
to use the binary format for any data type that you understand (and
where you're passing the data type oid explicitly in the query,
rather
than just leaving it as unknown).For results, things are harder, because libpq is currently
all-or-nothing about result type formats, and if you start using
extension types then not all of them even _have_ a binary
format. And to
decode a binary result you need to know the type, and have code to
handle every specific type's binary format.--
Andrew (irc:RhodiumToad)
--
Adrian Klaver
adrian.klaver@aklaver.com