Обсуждение: float8 transmitted in ascii

Hi!

9.1-901-1.jdbc4 (and probably also the latest 9.4 driver, did most testing with
the 9.1 one, though [on pg 9.4.1])

postgres=# select 47.88::DOUBLE PRECISION,
(47.88::DOUBLE PRECISION)::text,
(47.88::DOUBLE PRECISION)::numeric,
replace((47.88::DOUBLE PRECISION)::text, '.', ',');

  float8 | text  | numeric | replace
--------+-------+---------+---------
   47.88 | 47.88 |   47.88 | 47,88

Good. What I actually get with JDBC doesn't look quite as good, though:

47.88
47.8800000000000026
47.88
47,8800000000000026

So, I looked into the driver and the first surprise was that for the first 2
columns the driver gets an identical 19-byte array; despite one is a float8 (and
8!=19) and the other is supposedly Ba string (with 5 characters).

Here it is (AbstractJdbcResultSet.this_row):
52,55,46,56,56,48,48,48,48,48,48,48,48,48,48,48,48,50,54

Well, that appears to be the 19-char *string* '47.8800000000000026' in ascii.

And indeed:

         return toDouble( getFixedString(columnIndex) );
         return Double.parseDouble(s);

Srsly?! Doubles are sent over the wire in "some" text representation and then
parsed in the JDBC driver? Really?

What I still don't get, though: if this is what postgres sends over the wire,
why does psql/pgadmin print the 4th (and 2nd) column correctly?

thx
tcn

P.S.:

     if ("hstore".equals(getPGType(columnIndex))) {
         return HStoreConverter.toString((Map) obj);
     }

So, internally we already have a java.util.Map and there are a few 'solutions'
out there that *parse* the string representation back into java.util.Map? Srsly?

Dave Cramer <pg@fastcrypt.com> writes:
> If I understand you correctly in java you did:
> select 47.88::DOUBLE PRECISION,
> (47.88::DOUBLE PRECISION)::text,
> (47.88::DOUBLE PRECISION)::numeric,
> replace((47.88::DOUBLE PRECISION)::text, '.', ',');
> and the two numeric values are correct, but the text values are wrong ?

extra_float_digits explains it all, I'm sure.

regression=# select 47.88::DOUBLE PRECISION,
(47.88::DOUBLE PRECISION)::text,
(47.88::DOUBLE PRECISION)::numeric,
replace((47.88::DOUBLE PRECISION)::text, '.', ',');
 float8 | text  | numeric | replace
--------+-------+---------+---------
  47.88 | 47.88 |   47.88 | 47,88
(1 row)

regression=# set extra_float_digits = 3;
SET
regression=# select 47.88::DOUBLE PRECISION,
(47.88::DOUBLE PRECISION)::text,
(47.88::DOUBLE PRECISION)::numeric,
replace((47.88::DOUBLE PRECISION)::text, '.', ',');
       float8        |        text         | numeric |       replace
---------------------+---------------------+---------+---------------------
 47.8800000000000026 | 47.8800000000000026 |   47.88 | 47,8800000000000026
(1 row)


            regards, tom lane

On Wed, 29 Apr 2015, Tom Lane wrote:

> extra_float_digits explains it all, I'm sure.

It does. So, the issue here is that postgres itself defaults to 0 while the jdbc
driver (>=9.0) defaults to 3.

But what's the reason for the data being transmitted in ascii?

Thanks!

Dave Cramer <pg@fastcrypt.com> writes:
> I'm not convinced it is being transmitted in ASCII. It should not be

Transmission in binary would require an assumption that the server and
client share identical floating-point representations, which sure sounds
like a leap of faith from here (or at least blind Intel-centricism).

            regards, tom lane

On Apr 30, 2015, at 9:36 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Dave Cramer <pg@fastcrypt.com> writes:
>> I'm not convinced it is being transmitted in ASCII. It should not be
>
> Transmission in binary would require an assumption that the server and
> client share identical floating-point representations, which sure sounds
> like a leap of faith from here (or at least blind Intel-centricism).

Why not just specify the binary format as IEEE 754?  Native to many machines,
and probably cheaper to convert than ASCII anyway if it's not native.