Обсуждение: 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
problemwhich 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
(datatypesREAL and DOUBLE PRECISION) to be returned as strings with the hexadecimal notation (which would easily
preserveall bits). pg_dump should then also use this to retrieve the correct (bit-by-bit) value. Hence, I hope that you
couldcome up with a proper solution for this feature, and since you are far more experienced with PostgreSQL's
internalsand 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
solutionfor all possible values of the floating point types. Using the hexadecimal notation feels like the natural
solutionto me, would give me much more confidence and, as a bonus, it would also improve efficiency, since it's much
easierthan decimal conversions. 

Best regards, Daniel

Daniel Frey <d.frey@gmx.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
preserveall 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.frey@gmx.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
thatpg_dump/pg_restore can not be guaranteed to work, right? 

Regards, Daniel