Infinities in type numeric

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Infinities in type numeric
Date: 2020-06-12 01:16:22
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

We had a discussion recently about how it'd be a good idea to support
infinity values in type numeric [1]. Here's a draft patch enabling
that, using the idea suggested in that thread of commandeering some
unused bits in the representation of numeric NaNs. AFAICT we've been
careful to ensure those bits are always zero, so that this will work
without creating any pg_upgrade problems.

This is just WIP, partly because I haven't touched the SGML docs
yet, but also because there are some loose ends to be resolved:

* I believe I made all the functions that correspond to POSIX-standard
functions do what POSIX says for infinite inputs. However, this does
not always match what our existing float8 functions do [2]. I'm
assuming that we'll change those functions to match POSIX; but if we
don't, this might need another look.

* I had to invent some semantics for non-standardized functions,
particularly numeric_mod, numeric_gcd, numeric_lcm. This area
could use review to be sure that I chose desirable behaviors.

* I'm only about 50% sure that I understand what the sort abbreviation
code is doing. A quick look from Peter or some other expert would be

* It seems to me that the existing behavior of numeric_stddev_internal
is not quite right for the case of a single input value that is a NaN,
when in "sample" mode. Per the comment "Sample stddev and variance are
undefined when N <= 1", ISTM that we ought to return NULL in this case,
but actually you get a NaN because the check for "NaNcount > 0" is made
before considering that. I think that's the wrong way round --- in some
sense NULL is "less defined" than NaN, so that's what we ought to use.
Moreover, the float8 stddev code agrees: in HEAD you get

regression=# SELECT stddev_samp('nan'::float8);

(1 row)

regression=# SELECT stddev_samp('nan'::numeric);
(1 row)

So I think we ought to make the numeric code match the former, and have
done that here. However, the float8 code has its own issues for the
population case [3], and depending on what we do about that, this might
need further changes to agree. (There's also the question of whether to
back-patch any such bug fixes.)

* The jsonpath code is inconsistent about how it handles NaN vs Inf [4].
I'm assuming here that we'll fix that by rejecting NaNs in that code,
but if we conclude that we do need to allow non-finite double values
there, probably we need to allow Infs too.

* It seems like there might be a use-case for isfinite() and maybe
isnan() SQL functions. On the other hand, we don't have those for
float4/float8 either. These could be a follow-on addition, anyway.

I'll stick this in the queue for review.

regards, tom lane


Attachment Content-Type Size
numeric-infinities-1.patch text/x-diff 111.0 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Ranier Vilela 2020-06-12 01:20:09 Re: [PATCH] fix two shadow vars (src/backend/commands/sequence.c)
Previous Message Darafei Komяpa Praliaskouski 2020-06-12 00:36:26 Re: exp() versus the POSIX standard