ERROR: "4.9E-324" is out of range for type double precision

Question

I saw this error when trying to perform the following insert into a PostgreSQL 8.4.20:

java.sql.BatchUpdateException: Batch entry 12 insert into perf_raw_2017_03_16 values ('3129', '182', '818', '2017-03-20 16:01:17.507000 +00:00:00', '4.9E-324') was aborted.  Call getNextException to see the cause.
org.postgresql.util.PSQLException: ERROR: "4.9E-324" is out of range for type double precision

However, when I try the same insert on a different machine with PostgreSQL 8.4.4, the insert finishes fine. When I do a select to verify that the value is there, it also finishes fine:

select * from perf_raw_2017_03_20 where guid_key=3129 and property_key=182;

 guid_key | property_key | instance_id_key |       time_stamp        |         value
----------+--------------+-----------------+-------------------------+-----------------------
     3129 |          182 |             818 | 2017-03-20 16:01:17.507 | 4.94065645841247e-324

The type of the column is double precision, which, according to the documentation, is "15 decimal digits precision". The error makes sense, I am indeed outside of its range, so I am not very surprised by the error. Here are my questions though:

The binary format of the DB does not change with minor releases. Why did this even work in 8.4.4 and not 8.4.20?

I did not find anything in the changelog that would explain this: https://bucardo.org/postgres_all_versions.html

Why does the DB still allow me to insert the value if I use a select to get it from an existing record?

I will probably post an answer to this question based on what I observed, but it would be cool if somebody could provide a deeper insight into this issue.

Answer 1

You're asking for software that was EOL in July 2014. It's far from supported. I'm not sure I trust git on those releases. The work transferring over the repo may have been sloppy. You may have to go back to SVN to get an authoritative answer but this seems to be responsible...

On some platforms, strtod() reports ERANGE for a denormalized value (ie, one that can be represented as distinct from zero, but is too small to have full precision). On others, it doesn't. It seems better to try to accept these values consistently, so add a test to see if the result value indicates a true out-of-range condition. This should be okay per Single Unix Spec. On machines where the underlying math isn't IEEE standard, the behavior for such small numbers may not be very consistent, but then it wouldn't be anyway.

Marti Raudsepp, after a proposal by Jeroen Vermeulen

You can see the thread about this here.