Обсуждение: PostgresSQL 10 | Driver 42.2.5 | Float Conversion Issue

Hi Team,

 

I am using postgresql version 9.4.1. We are in the process of upgrading to PostgreSQL 10.

 

While doing so, we have upgraded the JDBC postgres driver from 9.1-901.jdbc4 to 42.2.5 Post the upgrade we are facing the issue with data integrity where the numeric value is getting rounded off.

 

Scenario:

I am trying to insert a value (float datatype in Java) and in DB it is represented as Numeric.

 

When I try to store, "1234567" the value is stored as "1234570".

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

When I run the select statement, I am getting different data…

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

On Mon, Oct 19, 2020 at 11:46 PM Andreas Joseph Krogh <andreas@visena.com> wrote:

På tirsdag 20. oktober 2020 kl. 08:43:55, skrev Thangavel, Parameswaran <Parameswaran.Thangavel@rsa.com>:

When I run the select statement, I am getting different data…

I suggest you use the native "psql"-client and chech the results from that first.

 

Specifically this behavior can be readily observed using the following query:

 

SELECT '1.234567+e06'::float4::numeric;

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

Andreas Joseph Krogh <andreas@visena.com> writes:
>  How can this be sane? 

If you're expecting exact results from float calculations, you need
a refresher course in what floating-point arithmetic is all about.

Standard IEEE float4 representation can handle somewhere between
six and seven decimal digits of accuracy.  It's common to round
off the display to six digits to make sure that you don't see
any bogus digits.  float4_numeric() does that:

    snprintf(buf, sizeof(buf), "%.*g", FLT_DIG, val);

which explains the results David gets from testing direct
float4-to-numeric coercions.  However, that's not the same
code path used for textual display of float4.  float4out()
can be coerced into showing possibly-imprecise digits:

regression=# select '1234567'::float4;
   float4    
-------------
 1.23457e+06
(1 row)

regression=# show extra_float_digits; 
 extra_float_digits 
--------------------
 0
(1 row)

regression=# set extra_float_digits = 1;
SET
regression=# select '1234567'::float4;
 float4  
---------
 1234567
(1 row)

(Also note that v12 and later behave differently still, since they
use a new algorithm for converting floats to decimal.)

I'm not sure though why a JDBC driver change, without a server change,
would affect anything here.  Maybe the driver is/was changing
extra_float_digits behind your back.  Or maybe it's now asking for the
float value to be transmitted in binary, and then the decimal conversion
is being done on the Java side using who-knows-what rules.

Bottom line is that if you are using float anywhere along the line
in a calculation you expect exact results from, You're Doing It
Wrong.  It's not really that exciting to trace down exactly where
the roundoff is happening, because the end answer is still going to
be that you need to not do that.  Use numeric, or integer if you
know you're dealing with not-too-large integers.

            regards, tom lane

Andreas Joseph Krogh <andreas@visena.com> writes:
>  How can this be sane?

If you're expecting exact results from float calculations, you need
a refresher course in what floating-point arithmetic is all about.

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

there should, IMO, be a big fat warning "Don't use the float datatype, at all" somewhere.

Well, the documentation say:

"The data types real and double precision are inexact, variable-precision numeric types. In practice, these types are usually implementations of IEEE Standard 754 for Binary Floating-Point Arithmetic (single and double precision, respectively), to the extent that the underlying processor, operating system, and compiler support it.

Inexact means that some values cannot be converted exactly to the internal format and are stored as approximations, so that storing and retrieving a value might show slight discrepancies"

I think that counts as a big fat warning, it's the same in all SQL servers, nothing unique for PG, the alternative is of course the NUMERIC data type.

På tirsdag 20. oktober 2020 kl. 19:17:54, skrev Tom Lane <tgl@sss.pgh.pa.us>:

Andreas Joseph Krogh <andreas@visena.com> writes:
>  How can this be sane?

If you're expecting exact results from float calculations, you need
a refresher course in what floating-point arithmetic is all about.

 

