Re: [GENERAL] Floating point error

Hi Everyone,

Riddle me this. I have a database column of type "real" that gets mapped to a Java field of type double via JDBC. We have two databases, test and production, and the test database is periodically blown away and reloaded from a copy of production. We recently noticed that some values do not match when viewed within our application on test vs. production. More specifically:

- Selecting values from both test and production DBs using psql shows "10.3885" as the value
- The Java app on production shows "10.3884573" while the test app shows "10.3885"

I have a hunch that when the value was originally inserted into the production DB it probably contained more than the 6 digits supported by the real data type. It may have even been exactly the "10.3884573" value we see when retrieving via JDBC on production. What I don't understand is why when the value gets mapped back to Java via JDBC those extra digits are coming back. Can anyone explain this or do you think I'm on the wrong track? I stepped through code and it sure seems like the extra information is coming back from the JDBC driver.

Tom

--
Tom Duffey
tduffey(at)trillitech(dot)com
414-751-0600 x102

On 2/24/2013 6:13 PM, Tom Duffey wrote:
> - The Java app on production shows "10.3884573" while the test app shows "10.3885"

'real' is single precision, which is only about 6 digits of decimal
accuracy. if your java variables were double precision, you probably
should have used double in postgres too.

0.1 decimal is a repeating fraction in binary.

--
john r pierce 37N 122W
somewhere on the middle of the left coast

On 2/24/2013 6:20 PM, John R Pierce wrote:
> On 2/24/2013 6:13 PM, Tom Duffey wrote:
>> - The Java app on production shows "10.3884573" while the test app
>> shows "10.3885"
>
> 'real' is single precision, which is only about 6 digits of decimal
> accuracy. if your java variables were double precision, you probably
> should have used double in postgres too.
>
> 0.1 decimal is a repeating fraction in binary.

ooops, missed my third point. if precise decimal numbers are
important, use a decimal data type, which is NUMERIC in SQL (I'm not
familiar enough with Java to know if it has a BCD or other decimal type).

--
john r pierce 37N 122W
somewhere on the middle of the left coast

Em 24/02/2013 23:26, John R Pierce escreveu:
> On 2/24/2013 6:20 PM, John R Pierce wrote:
>> On 2/24/2013 6:13 PM, Tom Duffey wrote:
>>> - The Java app on production shows "10.3884573" while the test app
>>> shows "10.3885"
>>
>> 'real' is single precision, which is only about 6 digits of decimal
>> accuracy. if your java variables were double precision, you
>> probably should have used double in postgres too.
>>
>> 0.1 decimal is a repeating fraction in binary.
>
> ooops, missed my third point. if precise decimal numbers are
> important, use a decimal data type, which is NUMERIC in SQL (I'm not
> familiar enough with Java to know if it has a BCD or other decimal type).
>
Java BigDecimal is the best fit for Numeric in PostgreSQL

It requires a bit more programming effort, but is the most precise type
for money work.

For instance to sum 2.00 plus 2.00 program:

public static void main(String [] args) {
BigDecimal num1 = new BigDecimal("2.00");
BigDecimal num2 = new BigDecimal("2.00");
BigDecimal sum = num1.add(num2);

if(sum.compareTo(new BigDecimal("4.00"))==0) {
System.out.println("Matches: 2.00+2.00 == 4.00");
} else {
System.out.println("Uau, how did you get here?");
}
}

You must use "compareTo" method to compare two values, and can't use
BigDecimal "equals" to compare values, because 2.0 is different than
2.00 (scale differ)...

Regards,

Edson

On 02/24/2013 06:13 PM, Tom Duffey wrote:
> Hi Everyone,
>
> Riddle me this. I have a database column of type "real" that gets mapped to a Java field of type double via JDBC. We have two databases, test and production, and the test database is periodically blown away and reloaded from a copy of production. We recently noticed that some values do not match when viewed within our application on test vs. production. More specifically:
>
> - Selecting values from both test and production DBs using psql shows "10.3885" as the value
> - The Java app on production shows "10.3884573" while the test app shows "10.3885"
>
> I have a hunch that when the value was originally inserted into the production DB it probably contained more than the 6 digits supported by the real data type. It may have even been exactly the "10.3884573" value we see when retrieving via JDBC on production. What I don't understand is why when the value gets mapped back to Java via JDBC those extra digits are coming back. Can anyone explain this or do you think I'm on the wrong track? I stepped through code and it sure seems like the extra information is coming back from the JDBC driver.

Are the production and test apps running on the same platform i.e. OS,
bitness, etc.

>
> Tom
>
> --
> Tom Duffey
> tduffey(at)trillitech(dot)com
> 414-751-0600 x102
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

On Feb 24, 2013, at 8:44 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> wrote:

> On 02/24/2013 06:13 PM, Tom Duffey wrote:
>> Hi Everyone,
>>
>> Riddle me this. I have a database column of type "real" that gets mapped to a Java field of type double via JDBC. We have two databases, test and production, and the test database is periodically blown away and reloaded from a copy of production. We recently noticed that some values do not match when viewed within our application on test vs. production. More specifically:
>>
>> - Selecting values from both test and production DBs using psql shows "10.3885" as the value
>> - The Java app on production shows "10.3884573" while the test app shows "10.3885"
>>
>> I have a hunch that when the value was originally inserted into the production DB it probably contained more than the 6 digits supported by the real data type. It may have even been exactly the "10.3884573" value we see when retrieving via JDBC on production. What I don't understand is why when the value gets mapped back to Java via JDBC those extra digits are coming back. Can anyone explain this or do you think I'm on the wrong track? I stepped through code and it sure seems like the extra information is coming back from the JDBC driver.
>
> Are the production and test apps running on the same platform i.e. OS, bitness, etc.

Yes, the production and test apps are running on the same platform. The Java apps themselves are physically on the same Linux server. The production and test databases reside within the same instance of PostgreSQL.

