1

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.

2

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.

  • @JohnEye the methodology to find this, if interested, is to find what triggers the error which was a one minute job with the error you produced, and to use git blame through the github user interface. In the event you want to know how I did it. There was a few commits that changed the white space on this code, I kept blaming-back until I found this commit in the date range applicable to 8.4 – Evan Carroll Mar 21 '17 at 18:32
  • Thank you, that's good to know. Apparently, I shouldn't have given up on the github search, most of the time it works so badly that I just search using a generic web search engine. – JohnEye Mar 22 '17 at 10:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.