Re: Rounding to even for numeric data type

On 27 March 2015 at 23:26, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Michael Paquier <michael(dot)paquier(at)gmail(dot)com> writes:
>> It sounds appealing to switch the default behavior to something that
>> is more IEEE-compliant, and not only for scale == 0. Now one can argue
>> as well that changing the default is risky for existing applications,
>> or the other way around that other RDBMs (?) are more compliant than
>> us for their equivalent numeric data type, and people get confused
>> when switching to Postgres.
>
>> An idea, from Dean, would be to have a new specific version for
>> round() able to do compliant IEEE rounding to even as well...
>
> I think confining the change to round() would be a fundamental error.
> The main reason why round-to-nearest-even is IEEE standard is that it
> reduces error accumulation over long chains of calculations, such as
> in numeric's power and trig functions; if we go to the trouble of
> implementing such a behavior, we certainly want to use it there.
>

Sure, using round-to-nearest-even for intermediate rounding in complex
numeric methods would be a good way to reduce (but not completely
eliminate) rounding errors. But that's a somewhat different
proposition from changing the default for round(), which is a much
more user-visible change. If we did implement a choice of rounding
modes, I would still argue for keeping round-half-away-from-zero as
the default mode for round().

> I think the concern over backwards compatibility here is probably
> overblown; but if we're sufficiently worried about it, a possible
> compromise is to invent a numeric_rounding_mode GUC, so that people
> could get back the old behavior if they really care.
>

Backwards compatibility is certainly one concern. Michael also
mentioned compatibility with other databases, and its worth noting
that Oracle, MySQL, DB2 and SQL Server all use the same default
round-half-away-from-zero "Schoolbook" rounding mode in round() for
their equivalents of numeric. Most of those other DBs are also careful
to document exactly how round() behaves. To make our round() function
do something different by default isn't going to make porting any
easier.

Andrew mentioned that there have been complaints from people doing
calculations with monetary data that we don't implement
round-to-nearest-even (Banker's) rounding. It's actually the case that
various different financial calculations demand different specific
rounding modes, so it wouldn't be enough to simply change the default
- we would have to provide a choice of modes. I also agree with Andrew
that all numeric functions should be kept immutable.

Regards,
Dean