Also, I should have mentioned up front that I am well aware of the pitfalls of using floating point values and also the fact that PostgreSQL's "real" data type supports 6 digits of precision. What I do not understand is why my JDBC driver is returning more information than what I receive in psql or if I operate on a copy of the database. This leads me to believe that more information was available at insertion time and is somehow being made available to my application even though the data type should only store 6 digits. Let me see if I can write a quick little test case.

Tom

--
Tom Duffey
tduffey(at)trillitech(dot)com
414-751-0600 x102

Em 24/02/2013 23:44, Adrian Klaver escreveu:
> On 02/24/2013 06:13 PM, Tom Duffey wrote:
>> Hi Everyone,
>>
>> Riddle me this. I have a database column of type "real" that gets
>> mapped to a Java field of type double via JDBC. We have two
>> databases, test and production, and the test database is periodically
>> blown away and reloaded from a copy of production. We recently
>> noticed that some values do not match when viewed within our
>> application on test vs. production. More specifically:
>>
>> - Selecting values from both test and production DBs using psql shows
>> "10.3885" as the value
>> - The Java app on production shows "10.3884573" while the test app
>> shows "10.3885"
>>
>> I have a hunch that when the value was originally inserted into the
>> production DB it probably contained more than the 6 digits supported
>> by the real data type. It may have even been exactly the "10.3884573"
>> value we see when retrieving via JDBC on production. What I don't
>> understand is why when the value gets mapped back to Java via JDBC
>> those extra digits are coming back. Can anyone explain this or do you
>> think I'm on the wrong track? I stepped through code and it sure
>> seems like the extra information is coming back from the JDBC driver.
>
> Are the production and test apps running on the same platform i.e. OS,
> bitness, etc.

According to the Java Language specification, double and real are not
precise data types because the how it is stored in binary, which in turn
result in such errors.

See here:
http://docs.oracle.com/javase/specs/jls/se7/html/jls-4.html

See also a discussion about how to overcome here:

http://stackoverflow.com/questions/179427/how-to-resolve-a-java-rounding-double-issue

This issue is not exclusive from Java, other languages based on IEEE 754
standard's suffer of same problem.
That's the reason because BigDecimal exists.

Regards,

Edson

>
>>
>> Tom
>>
>> --
>> Tom Duffey
>> tduffey(at)trillitech(dot)com
>> 414-751-0600 x102
>>
>>
>>
>
>

On 02/24/2013 06:58 PM, Tom Duffey wrote:
>
> On Feb 24, 2013, at 8:44 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> wrote:
>
>> On 02/24/2013 06:13 PM, Tom Duffey wrote:
>>> Hi Everyone,
>>>
>>> Riddle me this. I have a database column of type "real" that gets mapped to a Java field of type double via JDBC. We have two databases, test and production, and the test database is periodically blown away and reloaded from a copy of production. We recently noticed that some values do not match when viewed within our application on test vs. production. More specifically:
>>>
>>> - Selecting values from both test and production DBs using psql shows "10.3885" as the value
>>> - The Java app on production shows "10.3884573" while the test app shows "10.3885"
>>>
>>> I have a hunch that when the value was originally inserted into the production DB it probably contained more than the 6 digits supported by the real data type. It may have even been exactly the "10.3884573" value we see when retrieving via JDBC on production. What I don't understand is why when the value gets mapped back to Java via JDBC those extra digits are coming back. Can anyone explain this or do you think I'm on the wrong track? I stepped through code and it sure seems like the extra information is coming back from the JDBC driver.
>>
>> Are the production and test apps running on the same platform i.e. OS, bitness, etc.
>
> Yes, the production and test apps are running on the same platform. The Java apps themselves are physically on the same Linux server. The production and test databases reside within the same instance of PostgreSQL.
>
> Also, I should have mentioned up front that I am well aware of the pitfalls of using floating point values and also the fact that PostgreSQL's "real" data type supports 6 digits of precision. What I do not understand is why my JDBC driver is returning more information than what I receive in psql or if I operate on a copy of the database. This leads me to believe that more information was available at insertion time and is somehow being made available to my application even though the data type should only store 6 digits. Let me see if I can write a quick little test case.
>

Well I guess you could look in the dump file and see what is recorded there.

> Tom
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

Here is a smaller test case that does not involve Java. I guess this probably is just due to floating point error when the initial value is inserted that is too large for the field but it's still a surprise.

Create a test table, insert a couple values and view the results:

CREATE TABLE test (
id INTEGER PRIMARY KEY,
value REAL NOT NULL
);

INSERT INTO test (id, value) VALUES (1, 10.3884573), (2, 10.3885);
SELECT * FROM test;

id | value
----+---------
1 | 10.3885
2 | 10.3885
(2 rows)

At this point you would think you have two equal values. Now change the type:

ALTER TABLE test ALTER COLUMN value TYPE DOUBLE PRECISION;
SELECT * FROM test;

id | value
----+------------------
1 | 10.3884572982788
2 | 10.388500213623
(2 rows)

Values no longer equal and the first one is in fact closer to what as originally inserted. Why is this? Is this simply caused by how the initially inserted value is stored as floating point?

If you create a "copy" of the database before changing the field type then both values get inserted as "10.3885." Changing the type then results in two equal values. Maybe this is just another pitfall of using floating point numbers and at this point I am just trying to identify exactly where our errors are being introduced so can anyone confirm the above behavior is correct? In our real world example we are not changing the type but are instead getting the second value w/id = 1 above when using JDBC to retrieve values into a Java double field.

I ran the above on PostgreSQL 9.1.2 and 9.2.2 with the same results.

Tom

On Feb 24, 2013, at 9:17 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> wrote:

