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

Tom Lane wrote:
> "Robert Brewer" <fumanchu@aminus.org> writes:
> > 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:

> > 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.
>
> cube_in doesn't accept either 'nan' or 'inf'.  It's probably a bug
that
> you can get those things into a cube value via cube(float8[]).  Or we
> could see about upgrading the datatype to allow them, but that would
> require looking at all its operations not just cube_in.  It seems
pretty
> likely to me that there are some other things in that module that
won't
> behave sanely with NaN, because the original author clearly never
> thought about it.
>
> I'd suggest rethinking your design to avoid needing NaN in a cube.

Thanks Tom,

I could, of course, use a sentinel value like -1 or even -Inf to mean
"indeterminate", but I'd like future versions of this app (wink) to be
able to use qnan's as IEEE 754 intended. Note that cube doesn't take
NULL either. It may be surprising and underspecified, but using NaN and
Inf in other cube operations (including gist indices) does indeed work
quite well--probably because they are, after all, just floats. I believe
it's a bug to handle only some subset of values of an extremely
well-known and well-specified datatype like float, but of course I
understand if contrib modules aren't high priority.


Robert Brewer
fumanchu@aminus.org