SELECT '(1, nan, 3)'::cube;

Hi folks,

I'm working on a hypercube implementation in Postgres using contrib/cube

and need to insert 80,000 rows in one go from Python. Doing so with=20
INSERT, even multiple statements in one call, is pretty slow. I've been=20
investigating if using COPY is faster. It is, but there's a problem:=20
some of the cubes should include NaN. Writing:

    INSERT INTO foo (coords) VALUES (cube(ARRAY[1, 'nan', 3]::float[]));

...works fine. But I can't find the magic incantation to do the same=20
thing using COPY FROM. Indeed, even SELECT chokes on this:

     # SELECT '(1, nan, 3)'::cube;
     ERROR:  bad cube representation
     LINE 1: select '(1, nan, 3)'::cube;
                ^
     DETAIL:  syntax error at or near "n"

Note that the 'cube' datatype also doesn't have a 'binary' repr, and=20
COPY FROM exhibits the same problem with CSV as it does with text.

I actually found out I could trick the parser into storing 'Inf' by just

giving it a very large number, like '(1, 1e+309, 3)::cube', but I can't=20
figure out an equivalent trick for NaN.

Also note that 'nan' and 'inf' are not a problem for float[]

     # select '{1, nan, inf}'::float[];
       float8
     -----------
      {1,NaN,Infinity}
     (1 row)

...but 1e+309 raises an error for float (where it does not for cube):

     # select '{1, 1e+309, 3}'::float[];
     ERROR:  "1e+309" is out of range for type double precision
     LINE 1: select '{1, 1e+309, 3}'::float[];

So:

  1. It would be good to have cube parsing in PG behave more like float,

allowing 'nan' and 'inf', and once those are working, deny using large=20
numbers to fake the parser into returning Infinity.
  2. If anyone can think of a way to trick the cube parser in the short=20
term into returning NaN, I'd love to hear it.
  3. If anyone understands cubeparse.c better than I do and can shoot me

a quick patch, I'd appreciate it.


Robert Brewer
fumanchu@aminus.org