> On 02/24/2013 06:58 PM, Tom Duffey wrote:
>>
>> On Feb 24, 2013, at 8:44 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> wrote:
>>
>>> On 02/24/2013 06:13 PM, Tom Duffey wrote:
>>>> Hi Everyone,
>>>>
>>>> Riddle me this. I have a database column of type "real" that gets mapped to a Java field of type double via JDBC. We have two databases, test and production, and the test database is periodically blown away and reloaded from a copy of production. We recently noticed that some values do not match when viewed within our application on test vs. production. More specifically:
>>>>
>>>> - Selecting values from both test and production DBs using psql shows "10.3885" as the value
>>>> - The Java app on production shows "10.3884573" while the test app shows "10.3885"
>>>>
>>>> I have a hunch that when the value was originally inserted into the production DB it probably contained more than the 6 digits supported by the real data type. It may have even been exactly the "10.3884573" value we see when retrieving via JDBC on production. What I don't understand is why when the value gets mapped back to Java via JDBC those extra digits are coming back. Can anyone explain this or do you think I'm on the wrong track? I stepped through code and it sure seems like the extra information is coming back from the JDBC driver.
>>>
>>> Are the production and test apps running on the same platform i.e. OS, bitness, etc.
>>
>> Yes, the production and test apps are running on the same platform. The Java apps themselves are physically on the same Linux server. The production and test databases reside within the same instance of PostgreSQL.
>>
>> Also, I should have mentioned up front that I am well aware of the pitfalls of using floating point values and also the fact that PostgreSQL's "real" data type supports 6 digits of precision. What I do not understand is why my JDBC driver is returning more information than what I receive in psql or if I operate on a copy of the database. This leads me to believe that more information was available at insertion time and is somehow being made available to my application even though the data type should only store 6 digits. Let me see if I can write a quick little test case.
>>
>
> Well I guess you could look in the dump file and see what is recorded there.
>
>> Tom
>>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com

--
Tom Duffey
tduffey(at)trillitech(dot)com
414-751-0600 x102

Tom Duffey wrote:
> Here is a smaller test case that does not involve Java. I guess this probably is just due to floating
> point error when the initial value is inserted that is too large for the field but it's still a
> surprise.
>
> Create a test table, insert a couple values and view the results:
>
> CREATE TABLE test (
> id INTEGER PRIMARY KEY,
> value REAL NOT NULL
> );
>
> INSERT INTO test (id, value) VALUES (1, 10.3884573), (2, 10.3885);
> SELECT * FROM test;
>
> id | value
> ----+---------
> 1 | 10.3885
> 2 | 10.3885
> (2 rows)

SET extra_float_digits=3;
SELECT * FROM test;

id | value
----+------------
1 | 10.3884573
2 | 10.3885002
(2 rows)

PostgreSQL by default omits the last three digits to avoid
differences on different architectures (I think).

When you convert to double precision, you'll see these digits.

> At this point you would think you have two equal values. Now change the type:
>
> ALTER TABLE test ALTER COLUMN value TYPE DOUBLE PRECISION;
> SELECT * FROM test;
>
> id | value
> ----+------------------
> 1 | 10.3884572982788
> 2 | 10.388500213623
> (2 rows)

Yours,
Laurenz Albe

That's exactly what I was looking for. We use COPY to transfer data from a 1 billion+ row table to a test database and were confused why the results looked the same but were obviously not. Sounds like we need to use the extra_float_digits setting to include all the available information when transferring the data.

Thanks for the explanation.

Tom

On Feb 25, 2013, at 8:00 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:

> Tom Duffey wrote:
>> Here is a smaller test case that does not involve Java. I guess this probably is just due to floating
>> point error when the initial value is inserted that is too large for the field but it's still a
>> surprise.
>>
>> Create a test table, insert a couple values and view the results:
>>
>> CREATE TABLE test (
>> id INTEGER PRIMARY KEY,
>> value REAL NOT NULL
>> );
>>
>> INSERT INTO test (id, value) VALUES (1, 10.3884573), (2, 10.3885);
>> SELECT * FROM test;
>>
>> id | value
>> ----+---------
>> 1 | 10.3885
>> 2 | 10.3885
>> (2 rows)
>
> SET extra_float_digits=3;
> SELECT * FROM test;
>
> id | value
> ----+------------
> 1 | 10.3884573
> 2 | 10.3885002
> (2 rows)
>
> PostgreSQL by default omits the last three digits to avoid
> differences on different architectures (I think).
>
> When you convert to double precision, you'll see these digits.
>
>> At this point you would think you have two equal values. Now change the type:
>>
>> ALTER TABLE test ALTER COLUMN value TYPE DOUBLE PRECISION;
>> SELECT * FROM test;
>>
>> id | value
>> ----+------------------
>> 1 | 10.3884572982788
>> 2 | 10.388500213623
>> (2 rows)
>
> Yours,
> Laurenz Albe

--
Tom Duffey
tduffey(at)trillitech(dot)com
414-751-0600 x102

Tom Duffey <tduffey(at)trillitech(dot)com> wrote:

> CREATE TABLE test (
>     id INTEGER PRIMARY KEY,
>     value REAL NOT NULL
> );
>
> INSERT INTO test (id, value) VALUES (1, 10.3884573), (2, 10.3885);
> SELECT * FROM test;
>
> id |  value
> ----+---------
>   1 | 10.3885
>   2 | 10.3885
> (2 rows)
>
> At this point you would think you have two equal values.

At this point, try this:

select * from test where value = '10.3885';

 id |  value
----+---------
  2 | 10.3885
(1 row)

Now try this:

select * from test where value = 10.3885;
 id | value
----+-------
(0 rows)

Always remember that floating point types are *approximate* data
types; equality often does not behave as you might expect.  You're
probably aware of the below issues, but just in case:

select '99999999'::real = ('99999999'::real + '1'::real);
 ?column?
----------
 t
(1 row)

select '.1'::real::float;
      float8       
-------------------
 0.100000001490116
(1 row)

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

>>>>> "TD" == Tom Duffey <tduffey(at)trillitech(dot)com> writes:

TD> Riddle me this. I have a database column of type "real" that gets
TD> mapped to a Java field of type double via JDBC. ...

TD> - Selecting values from both test and production DBs using psql
TD> shows "10.3885" as the value

TD> - The Java app on production shows "10.3884573" while the test app
TD> shows "10.3885"

