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