Thread: Difference for Binary format vs Text format for client-server communication
Hi:
Every pg_type has typinput/typoutput and typreceive/typsend
they are used for text format and binary format accordingly. What is
the difference between them in practice? For example, for a PG user,
shall they choose binary format or text format? Actually I don't even
know how to set this in JDBC. Which one is more common in real
life and why?
The reason I ask this is because I have a task to make numeric output
similar to oracle.
Oracle:
SQL> select 2 / 1.0 from dual;
2/1.0
----------
2
2/1.0
----------
2
PG:
postgres=# select 2 / 1.0;
?column?
--------------------
2.0000000000000000
(1 row)
?column?
--------------------
2.0000000000000000
(1 row)
If the user uses text format, I can just hack some numeric_out function, but if they
use binary format, looks I have to change the driver they used for it. Am I
understand it correctly?
--
Best Regards
Andy Fan
Re: Difference for Binary format vs Text format for client-server communication
From
Peter Eisentraut
Date:
On 2020-07-16 18:52, Andy Fan wrote: > The reason I ask this is because I have a task to make numeric output > similar to oracle. > > Oracle: > > SQL> select 2 / 1.0 from dual; > > 2/1.0 > ---------- > 2 > > PG: > > postgres=# select 2 / 1.0; > ?column? > -------------------- > 2.0000000000000000 > (1 row) > > If the user uses text format, I can just hack some numeric_out function, > but if they > use binary format, looks I have to change the driver they used for it. > Am I > understand it correctly? I think what you should be looking at is why the numeric division function produces that scale and possibly make changes there. By the time the type's output or send function is invoked, that's already decided. The output/send functions are not the place to make scale or other semantic adjustments. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Sun, Jul 26, 2020 at 1:49 AM Peter Eisentraut <peter.eisentraut@2ndquadrant.com> wrote:
On 2020-07-16 18:52, Andy Fan wrote:
> The reason I ask this is because I have a task to make numeric output
> similar to oracle.
>
> Oracle:
>
> SQL> select 2 / 1.0 from dual;
>
> 2/1.0
> ----------
> 2
>
> PG:
>
> postgres=# select 2 / 1.0;
> ?column?
> --------------------
> 2.0000000000000000
> (1 row)
>
> If the user uses text format, I can just hack some numeric_out function,
> but if they
> use binary format, looks I have to change the driver they used for it.
> Am I
> understand it correctly?
I think what you should be looking at is why the numeric division
function produces that scale and possibly make changes there.
Thanks, I think you are talking about the select_div_scale function, which is
called before the real division task in div_var. so it will be hard to hack
at that part. Beside that, oracle returns the zero-trim version no matter if division
is involved(I forgot to mention at the first).
At last, I just hacked the numeric_out function, then it works like Oracle now.
However it just works in text format. I tried JDBC, and it uses text format by
default. The solution is not good enough but it is ok for my purpose currently.
IIUC, if a driver uses text protocol for a data type, then it works like this: 1). server
gets a value in binary format. 2). server convert it to string and send it via network,
3). client gets the string. 4). client converts the string to a given data type. looks it is much
more complex than binary protocol. then why text protocol is chosen by default.
By the
time the type's output or send function is invoked, that's already
decided. The output/send functions are not the place to make scale or
other semantic adjustments.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Best Regards
Andy Fan