I suspect the issue is that psql(1) and whatever java method you use to
convert the floats to text choose different rounding.

By default, it seems that psql(1) uses something like printf("%.4f",...)
whereas your java app calls a routing which works more like "%.7f".

(The wire format for floats is the same as they are stored, not a text
representation thereof.)

-JimC
--
James Cloos <cloos(at)jhcloos(dot)com> OpenPGP: 1024D/ED7DAEA6

Hi Everyone,

To bring closure to this thread, my whole problem was caused by not knowing about the extra_float_digits setting. We have a script that uses COPY to transfer a subset of rows from a very large production table to a test table. The script was not setting extra_float_digits so the values did not match even though they appeared to match when running queries in psql. Definitely another gotcha for floating point values and it might be a good idea to mention this setting on the "Numeric Types" page of the docs.

Thanks to all who chimed in to help!

Tom

On Feb 28, 2013, at 7:05 PM, James Cloos <cloos(at)jhcloos(dot)com> wrote:

>>>>>> "TD" == Tom Duffey <tduffey(at)trillitech(dot)com> writes:
>
> TD> Riddle me this. I have a database column of type "real" that gets
> TD> mapped to a Java field of type double via JDBC. ...
>
> TD> - Selecting values from both test and production DBs using psql
> TD> shows "10.3885" as the value
>
> TD> - The Java app on production shows "10.3884573" while the test app
> TD> shows "10.3885"
>
> I suspect the issue is that psql(1) and whatever java method you use to
> convert the floats to text choose different rounding.
>
> By default, it seems that psql(1) uses something like printf("%.4f",...)
> whereas your java app calls a routing which works more like "%.7f".
>
> (The wire format for floats is the same as they are stored, not a text
> representation thereof.)
>
> -JimC
> --
> James Cloos <cloos(at)jhcloos(dot)com> OpenPGP: 1024D/ED7DAEA6

--
Tom Duffey
tduffey(at)trillitech(dot)com
414-751-0600 x102

Tom Duffey wrote (on -general):
> To bring closure to this thread, my whole problem was caused by not knowing about the
> extra_float_digits setting. We have a script that uses COPY to transfer a subset of rows from a very
> large production table to a test table. The script was not setting extra_float_digits so the values
> did not match even though they appeared to match when running queries in psql. Definitely another
> gotcha for floating point values and it might be a good idea to mention this setting on the "Numeric
> Types" page of the docs.

True; how about this patch.

Yours,
Laurenz Albe

>>>>> "TD" == Tom Duffey <tduffey(at)trillitech(dot)com> writes:

TD> We have a script thatb uses COPY to transfer a subset of rows
TD> from a very large production table to a test table. The script
TD> was not setting extra_float_digits so the values did not match
TD> even though they appeared to match when running queries in psql.

Incidently, this is a prime example of where the hexidecimal format
for floats would benefit pg, as it makes the text format for floats
just as suitable for round-tripping as the binary format.

-JimC
--
James Cloos <cloos(at)jhcloos(dot)com> OpenPGP: 1024D/ED7DAEA6

While having more docs around extra_float_digits is a great idea, I
don't think the patch really clarifies much.

(Disclaimer: I honestly have only a vague idea of the reasoning behind
extra_float_digits existing in the first place, but perhaps that means
I'm a good target audience for the doc patch. Also, I apologize if
anything below sounds abrasive--having worked on both my own driver
and a few things here and there on the JDBC one, plus running into
this in assorted places in the wild, I still find extra_float_digits
baffling at best. I immensely appreciate the effort to make
improvements here.)

That is, the patch explains some of the reasoning behind the setting,
but it doesn't really help the reader identify where setting this is
useful and/or necessary. E.g., the JDBC driver just indiscriminately
sets extra_float_digits to 3 if the server supports it (the max
allowable). Is this a bogus approach?

+ The (inherently inaccurate) textual representation of a <type>real</type>
+ or <type>double precision</type> value...

Isn't full fidelity possible assuming sensible rounding semantics and
enough characters of precision? Isn't that the point of
extra_float_digits?

+ will by default contain only as many
+ significant digits as can be represented without losing precision
+ on any platform supported by PostgreSQL.

How would providing more digits lose precision? Platform as in a
non-IEEE 754 server restoring a dump? A non-IEEE 754 client running
queries? Something more subtle? And how does having more precision in
the serialized cause these to lose precision when decoding the value?

I think extra_float_digits is an awkward, surprising implementation
detail--at first glance it seems like a "flip this switch for correct
behavior" knob. I'd love to have a clear explanation in the docs of
why it's needed, and when you should and should not set it (e.g., is
the JDBC driver wrong to set it all the time? should every driver set
it all the time?).

Thanks,
Maciek

Maciek Sakrejda <m(dot)sakrejda(at)gmail(dot)com> writes:
> [ a bunch of questions that boil down to: ]
> Isn't full fidelity possible assuming sensible rounding semantics and
> enough characters of precision?

The fundamental issue is that the underlying representation is binary
and so its precision limit doesn't correspond to an exact number of
decimal digits.

The default print format tries to hide that from you by printing only
as many decimal digits as the stored format is certain to be able to
reproduce, ie if you enter "1.23456" you should see that printed again,
not "1.23455" or "1.23457". However, the stored value is not going to
be exactly equal to "1.23456".

The real difficulty is that there may be more than one storable value
that corresponds to "1.23456" to six decimal digits. To be certain that
we can reproduce the stored value uniquely, we have to err in the other
direction, and print *more* decimal digits than the underlying precision
justifies, rather than a bit less. Some of those digits are going to
look like garbage to the naked eye.

pg_dump cares about reproducing values exactly, and not about whether
things are nice-looking, so it cranks up extra_float_digits. The JDBC
driver might be justified in doing likewise, to ensure that the
identical binary float value is stored on both client and server ---
but that isn't even a valid goal unless you assume that the server's
float implementation is the same as Java's, which is a bit of a leap of
faith, even if IEEE 754 is nigh universal these days.

