Thread: Rounding to even for numeric data type

Rounding to even for numeric data type

From
Michael Paquier
Date:
Hi all,

A couple of days ago a bug has showed up regarding rounding of float here:

http://www.postgresql.org/message-id/flat/20150320194337.2573.72944@wrigleys.postgresql.org#20150320194337.2573.72944@wrigleys.postgresql.org
The result being that the version of rint() shipped in src/port was
not IEEE compliant when rounding to even (MSVC < 2013 at least using
it), leading to inconsistent results depending on if the platform uses
src/port's rint() or the platform's one.

During this thread, Tom has raised as well that rounding for numeric
is not that IEEE-compliant:
http://www.postgresql.org/message-id/22366.1427313454@sss.pgh.pa.us

For example:
=# SELECT round(2.5::numeric), round(1.5::numeric),
round(0.5::numeric), round(-2.5::numeric);round | round | round | round
-------+-------+-------+-------    3 |     2 |     1 |    -3
(1 row)
=# SELECT round(2.5::float), round(1.5::float), round(0.5::float),
round(-2.5::float);round | round | round | round
-------+-------+-------+-------    2 |     2 |     0 |    -2
(1 row)

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...

Opinions?
Regards,
-- 
Michael



Re: Rounding to even for numeric data type

From
Tom Lane
Date:
Michael Paquier <michael.paquier@gmail.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.

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.
        regards, tom lane



Re: Rounding to even for numeric data type

From
Andrew Gierth
Date:
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
Tom> I think the concern over backwards compatibility here is probablyTom> overblown; but if we're sufficiently worried
aboutit, a possibleTom> compromise is to invent a numeric_rounding_mode GUC, so thatTom> people could get back the old
behaviorif they really care.
 

I only see one issue with this, but it's a nasty one: do we really want
to make all numeric operations that might do rounding stable rather than
immutable?

-- 
Andrew (irc:RhodiumToad)



Re: Rounding to even for numeric data type

From
Dean Rasheed
Date:
On 27 March 2015 at 23:26, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Michael Paquier <michael.paquier@gmail.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



Re: Rounding to even for numeric data type

From
Dean Rasheed
Date:
On 28 March 2015 at 05:16, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:
>>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
>
>  Tom> I think the concern over backwards compatibility here is probably
>  Tom> overblown; but if we're sufficiently worried about it, a possible
>  Tom> compromise is to invent a numeric_rounding_mode GUC, so that
>  Tom> people could get back the old behavior if they really care.
>
> I only see one issue with this, but it's a nasty one: do we really want
> to make all numeric operations that might do rounding stable rather than
> immutable?
>

Yeah, making all numeric functions non-immutable seems like a really bad idea.

Regards,
Dean



Re: Rounding to even for numeric data type

From
Michael Paquier
Date:
On Sat, Mar 28, 2015 at 5:58 PM, Dean Rasheed wrote:
> On 27 March 2015 at 23:26, Tom Lane wrote:
>> 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.

I was not aware of that, and that's really an interesting point.
Thanks! It would indeed not be welcome for people migrating an
application to Postgres if we behave differently from the others.
Then, perhaps the solution would be to have this rounding GUC, but
pointing by default to round-half-away-from-zero and not round-to-even
as mentioned upthread already.

> 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.

This looks like a plan. Honestly by reading this thread the thing that
IMO we should not do is closing ourselves into a single mode of
calculation.
-- 
Michael



Re: Rounding to even for numeric data type

From
Kevin Grittner
Date:
Dean Rasheed <dean.a.rasheed@gmail.com> wrote:

> 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'm inclined to agree.  In business software development, that's
how I've seen the "stakeholder" expectations.  Thinking back, I can
remember dealing with rounding in manufacturing incentive pay
calculation, interfacing long-range demand forcasting to production
planning, interfacing engineers' CAD/CAM software to IBM MAPICS,
professionals' timekeeping/billing/AR systems, and various general
accounting software systems; and as I seem to remember those
efforts, round half away from zero has always been when end users
understood and expected when explicitly rounding a final result.

I understand how rounding half to even in intermediate results
minimizes rounding error, and would not be surprised to see some
users with different expectations, but there is clearly a large
base of people who would be surprised by it when rounding a final
result.

> I also agree with Andrew that all numeric functions should be
> kept immutable.

Which means no GUC should affect how it behaves, although a
function with a parameter to control rounding behavior would be OK.

