Re: BUG #19741: `avg(double precision)` overflows on cancelling finite inputs whose mean is representable

PG Bug reporting form <noreply(at)postgresql(dot)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