On Sun, Nov 14, 2010 at 9:16 PM, Greg Stark <gsstark@mit.edu> wrote:
> On Sun, Nov 14, 2010 at 11:15 AM, Daniel Farina <drfarina@acm.org> wrote:
>> SUM(int2) => int4
>> SUM(int4) => int8
>> SUM(int8) => numeric
>>
>> Some weaknesses:
>>
>> SUM, of any precision, assumes that the precision being accumulated
>> into (which is also the return-precision) is enough to avoid overflow.
>> This is generally the case, but there's no reason why it *must* be
>> true. I'm especially looking at the int2 to int4 conversion. One could
>> imagine a more interesting scenario where overflow behavior could
>> occur much more easily.
>
> Fwiw I think he's right that sum(int2) should perhaps be redefined to
> return int8. As it stands all it would take is a 64k rows to
> potentially overflow. It's not super likely but it is plausible and
> the performance penalty to use int8 wouldn't be super big either.
>
> int4 doesn't seem like as realistic a problem since it would take 4
> billion rows and the performance penalty for using numeric would be
> much higher.
I think you could get the same effect by removing sum(int2)
altogether, which I wouldn't lose any sleep over.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company