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
INSERT, even multiple statements in one call, is pretty slow. I've been
investigating if using COPY is faster. It is, but there's a problem:
some of the cubes should include 'NaN'::float. 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
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
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
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
numbers to fake the parser into returning Infinity.
2. If anyone can think of a way to trick the cube parser in the short
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