> I think extra_float_digits is an awkward, surprising implementation
> detail

We could have dumbed it down to a boolean "look nice versus reproduce
the value exactly" switch, but it seemed like there might be
applications that could use some additional flexibility. In any case,
it's not Postgres' fault that there is an issue here; it's fundamental
to the use of binary rather than decimal stored values.

regards, tom lane

On Sun, Mar 3, 2013 at 9:14 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> The real difficulty is that there may be more than one storable value
> that corresponds to "1.23456" to six decimal digits. To be certain that
> we can reproduce the stored value uniquely, we have to err in the other
> direction, and print *more* decimal digits than the underlying precision
> justifies, rather than a bit less. Some of those digits are going to
> look like garbage to the naked eye.

I think part of the difficulty here is that psql (if I understand this
correctly) conflates the wire-format text representations with what
should be displayed to the user. E.g., a different driver might parse
the wire representation into a native representation, and then format
that native representation when it is to be displayed. That's what the
JDBC driver does, so it doesn't care about how the wire format
actually looks.

> pg_dump cares about reproducing values exactly, and not about whether
> things are nice-looking, so it cranks up extra_float_digits. The JDBC
> driver might be justified in doing likewise, to ensure that the
> identical binary float value is stored on both client and server ---
> but that isn't even a valid goal unless you assume that the server's
> float implementation is the same as Java's, which is a bit of a leap of
> faith, even if IEEE 754 is nigh universal these days.

I would hope that any driver cares about reproducing values exactly
(or at least as exactly as the semantics of the client and server
representations of the data type allow). Once you start talking
operations, sure, things get a lot more complicated and you're better
off not relying on any particular semantics. But IEEE 754
unambiguously defines certain bit patterns to correspond to certain
values, no? If both client and server talk IEEE 754 floating point, it
should be possible to round-trip values with no fuss and end up with
the same bits you started with (and as far as I can tell, it is, as
long as extra_float_digits is set to the max), even if the
implementations of actual operations on these numbers behave very
differently on client and server. I think given that many ORMs can
cause UPDATEs on tuple fields that have not changed as part of saving
an object, stable round trips seem like a desirable feature.

> We could have dumbed it down to a boolean "look nice versus reproduce
> the value exactly" switch, but it seemed like there might be
> applications that could use some additional flexibility. In any case,
> it's not Postgres' fault that there is an issue here; it's fundamental
> to the use of binary rather than decimal stored values.

