Re: When should parameters be passed as text v. binary?

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:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/include/catalog/pg_type.dat;h=fe2c4eabb46dac36297699366d7574824238ecf2;hb=HEAD

> Thank you.
>
> On Sat, Jan 4, 2020 at 3:30 PM Justin <zzzzz(dot)graf(at)gmail(dot)com
> <mailto:zzzzz(dot)graf(at)gmail(dot)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 benefiet
>
>
>
> On Sat, Jan 4, 2020 at 1:59 PM Andrew Gierth
> <andrew(at)tao11(dot)riddles(dot)org(dot)uk <mailto:andrew(at)tao11(dot)riddles(dot)org(dot)uk>>
> wrote:
>
> >>>>> "Paula" == Paula Kirsch <pl(dot)kirsch(at)gmail(dot)com
> <mailto:pl(dot)kirsch(at)gmail(dot)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(dot)klaver(at)aklaver(dot)com