It kinda seems like the SQL round() function should have a
parameter to control this which defaults to the historical behavior
when omitted.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company



Re: Rounding to even for numeric data type

From
Gavin Flower
Date:
On 28/03/15 21:58, Dean Rasheed wrote:
[...]
>
> 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.
[...]

Could the 2 current round functions have cousins that included an extra 
char parameter (or string), that indicated the type of rounding?

So we don't end up with an explosion of rounding functions, yet could 
cope with a limited set of additional rounding modes initially, and 
possibly others in the future.


Cheers,
Gavin





Re: Rounding to even for numeric data type

From
Michael Paquier
Date:
On Sun, Mar 29, 2015 at 5:34 AM, Gavin Flower
<GavinFlower@archidevsys.co.nz> wrote:
> On 28/03/15 21:58, Dean Rasheed wrote:
> [...]
>>
>>
>> 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.
>
> [...]
>
> Could the 2 current round functions have cousins that included an extra char
> parameter (or string), that indicated the type of rounding?
>
> So we don't end up with an explosion of rounding functions, yet could cope
> with a limited set of additional rounding modes initially, and possibly
> others in the future.

Instead of extending round, isn't what we are looking at here a new
data type? I have doubts that we only want to have a way to switch
round() between different modes. Hence, what we could do is:
1) Mention in the docs that numeric does round-half-away-from-zero
2) Add regression tests for numeric(n,m) and round(numeric)
3) Add a TODO item for something like numeric2, doing rounding-at-even
(this could be an extension as well), but with the number of
duplication that it may have with numeric, an in-core type would make
sense, to facilitate things exposing some of structures key structures
would help.

Regards,
-- 
Michael



Re: Rounding to even for numeric data type

From
"David G. Johnston"
Date:
On Sat, Mar 28, 2015 at 3:59 PM, Michael Paquier <michael.paquier@gmail.com> wrote:
On Sun, Mar 29, 2015 at 5:34 AM, Gavin Flower
<GavinFlower@archidevsys.co.nz> wrote:
> On 28/03/15 21:58, Dean Rasheed wrote:
> [...]
>>
>>
>> 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.
>
> [...]
>
> Could the 2 current round functions have cousins that included an extra char
> parameter (or string), that indicated the type of rounding?
 
>
> So we don't end up with an explosion of rounding functions, yet could cope
> with a limited set of additional rounding modes initially, and possibly
> others in the future.

Instead of extending round, isn't what we are looking at here a new
data type? I have doubts that we only want to have a way to switch
round() between different modes. Hence, what we could do is:
1) Mention in the docs that numeric does round-half-away-from-zero
2) Add regression tests for numeric(n,m) and round(numeric)
3) Add a TODO item for something like numeric2, doing rounding-at-even
(this could be an extension as well), but with the number of
duplication that it may have with numeric, an in-core type would make
sense, to facilitate things exposing some of structures key structures
would help.


So, create a numeric type for each possible rounding mode​?  That implies at least two types, round-half-even and round-half-away-from-zero, with suitable abbreviations: numeric_rhe, numeric_raz.

If the goal is to make plain numeric IEEE standard conforming then giving the user a way to switch all existing numeric types to numeric_raz would be nice.

Implicit casts between each of the various numeric types would be needed and understandable.

I'm pondering calling them numeric_eng and numeric_bus (for engineering and business respectively)...

David J.

Re: Rounding to even for numeric data type