It seems like getting things to look nice should be the client's job,
no? Why does that factor into wire protocol data representations (and
yes, I know part of the answer here--presumably literals are
intimately tied to the same code paths, so it's not quite so simple)?

Going back to the documentation patch, what should the advice be? How
about something along these lines:

Due to the nature of floating point numeric values, a faithful
textual representation
of a <type>real</type> or <type>double precision</type> value
requires some decimal
digits that are generally insignificant, impairing readability of
common values. Because of this, Postgres supports a limited output
precision for
floating point numbers by default. In order to preserve floating
point values more
exactly, you can use the <xref linkend="guc-extra-float-digits">
to adjust this setting.

Is that reasonable? It still feels like extra_float_digits should be
opt-out rather than opt-in (leaving any formatting issues to clients),
but this could be a start. It doesn't address non-IEEE 754 platforms,
but the note in the other proposed patch is so high-level as to just
be hand-waving.

On Mon, Mar 4, 2013 at 2:27 PM, Maciek Sakrejda <m(dot)sakrejda(at)gmail(dot)com> wrote:
> On Sun, Mar 3, 2013 at 9:14 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> The real difficulty is that there may be more than one storable value
>> that corresponds to "1.23456" to six decimal digits. To be certain that
>> we can reproduce the stored value uniquely, we have to err in the other
>> direction, and print *more* decimal digits than the underlying precision
>> justifies, rather than a bit less. Some of those digits are going to
>> look like garbage to the naked eye.
>
> I think part of the difficulty here is that psql (if I understand this
> correctly) conflates the wire-format text representations with what
> should be displayed to the user. E.g., a different driver might parse
> the wire representation into a native representation, and then format
> that native representation when it is to be displayed. That's what the
> JDBC driver does, so it doesn't care about how the wire format
> actually looks.
>
>> pg_dump cares about reproducing values exactly, and not about whether
>> things are nice-looking, so it cranks up extra_float_digits. The JDBC
>> driver might be justified in doing likewise, to ensure that the
>> identical binary float value is stored on both client and server ---
>> but that isn't even a valid goal unless you assume that the server's
>> float implementation is the same as Java's, which is a bit of a leap of
>> faith, even if IEEE 754 is nigh universal these days.
>
> I would hope that any driver cares about reproducing values exactly
> (or at least as exactly as the semantics of the client and server
> representations of the data type allow). Once you start talking
> operations, sure, things get a lot more complicated and you're better
> off not relying on any particular semantics. But IEEE 754
> unambiguously defines certain bit patterns to correspond to certain
> values, no? If both client and server talk IEEE 754 floating point, it
> should be possible to round-trip values with no fuss and end up with
> the same bits you started with (and as far as I can tell, it is, as
> long as extra_float_digits is set to the max), even if the
> implementations of actual operations on these numbers behave very
> differently on client and server. I think given that many ORMs can
> cause UPDATEs on tuple fields that have not changed as part of saving
> an object, stable round trips seem like a desirable feature.

I also find the rationale for extra_float digits quite mysterious for
the same reason: why would most programs care about precision less
than pg_dump does?

If a client wants floating point numbers to look nice, I think the
rendering should be on them (e.g. psql and pgadmin), and the default
should be to expose whatever precision is available to clients that
want an accurate representation of what is in the database.

This kind of change may have many practical problems that may make it
un-pragmatic to alter at this time (considering the workaround is to
set the extra float digits), but I can't quite grasp the rationale for
"well, the only program that cares about the most precision available
is pg_dump". It seems like most programs would care just as much.

--
fdr

Daniel Farina wrote:
> On Mon, Mar 4, 2013 at 2:27 PM, Maciek Sakrejda <m(dot)sakrejda(at)gmail(dot)com> wrote:
>> On Sun, Mar 3, 2013 at 9:14 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> The real difficulty is that there may be more than one storable value
>>> that corresponds to "1.23456" to six decimal digits. To be certain that
>>> we can reproduce the stored value uniquely, we have to err in the other
>>> direction, and print *more* decimal digits than the underlying precision
>>> justifies, rather than a bit less. Some of those digits are going to
>>> look like garbage to the naked eye.
>>
>> I think part of the difficulty here is that psql (if I understand this
>> correctly) conflates the wire-format text representations with what
>> should be displayed to the user. E.g., a different driver might parse
>> the wire representation into a native representation, and then format
>> that native representation when it is to be displayed. That's what the
>> JDBC driver does, so it doesn't care about how the wire format
>> actually looks.
>>
>>> pg_dump cares about reproducing values exactly, and not about whether
>>> things are nice-looking, so it cranks up extra_float_digits. The JDBC
>>> driver might be justified in doing likewise, to ensure that the
>>> identical binary float value is stored on both client and server ---
>>> but that isn't even a valid goal unless you assume that the server's
>>> float implementation is the same as Java's, which is a bit of a leap of
>>> faith, even if IEEE 754 is nigh universal these days.
>>
>> I would hope that any driver cares about reproducing values exactly
>> (or at least as exactly as the semantics of the client and server
>> representations of the data type allow). Once you start talking
>> operations, sure, things get a lot more complicated and you're better
>> off not relying on any particular semantics. But IEEE 754
>> unambiguously defines certain bit patterns to correspond to certain
>> values, no? If both client and server talk IEEE 754 floating point, it
>> should be possible to round-trip values with no fuss and end up with
>> the same bits you started with (and as far as I can tell, it is, as
>> long as extra_float_digits is set to the max), even if the
>> implementations of actual operations on these numbers behave very
>> differently on client and server. I think given that many ORMs can
>> cause UPDATEs on tuple fields that have not changed as part of saving
>> an object, stable round trips seem like a desirable feature.

But all these things are already available:
Any driver that cares can set extra_float_digits=3, and if it
prefers the binary format, the wire protocol supports sending
floating point values as such.

> I also find the rationale for extra_float digits quite mysterious for
> the same reason: why would most programs care about precision less
> than pg_dump does?
>
> If a client wants floating point numbers to look nice, I think the
> rendering should be on them (e.g. psql and pgadmin), and the default
> should be to expose whatever precision is available to clients that
> want an accurate representation of what is in the database.
>
> This kind of change may have many practical problems that may make it
> un-pragmatic to alter at this time (considering the workaround is to
> set the extra float digits), but I can't quite grasp the rationale for
> "well, the only program that cares about the most precision available
> is pg_dump". It seems like most programs would care just as much.

I don't think that it is about looking nice.
C doesn't promise you more than FLT_DIG or DBL_DIG digits of
precision, so PostgreSQL cannot either.

If you allow more, that would mean that if you store the same
number on different platforms and query it, it might come out
differently. Among other things, that would be a problem for
the regression tests.

Yours,
Laurenz Albe

Daniel Farina <daniel(at)heroku(dot)com> wrote:

> This kind of change may have many practical problems that may
> make it un-pragmatic to alter at this time (considering the
> workaround is to set the extra float digits), but I can't quite
> grasp the rationale for "well, the only program that cares about
> the most precision available is pg_dump".  It seems like most
> programs would care just as much.

Something to keep in mind is that when you store 0.01 into a double
precision column, the precise value stored, when written in
decimal, is:

0.01000000000000000020816681711721685132943093776702880859375

Of course, some values can't be precisely written in decimal with
so few digits.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 05.03.2013 15:59, Kevin Grittner wrote:
> Daniel Farina<daniel(at)heroku(dot)com> wrote:
>
>> This kind of change may have many practical problems that may
>> make it un-pragmatic to alter at this time (considering the
>> workaround is to set the extra float digits), but I can't quite
>> grasp the rationale for "well, the only program that cares about
>> the most precision available is pg_dump". It seems like most
>> programs would care just as much.
>
> Something to keep in mind is that when you store 0.01 into a double
> precision column, the precise value stored, when written in
> decimal, is:
>
> 0.01000000000000000020816681711721685132943093776702880859375
>
> Of course, some values can't be precisely written in decimal with
> so few digits.

It would be nice to have a base-2 text format to represent floats. It
wouldn't be as human-friendly as base-10, but it could be used when you
don't want to lose precision. pg_dump in particular.

- Heikki

>>>>> "HL" == Heikki Linnakangas <hlinnakangas(at)vmware(dot)com> writes:

HL> It would be nice to have a base-2 text format to represent floats.
HL> It wouldn't be as human-friendly as base-10, but it could be used
HL> when you don't want to lose precision. pg_dump in particular.

hexidecimal notation for floats exists. The printf format flag is %a
for miniscule and %A for majuscule.

The result of 1./3. is 0xa.aaaaaaaaaaaaaabp-5.

This site has some info and a conversion demo:

http://gregstoll.dyndns.org/~gregstoll/floattohex/

-JimC
--
James Cloos <cloos(at)jhcloos(dot)com> OpenPGP: 1024D/ED7DAEA6

On Tue, Mar 5, 2013 at 12:03 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:
> I don't think that it is about looking nice.
> C doesn't promise you more than FLT_DIG or DBL_DIG digits of
> precision, so PostgreSQL cannot either.
>
> If you allow more, that would mean that if you store the same
> number on different platforms and query it, it might come out
> differently. Among other things, that would be a problem for
> the regression tests.

Thank you: I think this is what I was missing, and what wasn't clear
from the proposed doc patch. But then how can pg_dump assume that it's
always safe to set extra_float_digits = 3? Why the discrepancy between
default behavior and what pg_dump gets? It can't know whether the dump
is to be restored into the same system or a different one (and AFAICT,
there's not even an option to tweak extra_float_digits there).

Maciek Sakrejda <m(dot)sakrejda(at)gmail(dot)com> writes:
> Thank you: I think this is what I was missing, and what wasn't clear
> from the proposed doc patch. But then how can pg_dump assume that it's
> always safe to set extra_float_digits = 3?

It's been proven (don't have a link handy, but the paper is at least
a dozen years old) that 3 extra digits are sufficient to accurately
reconstruct any IEEE single or double float value, given properly
written conversion functions in libc. So that's where that number comes
from. Now, if either end is not using IEEE floats, you may or may not
get equivalent results --- but it's pretty hard to make any guarantees
at all in such a case.

> Why the discrepancy between
> default behavior and what pg_dump gets?

Basically, the default behavior is tuned to the expectations of people
who think that what they put in is what they should get back, ie we
don't want the system doing this by default:

regression=# set extra_float_digits = 3;
SET
regression=# select 0.1::float4;
float4
-------------
0.100000001
(1 row)

regression=# select 0.1::float8;
float8
---------------------
0.10000000000000001
(1 row)

We would get a whole lot more bug reports, not fewer, if that were
the default behavior.

regards, tom lane

On Tue, Mar 5, 2013 at 10:23 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Why the discrepancy between
>> default behavior and what pg_dump gets?
>
> Basically, the default behavior is tuned to the expectations of people
> who think that what they put in is what they should get back, ie we
> don't want the system doing this by default:
>
> regression=# set extra_float_digits = 3;
> SET
> regression=# select 0.1::float4;
> float4
> -------------
> 0.100000001
> (1 row)
>
> regression=# select 0.1::float8;
> float8
> ---------------------
> 0.10000000000000001
> (1 row)
>
> We would get a whole lot more bug reports, not fewer, if that were
> the default behavior.

Isn't this a client rendering issue, rather than an on-the-wire encoding issue?

Maciek Sakrejda <m(dot)sakrejda(at)gmail(dot)com> writes:
> On Tue, Mar 5, 2013 at 10:23 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Basically, the default behavior is tuned to the expectations of people
>> who think that what they put in is what they should get back, ie we
>> don't want the system doing this by default:
>>
>> regression=# set extra_float_digits = 3;
>> SET
>> regression=# select 0.1::float4;
>> float4
>> -------------
>> 0.100000001
>> (1 row)
>>
>> regression=# select 0.1::float8;
>> float8
>> ---------------------
>> 0.10000000000000001
>> (1 row)
>>
>> We would get a whole lot more bug reports, not fewer, if that were
>> the default behavior.

> Isn't this a client rendering issue, rather than an on-the-wire encoding issue?

Nope, at least not unless you ask for binary output format (which
introduces a whole different set of portability gotchas, so it's
not the default either).

regards, tom lane

This conversation has moved beyond my ability to be useful but I want to remind everyone of my original issues in case it helps you improve the docs:

1) Data shown in psql did not match data retrieved by JDBC. I had to debug pretty deep into the JDBC code to confirm that a value I was staring at in psql was different in JDBC. Pretty weird, but I figured it had something to do with floating point malarky.

