agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedFrom: Tom Lane <tgl@sss.pgh.pa.us>
To: theshallow27@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19741: `avg(double precision)` overflows on cancelling finite inputs whose mean is representable
Date: Sat, 03 Oct 2026 17:34:45 -0400
Message-ID: <259950.1791063285@sss.pgh.pa.us> (raw)
In-Reply-To: <19741-01386c4f17e36788@postgresql.org>
References: <19741-01386c4f17e36788@postgresql.org>
PG Bug reporting form <noreply@postgresql.org> writes:
> The arithmetic mean of two finite values, `1e154` and `-1e154`, is exactly
> zero. PostgreSQL's `avg(double precision)` raises an overflow instead.
That happens because avg() shares its transition function "float8_accum"
with some other aggregates that require tracking sum(x^2) as well as
sum(x); it's the sum(x^2) that overflows. We could avoid it by giving
avg() a dedicated function that only counts sum(x) and N ... but I'm
skeptical that that's worth the trouble. If you're trying to perform
calculations that are as numerically unstable as this example in
float8, it's probably mostly garbage-in-garbage-out anyway.
If I had to do something like this in float8, I'd probably do
select sum(x order by abs(x)) / count(x) from ...
to try to reduce roundoff and cancellation error. But we're not going
to make the bare aggregate do that. Another answer could be to cast
the aggregate input to numeric, though that'll be a good deal slower
in its own way.
regards, tom lane
view thread (2+ messages)
Message-ID: <259950.1791063285@sss.pgh.pa.us>
Permalink: ../259950.1791063285@sss.pgh.pa.us/
Also on: postgresql.org/message-id/259950.1791063285@sss.pgh.pa.us
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, theshallow27@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19741: `avg(double precision)` overflows on cancelling finite inputs whose mean is representable
In-Reply-To: <259950.1791063285@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox