Re: [BUG] Denormal float values break backup/restore - Mailing list pgsql-hackers

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:
      Source                  ERANGE 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:TC2        Implementation-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