From
Gavin Flower
Date:
On 29/03/15 13:07, David G. Johnston wrote:
> On Sat, Mar 28, 2015 at 3:59 PM, Michael Paquier 
> <michael.paquier@gmail.com <mailto:michael.paquier@gmail.com>>wrote:
>
>     On Sun, Mar 29, 2015 at 5:34 AM, Gavin Flower
>     <GavinFlower@archidevsys.co.nz
>     <mailto:GavinFlower@archidevsys.co.nz>> wrote:
>     > On 28/03/15 21:58, Dean Rasheed wrote:
>     > [...]
>     >>
>     >>
>     >> 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.
>     >
>     > [...]
>     >
>     > Could the 2 current round functions have cousins that included
>     an extra char
>     > parameter (or string), that indicated the type of rounding? 
>
>     >
>     > So we don't end up with an explosion of rounding functions, yet
>     could cope
>     > with a limited set of additional rounding modes initially, and
>     possibly
>     > others in the future.
>
>     Instead of extending round, isn't what we are looking at here a new
>     data type? I have doubts that we only want to have a way to switch
>     round() between different modes. Hence, what we could do is:
>     1) Mention in the docs that numeric does round-half-away-from-zero
>     2) Add regression tests for numeric(n,m) and round(numeric)
>     3) Add a TODO item for something like numeric2, doing rounding-at-even
>     (this could be an extension as well), but with the number of
>     duplication that it may have with numeric, an in-core type would make
>     sense, to facilitate things exposing some of structures key structures
>     would help.
>
>
> So, create a numeric type for each possible rounding mode​? That 
> implies at least two types, round-half-even and 
> round-half-away-from-zero, with suitable abbreviations: numeric_rhe, 
> numeric_raz.
>
> If the goal is to make plain numeric IEEE standard conforming then 
> giving the user a way to switch all existing numeric types to 
> numeric_raz would be nice.
>
> Implicit casts between each of the various numeric types would be 
> needed and understandable.
>
> I'm pondering calling them numeric_eng and numeric_bus (for 
> engineering and business respectively)...
>
> David J.
>
In Java, there are 8 rounding modes specified:

https://docs.oracle.com/javase/8/docs/api/java/math/RoundingMode.html

Some of these may be relevant to pg.


Cheers,
Gavin



Re: Rounding to even for numeric data type

From
Michael Paquier
Date:
On Sun, Mar 29, 2015 at 9:21 AM, Gavin Flower
<GavinFlower@archidevsys.co.nz> wrote:
> On 29/03/15 13:07, David G. Johnston wrote:
>>
>> On Sat, Mar 28, 2015 at 3:59 PM, Michael Paquier
>> <michael.paquier@gmail.com <mailto:michael.paquier@gmail.com>>wrote:
>>
>>     On Sun, Mar 29, 2015 at 5:34 AM, Gavin Flower
>>     <GavinFlower@archidevsys.co.nz
>>     <mailto:GavinFlower@archidevsys.co.nz>> wrote:
>>     > On 28/03/15 21:58, Dean Rasheed wrote:
>>     > [...]
>>     >>
>>     >>
>>     >> 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.
>>     >
>>     > [...]
>>     >
>>     > Could the 2 current round functions have cousins that included
>>     an extra char
>>     > parameter (or string), that indicated the type of rounding?
>>     >
>>     > So we don't end up with an explosion of rounding functions, yet
>>     could cope
>>     > with a limited set of additional rounding modes initially, and
>>     possibly
>>     > others in the future.
>>
>>     Instead of extending round, isn't what we are looking at here a new
>>     data type? I have doubts that we only want to have a way to switch
>>     round() between different modes. Hence, what we could do is:
>>     1) Mention in the docs that numeric does round-half-away-from-zero
>>     2) Add regression tests for numeric(n,m) and round(numeric)
>>     3) Add a TODO item for something like numeric2, doing rounding-at-even
>>     (this could be an extension as well), but with the number of
>>     duplication that it may have with numeric, an in-core type would make
>>     sense, to facilitate things exposing some of structures key structures
>>     would help.
>>
>>
>> So, create a numeric type for each possible rounding mode? That implies at
>> least two types, round-half-even and round-half-away-from-zero, with
>> suitable abbreviations: numeric_rhe, numeric_raz.

The existing numeric now does half-up rounding.

>> If the goal is to make plain numeric IEEE standard conforming then giving
>> the user a way to switch all existing numeric types to numeric_raz would be
>> nice.
>>
>> Implicit casts between each of the various numeric types would be needed
>> and understandable.

That's exactly the thing I think would be helpful.

>> I'm pondering calling them numeric_eng and numeric_bus (for engineering
>> and business respectively)...
>
> In Java, there are 8 rounding modes specified:
>
> https://docs.oracle.com/javase/8/docs/api/java/math/RoundingMode.html
> Some of these may be relevant to pg.

That's interesting. I didn't recall those details.
Regards,
-- 
Michael



Re: Rounding to even for numeric data type

