Thread: NUMERIC's transcendental functions
I have noticed a change in behavior following the recent changes for
casting of numeric constants. In prior releases, we got
regression=# select log(10.1); log
------------------1.00432137378264
(1 row)
CVS tip gives
regression=# select log(10.1); log
--------------1.0043213738
(1 row)
The reason for the change is that 10.1 used to be implicitly typed as
float8, but now it's typed as numeric, so this command invokes
log(numeric) instead of log(float8). And log(numeric)'s idea of
adequate output precision seems low.
Similar problems occur with sqrt(), exp(), ln(), pow().
I realize that there's a certain amount of cuteness in being able to
calculate these functions to arbitrary precision, but this is a database
not a replacement for bc(1). ISTM the numeric datatype is intended for
exact calculations, and so transcendental functions (which inherently
have inexact results) don't belong.
So proposal #1 is to rip out the numeric versions of these functions.
If you're too attached to them, proposal #2 is to force them to
calculate at least 16 digits of output, so that we aren't losing any
accuracy compared to prior behavior.
Comments?
regards, tom lane
When you say: > So proposal #1 is to rip out the numeric versions of these functions. you mean remove the ability to do transendentals on numerics to prevent such unusual auto-casting? Are you suggesting that in all other cases, autocast to numeric is OK? I am a little confused. --------------------------------------------------------------------------- Tom Lane wrote: > I have noticed a change in behavior following the recent changes for > casting of numeric constants. In prior releases, we got > > regression=# select log(10.1); > log > ------------------ > 1.00432137378264 > (1 row) > > CVS tip gives > > regression=# select log(10.1); > log > -------------- > 1.0043213738 > (1 row) > > The reason for the change is that 10.1 used to be implicitly typed as > float8, but now it's typed as numeric, so this command invokes > log(numeric) instead of log(float8). And log(numeric)'s idea of > adequate output precision seems low. > > Similar problems occur with sqrt(), exp(), ln(), pow(). > > I realize that there's a certain amount of cuteness in being able to > calculate these functions to arbitrary precision, but this is a database > not a replacement for bc(1). ISTM the numeric datatype is intended for > exact calculations, and so transcendental functions (which inherently > have inexact results) don't belong. > > So proposal #1 is to rip out the numeric versions of these functions. > > If you're too attached to them, proposal #2 is to force them to > calculate at least 16 digits of output, so that we aren't losing any > accuracy compared to prior behavior. > > Comments? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Tom Lane wrote: > > I have noticed a change in behavior following the recent changes for > casting of numeric constants. In prior releases, we got > > regression=# select log(10.1); > log > ------------------ > 1.00432137378264 > (1 row) > > CVS tip gives > > regression=# select log(10.1); > log > -------------- > 1.0043213738 > (1 row) > > The reason for the change is that 10.1 used to be implicitly typed as > float8, but now it's typed as numeric, so this command invokes > log(numeric) instead of log(float8). And log(numeric)'s idea of > adequate output precision seems low. > > Similar problems occur with sqrt(), exp(), ln(), pow(). > > I realize that there's a certain amount of cuteness in being able to > calculate these functions to arbitrary precision, but this is a database > not a replacement for bc(1). ISTM the numeric datatype is intended for > exact calculations, and so transcendental functions (which inherently > have inexact results) don't belong. > > So proposal #1 is to rip out the numeric versions of these functions. > > If you're too attached to them, proposal #2 is to force them to > calculate at least 16 digits of output, so that we aren't losing any > accuracy compared to prior behavior. > > Comments? One problem is, that division already has an inherently inexact result. Do you intend to rip that out too while at it? (Just kidding) Proposal #2.667 would be to have a GUC variable for the default precision. Jan > > regards, tom lane -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Jan Wieck <JanWieck@Yahoo.com> writes:
> One problem is, that division already has an inherently inexact
> result. Do you intend to rip that out too while at it? (Just
> kidding)
No, but that too is now delivering less precision than it used to:
regression=# select 10.1/7.0; ?column?
--------------1.4428571429
(1 row)
versus 1.44285714285714 in prior releases.
> Proposal #2.667 would be to have a GUC variable for the default
> precision.
Perhaps, but I'd be satisfied if the default precision were at least
16 digits. Again, the point is not to have any apparent regression
from 7.2.
regards, tom lane
Seems we need to resolve this before beta2. --------------------------------------------------------------------------- Tom Lane wrote: > Jan Wieck <JanWieck@Yahoo.com> writes: > > One problem is, that division already has an inherently inexact > > result. Do you intend to rip that out too while at it? (Just > > kidding) > > No, but that too is now delivering less precision than it used to: > > regression=# select 10.1/7.0; > ?column? > -------------- > 1.4428571429 > (1 row) > > versus 1.44285714285714 in prior releases. > > > Proposal #2.667 would be to have a GUC variable for the default > > precision. > > Perhaps, but I'd be satisfied if the default precision were at least > 16 digits. Again, the point is not to have any apparent regression > from 7.2. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian wrote: > > Seems we need to resolve this before beta2. I'd go with making the NUMERIC default precision 16 for v7.3, so we are backwards compatible on this release (except that it is now a predictable 16 digit precision instead of an hardware implementation dependent one). For v7.4 we can discuss that a while. Jan > > --------------------------------------------------------------------------- > > Tom Lane wrote: > > Jan Wieck <JanWieck@Yahoo.com> writes: > > > One problem is, that division already has an inherently inexact > > > result. Do you intend to rip that out too while at it? (Just > > > kidding) > > > > No, but that too is now delivering less precision than it used to: > > > > regression=# select 10.1/7.0; > > ?column? > > -------------- > > 1.4428571429 > > (1 row) > > > > versus 1.44285714285714 in prior releases. > > > > > Proposal #2.667 would be to have a GUC variable for the default > > > precision. > > > > Perhaps, but I'd be satisfied if the default precision were at least > > 16 digits. Again, the point is not to have any apparent regression > > from 7.2. > > > > regards, tom lane > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup. | Newtown Square, Pennsylvania 19073 > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Seems we need to resolve this before beta2.
Not really. It's just a bug; we have others.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Seems we need to resolve this before beta2. > > Not really. It's just a bug; we have others. Oh, it doesn't effect initdb? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Is this an open item? --------------------------------------------------------------------------- Tom Lane wrote: > Jan Wieck <JanWieck@Yahoo.com> writes: > > One problem is, that division already has an inherently inexact > > result. Do you intend to rip that out too while at it? (Just > > kidding) > > No, but that too is now delivering less precision than it used to: > > regression=# select 10.1/7.0; > ?column? > -------------- > 1.4428571429 > (1 row) > > versus 1.44285714285714 in prior releases. > > > Proposal #2.667 would be to have a GUC variable for the default > > precision. > > Perhaps, but I'd be satisfied if the default precision were at least > 16 digits. Again, the point is not to have any apparent regression > from 7.2. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Is this an open item?
Yes. (Fooling with it right now, in fact, in a desultory way ...)
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Is this an open item? > > Yes. (Fooling with it right now, in fact, in a desultory way ...) OK, added. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073