2) The problem in #1 could not be reproduced when running on our test database. Again very weird, because as far as psql was showing me the values in the two databases were identical. I used COPY to transfer some data from the production database to the test database.

I now know that what you see in psql is not necessarily what you see in JDBC. I also know that you need to set extra_float_digits = 3 before using COPY to transfer data from one database to another or risk differences in floating point values. Sounds like both pg_dump and the JDBC driver must be doing this or its equivalent on their own.

If the numeric types page of the documentation had mentioned the extra_float_digits then I might have been able to solve my own problem. I'd like you to add some mention of it even if it is just handwaving but will let you guys hash it out from here. Either way, PostgreSQL rocks!

Tom

On Mar 5, 2013, at 12:38 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Maciek Sakrejda <m(dot)sakrejda(at)gmail(dot)com> writes:
>> On Tue, Mar 5, 2013 at 10:23 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Basically, the default behavior is tuned to the expectations of people
>>> who think that what they put in is what they should get back, ie we
>>> don't want the system doing this by default:
>>>
>>> regression=# set extra_float_digits = 3;
>>> SET
>>> regression=# select 0.1::float4;
>>> float4
>>> -------------
>>> 0.100000001
>>> (1 row)
>>>
>>> regression=# select 0.1::float8;
>>> float8
>>> ---------------------
>>> 0.10000000000000001
>>> (1 row)
>>>
>>> We would get a whole lot more bug reports, not fewer, if that were
>>> the default behavior.
>
>> Isn't this a client rendering issue, rather than an on-the-wire encoding issue?
>
> Nope, at least not unless you ask for binary output format (which
> introduces a whole different set of portability gotchas, so it's
> not the default either).
>
> regards, tom lane

--
Tom Duffey
tduffey(at)trillitech(dot)com
414-751-0600 x102

Em 05/03/2013 16:01, Tom Duffey escreveu:
> This conversation has moved beyond my ability to be useful but I want to remind everyone of my original issues in case it helps you improve the docs:
>
> 1) Data shown in psql did not match data retrieved by JDBC. I had to debug pretty deep into the JDBC code to confirm that a value I was staring at in psql was different in JDBC. Pretty weird, but I figured it had something to do with floating point malarky.

As stated before, Java tries to be consistent across platforms. This is
different than a dozen of C and C++ implementations you will find (some
implementations will not be consistent even using same library on
different platforms).

>
> 2) The problem in #1 could not be reproduced when running on our test database. Again very weird, because as far as psql was showing me the values in the two databases were identical. I used COPY to transfer some data from the production database to the test database.

Yes, this is really interesting. Is the production database running on
same platform (OS, architecture, updates) as the test database?

Regards,

Edson

