Different exponent in error messages

"Andrus" <eetasoft@online.ee> writes:

Why Postgres 8.1.1 in XP returns different exponents ( 10^6, 10^9, 10^7) for
field with precision 9, scale 3 ?

Could we see a complete test case --- ie, what are the input values
causing these messages?

regards, tom lane

On Wed, Jan 11, 2006 at 04:09:23PM -0500, Tom Lane wrote:

"Andrus" <eetasoft@online.ee> writes:

Why Postgres 8.1.1 in XP returns different exponents ( 10^6, 10^9, 10^7) for
field with precision 9, scale 3 ?

Could we see a complete test case --- ie, what are the input values
causing these messages?

I think this is what Andrus is seeing:

test=> CREATE TABLE foo (n numeric(9,3));
CREATE TABLE
test=> INSERT INTO foo VALUES (1000000);
ERROR: numeric field overflow
DETAIL: The absolute value is greater than or equal to 10^6 for field with precision 9, scale 3.
test=> INSERT INTO foo VALUES (1000000000);
ERROR: numeric field overflow
DETAIL: The absolute value is greater than or equal to 10^9 for field with precision 9, scale 3.
test=> INSERT INTO foo VALUES (10000000);
ERROR: numeric field overflow
DETAIL: The absolute value is greater than or equal to 10^7 for field with precision 9, scale 3.

--
Michael Fuhr

Michael Fuhr <mike@fuhr.org> writes:

I think this is what Andrus is seeing:

test=> CREATE TABLE foo (n numeric(9,3));
CREATE TABLE
test=> INSERT INTO foo VALUES (1000000);
ERROR: numeric field overflow
DETAIL: The absolute value is greater than or equal to 10^6 for field with precision 9, scale 3.
test=> INSERT INTO foo VALUES (1000000000);
ERROR: numeric field overflow
DETAIL: The absolute value is greater than or equal to 10^9 for field with precision 9, scale 3.

Hm, I thought I tested that same case, but I must've messed up somehow.

Anyway, the code seems to be intentionally reporting the log10 of the
actual input value, not the limiting log10 for the field size. This
behavior goes at least as far back as PG 7.0, so I'm disinclined to
change it. We could talk about altering the message wording though,
if you have a suggestion for something you'd find less confusing.
Pre-7.4 versions say

ERROR: overflow on numeric ABS(value) >= 10^9 for field with precision 9 scale 3

so it looks like we just fixed the grammar during the 7.4 message
wording cleanup, without reflecting about whether the meaning was clear.

regards, tom lane

On Wed, 2006-01-11 at 16:52, Tom Lane wrote:

Michael Fuhr <mike@fuhr.org> writes:

I think this is what Andrus is seeing:

test=> CREATE TABLE foo (n numeric(9,3));
CREATE TABLE
test=> INSERT INTO foo VALUES (1000000);
ERROR: numeric field overflow
DETAIL: The absolute value is greater than or equal to 10^6 for field with precision 9, scale 3.
test=> INSERT INTO foo VALUES (1000000000);
ERROR: numeric field overflow
DETAIL: The absolute value is greater than or equal to 10^9 for field with precision 9, scale 3.

Hm, I thought I tested that same case, but I must've messed up somehow.

Anyway, the code seems to be intentionally reporting the log10 of the
actual input value, not the limiting log10 for the field size. This
behavior goes at least as far back as PG 7.0, so I'm disinclined to
change it. We could talk about altering the message wording though,
if you have a suggestion for something you'd find less confusing.
Pre-7.4 versions say

ERROR: overflow on numeric ABS(value) >= 10^9 for field with precision 9 scale 3

so it looks like we just fixed the grammar during the 7.4 message
wording cleanup, without reflecting about whether the meaning was clear.

Does the SQL spec say anything about the error message? I can't
remember, as it's not a part of the spec I'm real familiar with.

Tom Lane wrote:

Michael Fuhr <mike@fuhr.org> writes:

I think this is what Andrus is seeing:

