Re: Bug / feature request for floating point to string conversion

Hi,

this is the follow-up to a recent IRC discussion. The topic at hand is floating point values and I think there is one problem which might be solvable only with a new feature. First, the problem:

I created a table

CREATE TABLE dummy ( v DOUBLE PRECISION );

so far, so good. Now, I would like to add values. Since floating point values tend to be problematic when using decimal encoding, I opt for the binary encoding:

INSERT INTO dummy VALUES ( '0X1P-1022' );

this value itself is the problem. If I use pg_dump / pg_restore, the restore fails with:

COPY failed for table "dummy": ERROR: "2.22507385850720138e-308" is out of range for type double precision

This behavior might depend on the system's implementation of strtod(), I'm using Ubuntu 12.04.

Towards a solution:

While the problem occurs when importing the data back, the root (IMHO) is, that I can not request floating point values (datatypes REAL and DOUBLE PRECISION) to be returned as strings with the hexadecimal notation (which would easily preserve all bits). pg_dump should then also use this to retrieve the correct (bit-by-bit) value. Hence, I hope that you could come up with a proper solution for this feature, and since you are far more experienced with PostgreSQL's internals and the possible ways to provide such a feature, I'll leave it to you to propose a syntax / flag / ...

Of course, if I missed something and retrieving the correct value is possible, please let me know. On IRC, we found that "SET extra_float_digits=2" seems to work for the tests I have in my code, but I don't know if that is the correct solution for all possible values of the floating point types. Using the hexadecimal notation feels like the natural solution to me, would give me much more confidence and, as a bonus, it would also improve efficiency, since it's much easier than decimal conversions.

Best regards, Daniel

Daniel Frey <d(dot)frey(at)gmx(dot)de> writes:
> INSERT INTO dummy VALUES ( '0X1P-1022' );

> this value itself is the problem. If I use pg_dump / pg_restore, the restore fails with:

> COPY failed for table "dummy": ERROR: "2.22507385850720138e-308" is out of range for type double precision

> This behavior might depend on the system's implementation of strtod(), I'm using Ubuntu 12.04.

That is definitely a bug in strtod, which you should report. If it
doesn't accept a value that sprintf output, something is broken at the
libc level.

> While the problem occurs when importing the data back, the root (IMHO) is, that I can not request floating point values (datatypes REAL and DOUBLE PRECISION) to be returned as strings with the hexadecimal notation (which would easily preserve all bits).

That's not much of a solution from our standpoint, because it assumes
that every platform has such a feature (and that they all agree on what
the hex notation means).

regards, tom lane

On 10.10.2012, at 20:26, Tom Lane wrote:

> Daniel Frey <d(dot)frey(at)gmx(dot)de> writes:
>> INSERT INTO dummy VALUES ( '0X1P-1022' );
>
>> this value itself is the problem. If I use pg_dump / pg_restore, the restore fails with:
>
>> COPY failed for table "dummy": ERROR: "2.22507385850720138e-308" is out of range for type double precision
>
>> This behavior might depend on the system's implementation of strtod(), I'm using Ubuntu 12.04.
>
> That is definitely a bug in strtod, which you should report. If it
> doesn't accept a value that sprintf output, something is broken at the
> libc level.

Who guarantees that? While I would have hoped for strtod() to accept everything generated from sprintf() from a valid float/double, I don't see anyone giving you this guarantee. I'd be happy to be wrong about this, but until then, I think that pg_dump/pg_restore can not be guaranteed to work, right?

Regards, Daniel