Re: [HACKERS] Floating point error

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