>
> I now know that what you see in psql is not necessarily what you see in JDBC. I also know that you need to set extra_float_digits = 3 before using COPY to transfer data from one database to another or risk differences in floating point values. Sounds like both pg_dump and the JDBC driver must be doing this or its equivalent on their own.
>
> If the numeric types page of the documentation had mentioned the extra_float_digits then I might have been able to solve my own problem. I'd like you to add some mention of it even if it is just handwaving but will let you guys hash it out from here. Either way, PostgreSQL rocks!
>
> Tom
>
> On Mar 5, 2013, at 12:38 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Maciek Sakrejda <m(dot)sakrejda(at)gmail(dot)com> writes:
>>> On Tue, Mar 5, 2013 at 10:23 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>>> Basically, the default behavior is tuned to the expectations of people
>>>> who think that what they put in is what they should get back, ie we
>>>> don't want the system doing this by default:
>>>>
>>>> regression=# set extra_float_digits = 3;
>>>> SET
>>>> regression=# select 0.1::float4;
>>>> float4
>>>> -------------
>>>> 0.100000001
>>>> (1 row)
>>>>
>>>> regression=# select 0.1::float8;
>>>> float8
>>>> ---------------------
>>>> 0.10000000000000001
>>>> (1 row)
>>>>
>>>> We would get a whole lot more bug reports, not fewer, if that were
>>>> the default behavior.
>>> Isn't this a client rendering issue, rather than an on-the-wire encoding issue?
>> Nope, at least not unless you ask for binary output format (which
>> introduces a whole different set of portability gotchas, so it's
>> not the default either).
>>
>> regards, tom lane
> --
> Tom Duffey
> tduffey(at)trillitech(dot)com
> 414-751-0600 x102
>
>
>

Maciek Sakrejda wrote:
> On Tue, Mar 5, 2013 at 12:03 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:
>> I don't think that it is about looking nice.
>> C doesn't promise you more than FLT_DIG or DBL_DIG digits of
>> precision, so PostgreSQL cannot either.
>>
>> If you allow more, that would mean that if you store the same
>> number on different platforms and query it, it might come out
>> differently. Among other things, that would be a problem for
>> the regression tests.
>
> Thank you: I think this is what I was missing, and what wasn't clear
> from the proposed doc patch. But then how can pg_dump assume that it's
> always safe to set extra_float_digits = 3? Why the discrepancy between
> default behavior and what pg_dump gets? It can't know whether the dump
> is to be restored into the same system or a different one (and AFAICT,
> there's not even an option to tweak extra_float_digits there).

How about this elaboration?

Yours,
Laurenz Albe

On 03/05/2013 07:23 PM, Tom Lane wrote:
> Maciek Sakrejda <m(dot)sakrejda(at)gmail(dot)com> writes:
>> Thank you: I think this is what I was missing, and what wasn't clear
>> from the proposed doc patch. But then how can pg_dump assume that it's
>> always safe to set extra_float_digits = 3?
>
> It's been proven (don't have a link handy, but the paper is at least
> a dozen years old) that 3 extra digits are sufficient to accurately
> reconstruct any IEEE single or double float value, given properly
> written conversion functions in libc. So that's where that number comes
> from. Now, if either end is not using IEEE floats, you may or may not
> get equivalent results --- but it's pretty hard to make any guarantees
> at all in such a case.

There's also gdtoa, which returns the shortest decimal representation
which rounds to the same decimal number. It would print 0.1 as 0.1, but
0.1 + 0.2 as 0.30000000000000004.

--
Florian Weimer / Red Hat Product Security Team

At 2013-03-06 10:04:25 +0000, laurenz(dot)albe(at)wien(dot)gv(dot)at wrote:
>
> How about this elaboration?

Sorry to nitpick, but I don't like that either, on the grounds that if I
had been in Tom Duffey's place, this addition to the docs wouldn't help
me to understand and resolve the problem.

I'm not entirely convinced that any brief mention of extra_float_digits
would suffice, but here's a proposed rewording:

The <xref linkend="guc-extra-float-digits"> setting controls the
number of extra significant digits included when a floating point
value is converted to text for output. With the default value of
<literal>0</literal>, the output is portable to every platform
supported by PostgreSQL. Increasing it will produce output that
is closer to the stored value, but may be unportable.

It's a pretty generic sort of warning, but maybe it would help?

(I'm marking this "Waiting on author" in the CF.)

-- Abhijit

Abhijit Menon-Sen wrote:
> Sorry to nitpick, but I don't like that either, on the grounds that if I
> had been in Tom Duffey's place, this addition to the docs wouldn't help
> me to understand and resolve the problem.
>
> I'm not entirely convinced that any brief mention of extra_float_digits
> would suffice, but here's a proposed rewording:
>
> The <xref linkend="guc-extra-float-digits"> setting controls the
> number of extra significant digits included when a floating point
> value is converted to text for output. With the default value of
> <literal>0</literal>, the output is portable to every platform
> supported by PostgreSQL. Increasing it will produce output that
> is closer to the stored value, but may be unportable.
>
> It's a pretty generic sort of warning, but maybe it would help?

If I understood the original complaint right, anything that gives you
a hint that the textual representation is "truncated" would be helpful.

I like your suggestion and elaborated it a little.

What do you think of the attached version?

Yours,
Laurenz Albe

At 2013-06-24 10:16:33 +0000, laurenz(dot)albe(at)wien(dot)gv(dot)at wrote:
>
> What do you think of the attached version?

I'm not exactly fond of it, but I can't come up with a better version
either. It's slightly better if "but may not accurately represent the
stored value" is removed.

Does anyone else have suggestions?

-- Abhijit

Abhijit Menon-Sen escribió:
> At 2013-06-24 10:16:33 +0000, laurenz(dot)albe(at)wien(dot)gv(dot)at wrote:
> >
> > What do you think of the attached version?
>
> I'm not exactly fond of it, but I can't come up with a better version
> either. It's slightly better if "but may not accurately represent the
> stored value" is removed.
>
> Does anyone else have suggestions?

Pushed backpatched. If there are more suggestions, I'm happy to tweak
it.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services