Re: [HACKERS] Re: [BUGS] agregate function sum error - Mailing list pgsql-hackers

> > > > The limits are the same as for Postgres. The difference is that Postgres throws
> > > > an error on floating overflows, rather than returning "infinity".
> > >
> > > I think overflows on float8 is OK, the problem is about overflows on int4,
> > > int2 and float4.

What would it take to have backends start signalling ERROR (not core dump :) on integer
overflows? It would be nice to have the compile-time option; perhaps it is already
somewhere in the backend.

The SQL92 standard requires an abort on floating overflows, though _just_ because the
standard says it doesn't mean we have to do it. We should do what makes sense. However,
once an IEEE value becomes "Inf" further math operations keep it at "Inf", which
propagates the problem farther into your calculation. Probably better to signal the
error and let the user fix the overflow problem first. However, if you want to generate
compile-time patches...

> > > If PostgreSQL does all calculation using float8 (even on int2, int4 and float4)
> > > we have acceptable overflows. Why we don't do this in this way ?
> > Can you imagine the problems we would have?  Integral types and floats
> > are two different things.  They behave differently in division,
> > rounding, and overflows.  For those reasons, and performance, we can't
> > just use floats all the time.
> >
> I see..., then what we need is int8 as Tom said, to avoid to write code as:
>
> select cast mysmall as int4 * cast mysmall as int4 from mytable;
> select mysmall::int4 * mysmall::int4 from mytable;
> select int4(mysmall) * int4(mysmall) from mytable;
> select sum(int4(mysmall)) from mytable;
>
> that has nothing to do with SQL standard, and isn't too friendly,
> those conversions should be done automaticly by PostgreSQL whitout
> user's help as MySQL, Solid and other databases do.

Well, not all real databases do this, though I see your point. There is a performance
tradeoff between always promoting types when doing arithmetic and leaving the type
consistant. Also, "hidden" promotions may be less intuitive for some users, and lead to
unexpected behavior elsewhere.

> PS: I installed int8 in my Linux box, but I have an error during insert time:
> insert into qtest values('123','456');
> ERROR:  fmgr_info: function 159745: cache lookup failed

Hmm. Works on my machine (980217 cvs tree):

create table qtest(q1 int8, q2 int8);
CREATE
...
insert into qtest values('123','4567890123456789');
INSERT 1018571 1
...
select * from qtest;
              q1|               q2
----------------+-----------------
             123|              456
             123| 4567890123456789
4567890123456789|              123
4567890123456789| 4567890123456789
4567890123456789|-4567890123456789
(5 rows)

Did you try from a clean database? How about from a clean install??

                                                  - Tom