From
Michael Paquier
Date:
On Sun, Mar 29, 2015 at 7:59 AM, Michael Paquier
<michael.paquier@gmail.com> wrote:
> On Sun, Mar 29, 2015 at 5:34 AM, Gavin Flower
> <GavinFlower@archidevsys.co.nz> wrote:
>> On 28/03/15 21:58, Dean Rasheed wrote:
>> [...]
>>>
>>>
>>> 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.
>>
>> [...]
>>
>> Could the 2 current round functions have cousins that included an extra char
>> parameter (or string), that indicated the type of rounding?
>>
>> So we don't end up with an explosion of rounding functions, yet could cope
>> with a limited set of additional rounding modes initially, and possibly
>> others in the future.
>
> Instead of extending round, isn't what we are looking at here a new
> data type? I have doubts that we only want to have a way to switch
> round() between different modes. Hence, what we could do is:
> 1) Mention in the docs that numeric does round-half-away-from-zero
> 2) Add regression tests for numeric(n,m) and round(numeric)
> 3) Add a TODO item for something like numeric2, doing rounding-at-even
> (this could be an extension as well), but with the number of
> duplication that it may have with numeric, an in-core type would make
> sense, to facilitate things exposing some of structures key structures
> would help.

So, attached is a patch that does 1) and 2) to make clear to the user
how numeric and double precision behave regarding rounding. I am
adding it to CF 2015-06 to keep track of it...
--
Michael

Attachment

Re: Rounding to even for numeric data type

From
James Cloos
Date:
>>>>> "MP" == Michael Paquier <michael.paquier@gmail.com> writes:

MP> So, attached is a patch that does 1) and 2) to make clear to the
MP> user how numeric and double precision behave regarding rounding.
MP> I am adding it to CF 2015-06 to keep track of it...

Given that the examples show -2.5 rounds to -3, the IEEE term is
roundTiesToAway, and the typical conversational english is round ties
away from zero.

RoundUp means mean towards +Infinity.

754 specifies that for decimal, either roundTiesToEven or roundTiesToAway
are acceptable defaults, and which of the two applies is language dependent.
Does ANSI SQL say anything about how numeric should round?

In general, for decimals (or anything other than binary), there are
twelve possible roundings:
ToEven ToOdd AwayFromZero ToZero Up DownTiesToEven TiesToOdd TiesAwayFromZero TiesToZero TiesUp TiesDown

(Up is the same as ceil(3), Down as floor(3).)

-JimC
-- 
James Cloos <cloos@jhcloos.com>         OpenPGP: 0x997A9F17ED7DAEA6



Re: Rounding to even for numeric data type

From
Michael Paquier
Date:
On Mon, Mar 30, 2015 at 4:51 AM, James Cloos <cloos@jhcloos.com> wrote:
>>>>>> "MP" == Michael Paquier <michael.paquier@gmail.com> writes:
>
> MP> So, attached is a patch that does 1) and 2) to make clear to the
> MP> user how numeric and double precision behave regarding rounding.
> MP> I am adding it to CF 2015-06 to keep track of it...
>
> Given that the examples show -2.5 rounds to -3, the IEEE term is
> roundTiesToAway, and the typical conversational english is round ties
> away from zero.

Ah, thanks for the correct wording. Fixed in the attached.

> RoundUp means mean towards +Infinity.
>
> 754 specifies that for decimal, either roundTiesToEven or roundTiesToAway
> are acceptable defaults, and which of the two applies is language dependent.
> Does ANSI SQL say anything about how numeric should round?
>
> In general, for decimals (or anything other than binary), there are
> twelve possible roundings:
>
>  ToEven ToOdd AwayFromZero ToZero Up Down
>  TiesToEven TiesToOdd TiesAwayFromZero TiesToZero TiesUp TiesDown
>
> (Up is the same as ceil(3), Down as floor(3).)

Well, I am not sure about that... But reading this thread changing the
default rounding sounds unwelcome. So it may be better to just put in
words the rounding method used now in the docs, with perhaps a mention
that this is not completely in-line with the SQL spec if that's not
the case.
--
Michael

Attachment

Re: Rounding to even for numeric data type

From
Albe Laurenz
Date:
Michael Paquier wrote:
> Well, I am not sure about that... But reading this thread changing the
> default rounding sounds unwelcome. So it may be better to just put in
> words the rounding method used now in the docs, with perhaps a mention
> that this is not completely in-line with the SQL spec if that's not
> the case.

The SQL standard does not care, it says that numbers and other data types
should, whenever necessary, be rounded or truncated in an implementation-
defined fashion.

I cannot find any mention of a round() function.

Yours,
Laurenz Albe

Re: Rounding to even for numeric data type

