need higher extra_float_digits value (3)

I have applications that require IEEE floating-point data
to remain unchanged to the last bit after a dump/restore
cycle. Currently on all our linux systems, pg_dump uses
8 significant figures (FLT_DIG + 2) to dump 32-bit reals
and 17 significant figures (DBL_DIG + 2) to dump 64-bit
doubles, where FLT_DIG and DBL_DIG are defined in float.h
as 6 and 15, respectively, and 2 is the maximum allowed value
for extra_float_digits.

While 17 decimal digits are sufficient to guarantee the
complete recovery of all 64-bit double values, some 32-bit
reals actually require NINE significant figures. For example,
the binary real 0x3dcccd70 is converted using 8 significant
figures to 0.10000122, which if converted back to binary will
become 0x3dcccd71. A general proof for why 9 significant
figures are required for 32-bit reals (and 17 for 64-bit
doubles) is in Theorem 15 of David Goldberg's "What Every
Computer Scientist Should Know About Floating-Point Arithmetic",
which is available on the web and as an appendix to Sun's
Numerical Computation Guide.

I have been running a locally modified postgresql server with
extra_float_digits' upper limit increased by one to 3 and a
modified pg_dump which sets extra_float_digits to 3; I have
verified that the modification does what I need and it does
not seem to have any side effect other than providing one
more significant figure than needed for double precision
binary to decimal conversion. I would like to ask the
postgresql development team to consider it for a future
release.

Thanks,

Keh-Cheng Chu
Solar Physics Group, Hansen Experimental Physics Lab
Stanford University

P.S. I run slony with extra_float_digits set to 3 in
(my locally modified) postgresql.conf in order to
get around the same precision problem. It would be
nice if slony would do something similar to pg_dump
and enable the extra digits by default; I will bring
that up with slony developers if this modification is
accepted.

Keh-Cheng Chu <kehcheng(at)solar2(dot)Stanford(dot)EDU> writes:
> While 17 decimal digits are sufficient to guarantee the
> complete recovery of all 64-bit double values, some 32-bit
> reals actually require NINE significant figures.

Hm, annoying. Seems like we could do one of two things:

1. Increase the max extra_float_digits value to 3 and have pg_dump use
that.

2. Split extra_float_digits into two settings so that float4 and float8
can use different settings.

Offhand the only argument I can see for #2 is that #1 might bloat dump
files unnecessarily --- but it's not likely to be significant on a
percentage basis.

We'd also need to check the code to be sure it's leaving room for the
extra digit in all cases, though I doubt there's really any problem.

Comments?

regards, tom lane

On Wed, Sep 9, 2009 at 6:04 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Keh-Cheng Chu <kehcheng(at)solar2(dot)Stanford(dot)EDU> writes:
>> While 17 decimal digits are sufficient to guarantee the
>> complete recovery of all 64-bit double values, some 32-bit
>> reals actually require NINE significant figures.
>
> Hm, annoying.  Seems like we could do one of two things:
>
> 1. Increase the max extra_float_digits value to 3 and have pg_dump use
> that.
>
> 2. Split extra_float_digits into two settings so that float4 and float8
> can use different settings.
>
> Offhand the only argument I can see for #2 is that #1 might bloat dump
> files unnecessarily --- but it's not likely to be significant on a
> percentage basis.
>
> We'd also need to check the code to be sure it's leaving room for the
> extra digit in all cases, though I doubt there's really any problem.

(2) seems like overkill to me, FWIW.

...Robert

Keh-Cheng Chu <kehcheng(at)solar2(dot)Stanford(dot)EDU> writes:
> I have been running a locally modified postgresql server with
> extra_float_digits' upper limit increased by one to 3 and a
> modified pg_dump which sets extra_float_digits to 3; I have
> verified that the modification does what I need and it does
> not seem to have any side effect other than providing one
> more significant figure than needed for double precision
> binary to decimal conversion. I would like to ask the
> postgresql development team to consider it for a future
> release.

Committed for 8.5.

regards, tom lane