Обсуждение: 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@trillitech.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 havetwo 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. Morespecifically: > > - 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 6digits supported by the real data type. It may have even been exactly the "10.3884573" value we see when retrieving viaJDBC on production. What I don't understand is why when the value gets mapped back to Java via JDBC those extra digitsare coming back. Can anyone explain this or do you think I'm on the wrong track? I stepped through code and it sureseems 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@trillitech.com > 414-751-0600 x102 > > > -- Adrian Klaver adrian.klaver@gmail.com
On Feb 24, 2013, at 8:44 PM, Adrian Klaver <adrian.klaver@gmail.com> = wrote: > On 02/24/2013 06:13 PM, Tom Duffey wrote: >> Hi Everyone, >>=20 >> 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: >>=20 >> - 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" >>=20 >> 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. >=20 > 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@trillitech.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@trillitech.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.klaver@gmail.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. Wehave 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. Morespecifically: >>> >>> - 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 the6 digits supported by the real data type. It may have even been exactly the "10.3884573" value we see when retrievingvia JDBC on production. What I don't understand is why when the value gets mapped back to Java via JDBC those extradigits are coming back. Can anyone explain this or do you think I'm on the wrong track? I stepped through code and itsure 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 sameLinux 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 thefact that PostgreSQL's "real" data type supports 6 digits of precision. What I do not understand is why my JDBC driveris returning more information than what I receive in psql or if I operate on a copy of the database. This leads meto believe that more information was available at insertion time and is somehow being made available to my applicationeven 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.klaver@gmail.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 ); =20 INSERT INTO test (id, value) VALUES (1, 10.3884573), (2, 10.3885); SELECT * FROM test; id | value =20 ----+--------- 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 =20 ----+------------------ 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 =3D 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.klaver@gmail.com> = wrote: > On 02/24/2013 06:58 PM, Tom Duffey wrote: >>=20 >> On Feb 24, 2013, at 8:44 PM, Adrian Klaver <adrian.klaver@gmail.com> = wrote: >>=20 >>> On 02/24/2013 06:13 PM, Tom Duffey wrote: >>>> Hi Everyone, >>>>=20 >>>> 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: >>>>=20 >>>> - 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" >>>>=20 >>>> 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. >>>=20 >>> Are the production and test apps running on the same platform i.e. = OS, bitness, etc. >>=20 >> 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. >>=20 >> 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. >>=20 >=20 > Well I guess you could look in the dump file and see what is recorded = there. >=20 >> Tom >>=20 >=20 >=20 > --=20 > Adrian Klaver > adrian.klaver@gmail.com -- Tom Duffey tduffey@trillitech.com 414-751-0600 x102
Tom Duffey wrote: > Here is a smaller test case that does not involve Java. I guess this prob= ably 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. >=20 > Create a test table, insert a couple values and view the results: >=20 > CREATE TABLE test ( > id INTEGER PRIMARY KEY, > value REAL NOT NULL > ); >=20 > INSERT INTO test (id, value) VALUES (1, 10.3884573), (2, 10.3885); > SELECT * FROM test; >=20 > id | value > ----+--------- > 1 | 10.3885 > 2 | 10.3885 > (2 rows) SET extra_float_digits=3D3; 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 t= ype: >=20 > ALTER TABLE test ALTER COLUMN value TYPE DOUBLE PRECISION; > SELECT * FROM test; >=20 > 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.albe@wien.gv.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. >>=20 >> Create a test table, insert a couple values and view the results: >>=20 >> CREATE TABLE test ( >> id INTEGER PRIMARY KEY, >> value REAL NOT NULL >> ); >>=20 >> INSERT INTO test (id, value) VALUES (1, 10.3884573), (2, 10.3885); >> SELECT * FROM test; >>=20 >> id | value >> ----+--------- >> 1 | 10.3885 >> 2 | 10.3885 >> (2 rows) >=20 > SET extra_float_digits=3D3; > SELECT * FROM test; >=20 > id | value > ----+------------ > 1 | 10.3884573 > 2 | 10.3885002 > (2 rows) >=20 > PostgreSQL by default omits the last three digits to avoid > differences on different architectures (I think). >=20 > When you convert to double precision, you'll see these digits. >=20 >> At this point you would think you have two equal values. Now change = the type: >>=20 >> ALTER TABLE test ALTER COLUMN value TYPE DOUBLE PRECISION; >> SELECT * FROM test; >>=20 >> id | value >> ----+------------------ >> 1 | 10.3884572982788 >> 2 | 10.388500213623 >> (2 rows) >=20 > Yours, > Laurenz Albe -- Tom Duffey tduffey@trillitech.com 414-751-0600 x102
Tom Duffey <tduffey@trillitech.com> wrote:=0A=0A> CREATE TABLE test (=0A>= =A0=A0=A0=A0 id INTEGER PRIMARY KEY,=0A>=A0=A0=A0=A0 value REAL NOT NULL=0A= > ); =0A>=0A> INSERT INTO test (id, value) VALUES (1, 10.3884573), (2, 10.3= 885);=0A> SELECT * FROM test;=0A>=0A> id |=A0 value =0A> ----+---------=0A>= =A0=A0 1 | 10.3885=0A>=A0=A0 2 | 10.3885=0A> (2 rows)=0A>=0A> At this point= you would think you have two equal values.=0A=0AAt this point, try this:= =0A=0Aselect * from test where value =3D '10.3885';=0A=0A=A0id |=A0 value = =0A----+---------=0A=A0 2 | 10.3885=0A(1 row)=0A=0ANow try this:=0A=0Aselec= t * from test where value =3D 10.3885;=0A=A0id | value=0A----+-------=0A(0 = rows)=0A=0AAlways remember that floating point types are *approximate* data= =0Atypes; equality often does not behave as you might expect.=A0 You're=0Ap= robably aware of the below issues, but just in case:=0A=0Aselect '99999999'= ::real =3D ('99999999'::real + '1'::real);=0A=A0?column? =0A----------=0A= =A0t=0A(1 row)=0A=0Aselect '.1'::real::float;=0A=A0=A0=A0=A0=A0 float8=A0= =A0=A0=A0=A0 =A0=0A-------------------=0A=A00.100000001490116=0A(1 row)=0A= =0A--=0AKevin Grittner=0AEnterpriseDB: http://www.enterprisedb.com=0AThe En= terprise PostgreSQL Company
>>>>> "TD" == Tom Duffey <tduffey@trillitech.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@jhcloos.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 havea script that uses COPY to transfer a subset of rows from a very large production table to a test table. The script wasnot setting extra_float_digits so the values did not match even though they appeared to match when running queries inpsql. Definitely another gotcha for floating point values and it might be a good idea to mention this setting on the "NumericTypes" page of the docs. Thanks to all who chimed in to help! Tom On Feb 28, 2013, at 7:05 PM, James Cloos <cloos@jhcloos.com> wrote: >>>>>> "TD" == Tom Duffey <tduffey@trillitech.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@jhcloos.com> OpenPGP: 1024D/ED7DAEA6 -- Tom Duffey tduffey@trillitech.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@trillitech.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@jhcloos.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.sakrejda@gmail.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@sss.pgh.pa.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.sakrejda@gmail.com> wrote: > On Sun, Mar 3, 2013 at 9:14 PM, Tom Lane <tgl@sss.pgh.pa.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.sakrejda@gmail.com> wrote: >> On Sun, Mar 3, 2013 at 9:14 PM, Tom Lane <tgl@sss.pgh.pa.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@heroku.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@heroku.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
On Tue, Mar 5, 2013 at 12:03 AM, Albe Laurenz <laurenz.albe@wien.gv.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).
>>>>> "HL" == Heikki Linnakangas <hlinnakangas@vmware.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@jhcloos.com> OpenPGP: 1024D/ED7DAEA6
Maciek Sakrejda <m.sakrejda@gmail.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@sss.pgh.pa.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.sakrejda@gmail.com> writes: > On Tue, Mar 5, 2013 at 10:23 AM, Tom Lane <tgl@sss.pgh.pa.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 ithelps 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 thata value I was staring at in psql was different in JDBC. Pretty weird, but I figured it had something to do with floatingpoint malarky. 2) The problem in #1 could not be reproduced when running on our test database. Again very weird, because as far as psqlwas showing me the values in the two databases were identical. I used COPY to transfer some data from the productiondatabase 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 pointvalues. 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 solvemy 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 outfrom here. Either way, PostgreSQL rocks! Tom On Mar 5, 2013, at 12:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Maciek Sakrejda <m.sakrejda@gmail.com> writes: >> On Tue, Mar 5, 2013 at 10:23 AM, Tom Lane <tgl@sss.pgh.pa.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@trillitech.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 caseit 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 thata value I was staring at in psql was different in JDBC. Pretty weird, but I figured it had something to do with floatingpoint 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 psqlwas showing me the values in the two databases were identical. I used COPY to transfer some data from the productiondatabase 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 pointvalues. 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 solvemy 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 outfrom here. Either way, PostgreSQL rocks! > > Tom > > On Mar 5, 2013, at 12:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > >> Maciek Sakrejda <m.sakrejda@gmail.com> writes: >>> On Tue, Mar 5, 2013 at 10:23 AM, Tom Lane <tgl@sss.pgh.pa.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@trillitech.com > 414-751-0600 x102 > > >
On 03/05/2013 07:23 PM, Tom Lane wrote: > Maciek Sakrejda <m.sakrejda@gmail.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