Re: [HACKERS] [BUG] Denormal float values break backup/restore
On 2011-06-20 19:22, Marti Raudsepp wrote: AIUI that is defined to be a little vague, but includes denormalized numbers that would undergo any rounding at all. It says that on overflow the conversion should return the appropriate HUGE_VAL variant, and set ERANGE. On underflow it returns a reasonably appropriate value (and either may or must set ERANGE, which is the part that isn't clear to me). Which standard is that? Does IEEE 754 itself define strtod() or is there another relevant standard? Urr. No, this is C and/or Unix standards, not IEEE 754. I did some more research into this. The postgres docs do specify the range error, but seemingly based on a different interpretation of underflow than what I found in some of the instances of strtod() documentation: Numbers too close to zero that are not representable as distinct from zero will cause an underflow error. This talks about denormals that get _all_ their significant digits rounded away, but some of the documents I saw specify an underflow for denormals that get _any_ of their significant digits rounded away (and thus have an abnormally high relative rounding error). The latter would happen for any number that is small enough to be denormal, and is also not representable (note: that's not the same thing as "not representable as distinct from zero"!). It's easy to get non-representable numbers when dumping binary floats in a decimal format. For instance 0.1 is not representable, nor are 0.2, 0.01, and so on. The inherent rounding of non-representable values produces weirdness like 0.1 + 0.2 - 0.3 != 0. I made a quick round of the strtod() specifications I could find, and they seem to disagree wildly: SourceERANGE when Return what - PostgreSQL docs All digits lost zero Linux programmer's manual All digits lost zero My GNU/Linux strtod() Any digits lost rounded number SunOS 5 Any digits lost rounded number GNU documentation All digits lost zero IEEE 1003.1 (Open Group 2004) Any digits lost denormal JTC1/SC22/WG14 N794 Any digits lost denormal Sun Studio (C99)Implementation-defined ? ISO/IEC 9899:TC2Implementation-defined denormal C99 Draft N869 (1999) Implementation-defined denormal We can't guarantee very much, then. It looks like C99 disagrees with the postgres interpretation, but also leaves a lot up to the compiler. I've got a few ideas for solving this, but none of them are very good: (a) Ignore underflow errors. This could hurt anyone who relies on knowing their floating-point implementation and the underflow error to keep their rounding errors in check. It also leaves a kind of gap in the predictability of the database's floating-point behaviour. Worst hit, or possibly the only real problem, would be algorithms that divide other numbers, small enough not to produce infinities, by rounded denormals. (b) Dump REAL and DOUBLE PRECISION in hex. With this change, the representation problem goes away and ERANGE would reliably mean "this was written in a precision that I can't reproduce." We could sensibly provide an option to ignore that error for cross-platform dump/restores. This trick does raise a bunch of compatibility concerns: it's a new format of data to restore, it may not work on pre-C99 compilers, and so on. Also, output for human consumption would have to differ from pg_dump output. (c) Have pg_dump produce calculations, not literals, for denormals. Did I mention how these were not great ideas? If your database dump contains 1e-308, pg_dump could recognize that this value can be calculated in the database but possibly not entered directly, and dump e.g. "1e-307::float / 10" instead. (d) Make pg_dump set some "ignore underflows" option. This may make dumps unusable for older postgres versions. Moreover, it doesn't help ORMs and applications that are currently unable to store the "problem numbers." (e) Do what the documentation promises. Actually I have no idea how we could guarantee this. (f) Ignore ERANGE unless strtod() returns ±0 or ±HUGE_VAL. This is probably a reasonable stab at common sense. It does have the nasty property that it doesn't give a full guarantee either way: restores could still break on pre-C99 systems that return 0 on underflow, but C99 doesn't guarantee a particularly accurate denormal. In practice though, implementations seem to do their best to give you the most appropriate rounded number. Jeroen -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUG] Denormal float values break backup/restore
Hi, thanks for chiming in. On Sat, Jun 11, 2011 at 22:08, Jeroen Vermeulen wrote: > AIUI that is defined to be a little vague, but includes denormalized numbers > that would undergo any rounding at all. It says that on overflow the > conversion should return the appropriate HUGE_VAL variant, and set ERANGE. > On underflow it returns a reasonably appropriate value (and either may or > must set ERANGE, which is the part that isn't clear to me). Which standard is that? Does IEEE 754 itself define strtod() or is there another relevant standard? Regards, Marti -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUG] Denormal float values break backup/restore
On 2011-06-11 01:57, Tom Lane wrote: (5) Lobby your libc providers to make strtod accept denormals without throwing ERANGE. There is no obvious reason why it shouldn't. (On at least one of my boxes, it does.) The standard either explicitly allows or requires this behaviour (depending on which text I look at) for underflow. AIUI that is defined to be a little vague, but includes denormalized numbers that would undergo any rounding at all. It says that on overflow the conversion should return the appropriate HUGE_VAL variant, and set ERANGE. On underflow it returns a reasonably appropriate value (and either may or must set ERANGE, which is the part that isn't clear to me). ISTM the appropriate response to ERANGE combined with a "small" return value is to either ignore or report the rounding error, but accept the return value. The current code in float.c doesn't check the return value at all and treats all ERANGE conditions as equal. Jeroen -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUG] Denormal float values break backup/restore
Marti Raudsepp writes: > On Fri, Jun 10, 2011 at 17:20, Tom Lane wrote: >> I put this right about on par with the occasional suggestions that we >> implement our own filesystem. > I am worried that legitimate calculations can bring the database into > a state where a backup succeeds, but is no longer restorable. > Obviously making reliable backups is the job of a database. Yes, but my point is that Postgres cannot be held accountable for every misfeature of the platforms we are using. We are not replacing libc any more than we are replacing the filesystem, or the TCP stack, or several other components that people have trouble with from time to time. We don't have the manpower, nor the expertise, to take responsibility for all that stuff. > I see four ways to make this work: > 1. Clamp denormal numbers to 0.0 when stored into a table. > 2. Throw an error when denormal numbers are stored into a table. > 3. Use the FTZ (flush-to-zero) FPU mode so denormal values never > appear. I'm not sure whether this is available on all target > architectures. > 4. Change the float4in and float8in functions to accept denormal float > literals. This has a nice side-effect of enabling full IEEE 754 > floating point range. Or (5) Lobby your libc providers to make strtod accept denormals without throwing ERANGE. There is no obvious reason why it shouldn't. (On at least one of my boxes, it does.) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUG] Denormal float values break backup/restore
On Fri, Jun 10, 2011 at 17:20, Tom Lane wrote: > I put this right about on par with the occasional suggestions that we > implement our own filesystem. It's not our core competency and in the > end there is no value-add. If you need to work with denormals, find > a platform that supports them better. Sorry if I wasn't clear on this. I don't care whether PostgreSQL supports denormal float calculations or not. I know PostgreSQL doesn't offer IEEE 754 floating point semantics. I am worried that legitimate calculations can bring the database into a state where a backup succeeds, but is no longer restorable. Obviously making reliable backups is the job of a database. Case in point: % psql -c 'create table foo as select 0.25e-307::float8/2 as val' SELECT 1 % pg_dump -t foo > foo.sql % psql -c 'drop table foo' % psql < foo.sql ... ERROR: "1.24964e-308" is out of range for type double precision CONTEXT: COPY foo, line 1, column val: "1.24964e-308" I see four ways to make this work: 1. Clamp denormal numbers to 0.0 when stored into a table. 2. Throw an error when denormal numbers are stored into a table. 3. Use the FTZ (flush-to-zero) FPU mode so denormal values never appear. I'm not sure whether this is available on all target architectures. 4. Change the float4in and float8in functions to accept denormal float literals. This has a nice side-effect of enabling full IEEE 754 floating point range. Regards, Marti -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUG] Denormal float values break backup/restore
Marti Raudsepp writes: > Looking at utils/adt/float.c, seems that some platforms also have > other problems with the strtod() function. Maybe it's time to > implement our own, without bugs and with proper handling for denormal > float values? I put this right about on par with the occasional suggestions that we implement our own filesystem. It's not our core competency and in the end there is no value-add. If you need to work with denormals, find a platform that supports them better. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [BUG] Denormal float values break backup/restore
Hi list, I was playing around with denormal float values in response to the Itanium thread and noticed that Postgres' float conversion functions behave inconsistently, at least on Linux with glibc 2.14 It can successfully convert denormal float values to strings: marti=# select '0.25e-307'::float8/2 as val; val --- 1.25e-308 But trying to convert back to float fails: marti=# select ('0.25e-307'::float8/2)::text::float8 as val; ERROR: "1.25e-308" is out of range for type double precision The most significant impact of this is that anyone who has these values in their tables can't restore them from backup. I'm surprised nobody has reported this yet, but it seems like worthy of fixing in 9.2 at least. Looking at utils/adt/float.c, seems that some platforms also have other problems with the strtod() function. Maybe it's time to implement our own, without bugs and with proper handling for denormal float values? Also applies to float4s: marti=# select ('1.40129846432481707e-45'::float4/4)::text::float4; ERROR: value out of range: underflow Another erratic behavior of float4in: marti=# select ('1.40129846432481707e-45'::float4/2)::text; text -- 7.00649232162409e-46 marti=# select ('1.40129846432481707e-45'::float4/2)::text::float4; float4 1.4013e-45 Regards, Marti -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers