Re: Add PRODUCT() aggregate function - Mailing list pgsql-hackers

Hi Jeevan,

Thanks for the explanation and the pro() example, it is convincing.
I hadn't considered this properly earlier. for SUM the overflow
depends only on number of rows, but for PRODUCT it depends on the
values itself, so it will overflow in very few steps whenever values
are more than 1. So native fast path will help only for columns
having mostly 0, 1 or -1, not for large tables in general. I also
checked int128.h and you are right, there is no existing function
for overflow-checked "int128 *= int64" type of multiply, so this
needs new code, not reuse of the SUM(int8) pattern. So agree, fine to
take this up later as a follow-up, no need to block on it.

About your question on float8 vs numeric for float variants - I
would prefer float8. sum(float4)/sum(float8) already return
float4/float8, not numeric, so PRODUCT() staying same for float
types will be more consistent. It also avoids the overflow-primitive
problem for floats, since float just becomes Infinity instead of
erroring out. For int2/int4/int8 numeric is fine as it is.