Re: [HACKERS] 6.5.0 - Overflow bug in AVG( )

Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu> writes:
>> Some broader solution should be considered though if you
>> want AVG to work on numeric/decimal as well.

> The implementation can be specified for each datatype individually,

In the current implementation, each datatype does use its own type as
the accumulator --- and also as the counter. float8 and numeric are
fine, float4 is sort of OK (a float8 accumulator would be better for
accuracy reasons), int4 loses, int2 loses *bad*.

To fix it we'd need to invent operators that do the appropriate cross-
data-type operations. For example, int4 avg using float8 accumulator
would need "float8 + int4 yielding float8" and "float8 / int4 yielding
int4", neither of which are to be found in pg_proc at the moment. But
it's a straightforward thing to do.

int8 is the only integer type that I wouldn't want to use a float8
accumulator for. Maybe numeric would be the appropriate thing here,
slow though it be.

Note that switching over to float accumulation would *not* be real
palatable until we have fixed the memory-leak issue. avg() on int4
doesn't leak memory currently, but it would with a float accumulator...

regards, tom lane