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
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?
--
Best Regards
Andy Fan
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
----------
2PG:
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
----------
2PG:
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