test=> CREATE TABLE foo (n numeric(9,3));
CREATE TABLE
test=> INSERT INTO foo VALUES (1000000);
ERROR: numeric field overflow
DETAIL: The absolute value is greater than or equal to 10^6 for field with precision 9, scale 3.
test=> INSERT INTO foo VALUES (1000000000);
ERROR: numeric field overflow
DETAIL: The absolute value is greater than or equal to 10^9 for field with precision 9, scale 3.

Hm, I thought I tested that same case, but I must've messed up somehow.

Anyway, the code seems to be intentionally reporting the log10 of the
actual input value, not the limiting log10 for the field size. This
behavior goes at least as far back as PG 7.0, so I'm disinclined to
change it. We could talk about altering the message wording though,
if you have a suggestion for something you'd find less confusing.
Pre-7.4 versions say

ERROR: overflow on numeric ABS(value) >= 10^9 for field with precision 9 scale 3

so it looks like we just fixed the grammar during the 7.4 message
wording cleanup, without reflecting about whether the meaning was clear.

Yes, this message clearly needs help. Here is what I developed:

test=> CREATE TABLE foo (n numeric(9,3));
CREATE TABLE
test=> INSERT INTO foo VALUES (10000000);
ERROR: numeric field overflow
DETAIL: A field with precision 9, scale 3 must have an absolute value less than 10^6.

and the 10^6 is based on the max digits to the left of the decimal
point, not the input value.

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

Applied.

---------------------------------------------------------------------------

Bruce Momjian wrote:

Tom Lane wrote:

Michael Fuhr <mike@fuhr.org> writes:

I think this is what Andrus is seeing:

test=> CREATE TABLE foo (n numeric(9,3));
CREATE TABLE
test=> INSERT INTO foo VALUES (1000000);
ERROR: numeric field overflow
DETAIL: The absolute value is greater than or equal to 10^6 for field with precision 9, scale 3.
test=> INSERT INTO foo VALUES (1000000000);
ERROR: numeric field overflow
DETAIL: The absolute value is greater than or equal to 10^9 for field with precision 9, scale 3.

Hm, I thought I tested that same case, but I must've messed up somehow.

Anyway, the code seems to be intentionally reporting the log10 of the
actual input value, not the limiting log10 for the field size. This
behavior goes at least as far back as PG 7.0, so I'm disinclined to
change it. We could talk about altering the message wording though,
if you have a suggestion for something you'd find less confusing.
Pre-7.4 versions say

ERROR: overflow on numeric ABS(value) >= 10^9 for field with precision 9 scale 3

so it looks like we just fixed the grammar during the 7.4 message
wording cleanup, without reflecting about whether the meaning was clear.

Yes, this message clearly needs help. Here is what I developed:

test=> CREATE TABLE foo (n numeric(9,3));
CREATE TABLE
test=> INSERT INTO foo VALUES (10000000);
ERROR: numeric field overflow
DETAIL: A field with precision 9, scale 3 must have an absolute value less than 10^6.

and the 10^6 is based on the max digits to the left of the decimal
point, not the input value.

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

Index: src/backend/utils/adt/numeric.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/numeric.c,v
retrieving revision 1.88
diff -c -c -r1.88 numeric.c
*** src/backend/utils/adt/numeric.c	22 Nov 2005 18:17:23 -0000	1.88
--- src/backend/utils/adt/numeric.c	23 Jan 2006 20:00:54 -0000
***************
*** 3206,3213 ****
ereport(ERROR,
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
errmsg("numeric field overflow"),
! 							 errdetail("The absolute value is greater than or equal to 10^%d for field with precision %d, scale %d.",
! 									   ddigits - 1, precision, scale)));
break;
}
ddigits -= DEC_DIGITS;
--- 3206,3213 ----
ereport(ERROR,
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
errmsg("numeric field overflow"),
! 							 errdetail("A field with precision %d, scale %d must have an absolute value less than 10^%d.",
! 									   precision, scale, maxdigits)));
break;
}
ddigits -= DEC_DIGITS;

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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