From a programming perspective - yes, from a DB-client perspective, and in general, no way. In general - "Users" expect "reals" to be just "decimal-numbers", so with this behaviour of float in PG so close to "C-float" there should, IMO, be a big fat warning "Don't use the float datatype, at all" somewhere. Try to tell an accountant that doing calculations with floating-point numbers is crazy, and you should expect the computer to give you wrong answers...

 

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

 

On Tuesday, October 20, 2020, Andreas Joseph Krogh <andreas@visena.com> wrote:

there should, IMO, be a big fat warning "Don't use the float datatype, at all" somewhere.

 

 Chapter 8.1

 

David J.

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

På tirsdag 20. oktober 2020 kl. 20:59:45, skrev David G. Johnston <david.g.johnston@gmail.com>:

On Tuesday, October 20, 2020, Andreas Joseph Krogh <andreas@visena.com> wrote:

there should, IMO, be a big fat warning "Don't use the float datatype, at all" somewhere.

 

 Chapter 8.1

 

David J.

 

Many, at least non-programmers, expect computers to act like calculators when it comes to numbers and arithmetic operations, and I can't blame them.

On Tuesday, October 20, 2020, Andreas Joseph Krogh <andreas@visena.com> wrote:

På tirsdag 20. oktober 2020 kl. 20:59:45, skrev David G. Johnston <david.g.johnston@gmail.com>:

On Tuesday, October 20, 2020, Andreas Joseph Krogh <andreas@visena.com> wrote:

there should, IMO, be a big fat warning "Don't use the float datatype, at all" somewhere.

 

 Chapter 8.1

 

David J.

 

Many, at least non-programmers, expect computers to act like calculators when it comes to numbers and arithmetic operations, and I can't blame them.

 

Our target audience here is programmers so while I agree with the observation I don’t see its relevance.  Programming computers and using numbers means at some point you need to be at least aware of floating point dynamics and choose appropriate data types.

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tuesday, October 20, 2020, Andreas Joseph Krogh <andreas@visena.com>
> wrote:
>> Many, at least non-programmers, expect computers to act like calculators
>> when it comes to numbers and arithmetic operations, and I can't blame them.

> Our target audience here is programmers so while I agree with the
> observation I don’t see its relevance.  Programming computers and using
> numbers means at some point you need to be at least aware of floating point
> dynamics and choose appropriate data types.

The SQL standard specifically defines FLOAT and DOUBLE PRECISION
as being "approximate numeric types", and goes on at some length
about the difference between those and "exact numeric types".
Whether or not a SQL user has ever studied numerical analysis,
I would expect her to be familiar with this property of the
standard SQL data types.

            regards, tom lane

Vladimir Sitnikov <sitnikov.vladimir@gmail.com> writes:
> https://www.postgresql.org/docs/13/datatype-numeric.html#DATATYPE-FLOAT
> The documentation says that PostgreSQL implements IEEE Standard 754.

Note the waffling immediately after that, though.

> 1234567 is exactly representable in IEEE (e.g. see
> https://www.exploringbinary.com/floating-point-converter/ ),
> so I would expect the database should keep the value intact.

You don't really get to have your cake and eat it too.  Yeah, we
could allow more than six digits to propagate through float4_numeric,
but then some cases would result in surprising garbage digits showing
up in the result.  That would annoy at least as many people as the
current behavior.

> In case that matters, pgjdbc does send pass extra_float_digits=2 in the
> initial packet, then it upgrades to extra_float_digist=3 in case server
> version is 9.0+

Hmm, but it's done that for a long while, no?  Doesn't seem to explain
the behavior change the OP is complaining of.

> I did try removing that parameter, and it does not resolve
> 1234567::float4::numeric::text issue

Of course not, that conversion is happening entirely inside the
backend; extra_float_digits isn't involved.  (Although it would
get involved with 1234567::float4::text::numeric, I bet, since
the float4 -> text step would pay attention to it.)

            regards, tom lane

Vladimir Sitnikov <sitnikov.vladimir@gmail.com> writes:
> Tom>Note the waffling immediately after that, though.
> I assume PostgreSQL switched to Ryu around release 12 or so.

> Should float4_numeric switch to Ryu as well?
> I guess it would be both faster and more precise.

