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

From: "Robert Brewer" <fumanchu(at)aminus(dot)org>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: SELECT '(1, nan, 3)'::cube;
Date: 2011-03-15 16:08:42
Message-ID: F1962646D3B64642B7C9A06068EE1E64110D759F@ex10.hostedexchange.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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. 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(at)aminus(dot)org

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2011-03-15 17:07:16 Re: SELECT '(1, nan, 3)'::cube;
Previous Message Robert Brewer 2011-03-15 15:57:46 SELECT '(1, nan, 3)'::cube