Thread: [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
Marti Raudsepp <marti@juffo.org> 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
On Fri, Jun 10, 2011 at 17:20, Tom Lane <tgl@sss.pgh.pa.us> 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.24999999999999964e-308" is out of range for type double precision CONTEXT: COPY foo, line 1, column val: "1.24999999999999964e-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
Marti Raudsepp <marti@juffo.org> writes:
> On Fri, Jun 10, 2011 at 17:20, Tom Lane <tgl@sss.pgh.pa.us> 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
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
Hi, thanks for chiming in. On Sat, Jun 11, 2011 at 22:08, Jeroen Vermeulen <jtv@xs4all.nl> 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
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