Ryu or not, it'd have the issue of sometimes presenting digits the user
wasn't expecting, if the user is under the illusion that float is exact.
I'd rather document "this is always rounded to N digits" than have to
explain the cases where Ryu doesn't produce an "exact" result.
You don't have to look far for examples, eg

# select 1234567890::float4;
    float4
---------------
 1.2345679e+09
(1 row)

# select '0.98765456'::float4;
   float4
------------
 0.98765457
(1 row)

You have to keep in mind that what Ryu does (or claims to do anyway)
is produce the shortest decimal sequence that float4in will map back
to the same float4 bit pattern.  While this does often produce
"unsurprising" results, that is not guaranteed once you're past
six digits.

Now, I suppose there is something to be said for ensuring that
float::numeric and float::text::numeric produce the same answers,
which we'd get if we made float4_numeric and float8_numeric use
Ryu instead of the current method.  But that's not going to
change the fact that the OP is doing it wrong by expecting
exact results from what he's doing.

[ digs in archives... ]  The original thread about the Ryu patch [1]
considered this, and ultimately decided not to on the grounds
that float4_numeric is marked immutable and therefore we should
not change its behavior.  I'm not sure if that argument is
really irrefutable, but it's something to think about.

In any case, the JDBC list is not where to be complaining if you
want a server behavior change.

            regards, tom lane

[1] https://www.postgresql.org/message-id/flat/87r2el1bx6.fsf%40news-spur.riddles.org.uk

Many thanks to all on sharing the thoughts.

However, As an application developer,  I like to see all the options that I can evaluate and pick the minimal change
thatcan get things working. 

So far, I see following are the options that are suggested.

1. Workaround is to add binaryTransferDisable=700,701 driver connection property (the ids are for FLOAT4 and
FLOAT4_ARRAY)
2. Use Java's BigDecimal on the Application side while setting the value through prepared statement.

Any other suggestions which I missed that might work in my scenario?

Thanks
Param

-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Wednesday, October 21, 2020 4:12 AM
To: Vladimir Sitnikov
Cc: David G. Johnston; Andreas Joseph Krogh; Thangavel, Parameswaran; pgsql-jdbc@postgresql.org
Subject: Re: PostgresSQL 10 | Driver 42.2.5 | Float Conversion Issue


[EXTERNAL EMAIL]

Vladimir Sitnikov <sitnikov.vladimir@gmail.com> writes:
> Tom>Note the waffling immediately after that, though.
> I assume PostgreSQL switched to Ryu around release 12 or so.

> Should float4_numeric switch to Ryu as well?
> I guess it would be both faster and more precise.

Ryu or not, it'd have the issue of sometimes presenting digits the user wasn't expecting, if the user is under the
illusionthat float is exact. 
I'd rather document "this is always rounded to N digits" than have to explain the cases where Ryu doesn't produce an
"exact"result. 
You don't have to look far for examples, eg

# select 1234567890::float4;
    float4
---------------
 1.2345679e+09
(1 row)

# select '0.98765456'::float4;
   float4
------------
 0.98765457
(1 row)

You have to keep in mind that what Ryu does (or claims to do anyway) is produce the shortest decimal sequence that
float4inwill map back to the same float4 bit pattern.  While this does often produce "unsurprising" results, that is
notguaranteed once you're past six digits. 

Now, I suppose there is something to be said for ensuring that float::numeric and float::text::numeric produce the same
answers,which we'd get if we made float4_numeric and float8_numeric use Ryu instead of the current method.  But that's
notgoing to change the fact that the OP is doing it wrong by expecting exact results from what he's doing. 

[ digs in archives... ]  The original thread about the Ryu patch [1] considered this, and ultimately decided not to on
thegrounds that float4_numeric is marked immutable and therefore we should not change its behavior.  I'm not sure if
thatargument is really irrefutable, but it's something to think about. 

In any case, the JDBC list is not where to be complaining if you want a server behavior change.

            regards, tom lane

[1] https://www.postgresql.org/message-id/flat/87r2el1bx6.fsf%40news-spur.riddles.org.uk