postgres issues with doubles

[dhalperi]

See query http://vega.cs.washington.edu:1776/query/query-410:

AllNorm = SCAN(armbrustlab:seaflow:allnorm2);

AllBounds = SELECT Cruise, Day, File_Id
     , MIN(pe_norm) as min_pe
     , MIN(fsc_small_norm) as min_fsc_small
     , MIN(fsc_perp_norm) as min_fsc_perp
     , MIN(chl_small_norm) as min_chl_small
     , MAX(pe_norm) as max_pe
     , MAX(fsc_small_norm) as max_fsc_small
     , MAX(fsc_perp_norm) as max_fsc_perp
     , MAX(chl_small_norm) as max_chl_small
FROM AllNorm;

STORE(AllBounds, armbrustlab:seaflow:allbounds2);

This query, which seemingly inserts data that is read from the database, throws the following error:

edu.washington.escience.myria.DbException: org.postgresql.util.PSQLException: ERROR: \"4.9E-324\" is out of range for type double precision\n  Where: COPY armbrustlab MyriaSysTemp allbounds2, line 55, column max_pe: \"4.9E-324\"

Looks like this is a known issue and a bug of some sort in PostgreSQL 1. And 2. There may even be a fix 3, but it is not in postgres 9.1 that we currently run.

[dhalperi]

@domoritz as the postgres admin
@7andrew7 as the person who has experience dealing with the shit show that doubles induce

[7andrew7]

I have no special insight; that number is well within range, as doubles have 11 exponent bits.

I just checked that a C program can ingest that number; no problem:

double c = 4.9E-324;

[dhalperi]

It's a denormalized double and postgres doesn't handle those well. I'm still at a loss to see how printing the double doesn't let us put it back in the database.. however I was able to work around the issue by long-circuiting @domoritz 's COPY trick when there are doubles or floats present.

uwescience/myria@ae78e66

There is also a binary copy we should check out if this is something we want to pursue.