From
Michael Paquier
Date:
On Wed, Apr 22, 2015 at 9:30 PM, Pedro Gimeno
<pgsql-004@personal.formauri.es> wrote:
> Dean Rasheed wrote, On 2015-03-28 10:01:
>> On 28 March 2015 at 05:16, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:
>>>>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
>>>
>>>  Tom> I think the concern over backwards compatibility here is probably
>>>  Tom> overblown; but if we're sufficiently worried about it, a possible
>>>  Tom> compromise is to invent a numeric_rounding_mode GUC, so that
>>>  Tom> people could get back the old behavior if they really care.
>>>
>>> I only see one issue with this, but it's a nasty one: do we really want
>>> to make all numeric operations that might do rounding stable rather than
>>> immutable?
>>>
>>
>> Yeah, making all numeric functions non-immutable seems like a really bad idea.
>
> Would it be possible to make it an unchangeable per-cluster or
> per-database setting, kinda like how encoding behaves? Wouldn't that
> allow to keep the functions immutable?

Rounding is not something that can be enforced at the database or
server level but at data type level, see for example the differences
already present for double precision and numeric as mentioned
upthread. In short, you could keep rounding functions immutable by
having one data type with a different rounding method. At least that's
an idea.
-- 
Michael



Re: Rounding to even for numeric data type

From
Pedro Gimeno
Date:
Dean Rasheed wrote, On 2015-03-28 10:01:
> On 28 March 2015 at 05:16, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:
>>>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
>>
>>  Tom> I think the concern over backwards compatibility here is probably
>>  Tom> overblown; but if we're sufficiently worried about it, a possible
>>  Tom> compromise is to invent a numeric_rounding_mode GUC, so that
>>  Tom> people could get back the old behavior if they really care.
>>
>> I only see one issue with this, but it's a nasty one: do we really want
>> to make all numeric operations that might do rounding stable rather than
>> immutable?
>>
> 
> Yeah, making all numeric functions non-immutable seems like a really bad idea.

Would it be possible to make it an unchangeable per-cluster or
per-database setting, kinda like how encoding behaves? Wouldn't that
allow to keep the functions immutable?




Re: Rounding to even for numeric data type

From
Fabien COELHO
Date:
> So, attached is a patch that does 1) and 2) to make clear to the user
> how numeric and double precision behave regarding rounding. I am
> adding it to CF 2015-06 to keep track of it...

Quick review: patches applies, make check is fine, all is well.

Two minor suggestions:

All the casting tests could be put in "numeric.sql", as there are all 
related to numeric and that would avoid duplicating the values lists.

For the documentation, I would also add 3.5 so that rounding to even is 
even clearer:-)

-- 
Fabien.



Re: Rounding to even for numeric data type

From
Michael Paquier
Date:
On Sat, May 2, 2015 at 9:53 PM, Fabien COELHO wrote:
> Quick review: patches applies, make check is fine, all is well.

Thanks for the feedback, Fabien!

> All the casting tests could be put in "numeric.sql", as there are all
> related to numeric and that would avoid duplicating the values lists.

Not sure about that, the tests are placed here to be consistent with
for is done for float8.

> For the documentation, I would also add 3.5 so that rounding to even is even
> clearer:-)

Good idea. I reworked the example in the docs.
--
Michael

Attachment

Re: Rounding to even for numeric data type

From
Fabien COELHO
Date:
v2 applied & tested.

> [...] Not sure about that, the tests are placed here to be consistent 
> with for is done for float8.

Maybe float8 to numeric casts could have been in numeric too.

> [...] I reworked the example in the docs.

Indeed, looks good.

-- 
Fabien.



Re: Rounding to even for numeric data type

From
Tom Lane
Date:
Michael Paquier <michael.paquier@gmail.com> writes:
> On Sat, May 2, 2015 at 9:53 PM, Fabien COELHO wrote:
>> Quick review: patches applies, make check is fine, all is well.

> Thanks for the feedback, Fabien!

>> All the casting tests could be put in "numeric.sql", as there are all
>> related to numeric and that would avoid duplicating the values lists.

> Not sure about that, the tests are placed here to be consistent with
> for is done for float8.

>> For the documentation, I would also add 3.5 so that rounding to even is even
>> clearer:-)

> Good idea. I reworked the example in the docs.

Pushed with minor adjustments --- you missed updating
int8-exp-three-digits.out, and I thought the documentation wording
could be better.
        regards, tom lane