Thread: libpq ERROR: overflow on numeric ABS(value) ...
Hello, I need some help understanding an error message. I've got a program using libpq where I do a:DECLARE mycursor CURSOR FOR SELECT new_order(...)FETCH ALL IN mycursor and I get the resulting error from the FETCH more than half the time:ERROR: overflow on numeric ABS(value) >= 10^4 for fieldwith precision 4 scale 0 My new_order function is a C library that returns an integer that returns a number from 0 to 30. Any ideas or suggestions? Thanks! -- Mark Wong - - markw@osdl.org Open Source Development Lab Inc - A non-profit corporation 12725 SW Millikan Way - Suite 400 - Beaverton, OR 97005 (503)-626-2455 x 32 (office) (503)-626-2436 (fax) http://www.osdl.org/archive/markw/
markw@osdl.org writes:
> Hello, I need some help understanding an error message. I've got a
> program using libpq where I do a:
> DECLARE mycursor CURSOR FOR SELECT new_order(...)
> FETCH ALL IN mycursor
> and I get the resulting error from the FETCH more than half the time:
> ERROR: overflow on numeric ABS(value) >= 10^4 for field with precision 4 scale 0
> My new_order function is a C library that returns an integer that
> returns a number from 0 to 30.
I'm betting on a bug in your C code. But we can't guess what without
more details.
regards, tom lane
On 7 Aug, Tom Lane wrote: > markw@osdl.org writes: >> Hello, I need some help understanding an error message. I've got a >> program using libpq where I do a: >> DECLARE mycursor CURSOR FOR SELECT new_order(...) >> FETCH ALL IN mycursor > >> and I get the resulting error from the FETCH more than half the time: >> ERROR: overflow on numeric ABS(value) >= 10^4 for field with precision 4 scale 0 > >> My new_order function is a C library that returns an integer that >> returns a number from 0 to 30. > > I'm betting on a bug in your C code. But we can't guess what without > more details. > Does it make sense that is throws the error on the FETCH instead of the previous statement? I've attached the table definitions, create function statement, and the C stored function. Does that help? Thanks, Mark
markw@osdl.org writes:
>>> DECLARE mycursor CURSOR FOR SELECT new_order(...)
>>> FETCH ALL IN mycursor
>>> ERROR: overflow on numeric ABS(value) >= 10^4 for field with precision 4 scale 0
> Does it make sense that is throws the error on the FETCH instead of the
> previous statement?
Sure: the cursor won't actually be executed until you fetch from it.
Since there's no NUMERIC datatypes in the cursor operation, it's a
dead certainty that the cursor itself is not where the error is being
thrown. I see that your function issues a bunch of inserts and updates
internally; presumably the problem is that one of those is trying to
store bogus data into a table. I'd suggest turning on that DEBUG code
you've sprinkled in there and looking to see which statement gets the
error ...
regards, tom lane
On 7 Aug, Tom Lane wrote: > markw@osdl.org writes: >>>> DECLARE mycursor CURSOR FOR SELECT new_order(...) >>>> FETCH ALL IN mycursor >>>> ERROR: overflow on numeric ABS(value) >= 10^4 for field with precision 4 scale 0 > >> Does it make sense that is throws the error on the FETCH instead of the >> previous statement? > > Sure: the cursor won't actually be executed until you fetch from it. Oh right, I should have remembered that... Thanks, Mark
On 7 Aug, Tom Lane wrote: > markw@osdl.org writes: >>>> DECLARE mycursor CURSOR FOR SELECT new_order(...) >>>> FETCH ALL IN mycursor >>>> ERROR: overflow on numeric ABS(value) >= 10^4 for field with precision 4 scale 0 Ok, found the offending statement:UPDATE stockSET s_quantity = s_quantity - 4WHERE s_i_id = 28472 AND s_w_id = 2 s_quantity is defined as NUMERIC(4). So does this means s_quantity must be a positive number? Should I use a different data type? Thanks, Mark
On 7 Aug, To: tgl@sss.pgh.pa.us wrote: > On 7 Aug, Tom Lane wrote: >> markw@osdl.org writes: >>>>> DECLARE mycursor CURSOR FOR SELECT new_order(...) >>>>> FETCH ALL IN mycursor >>>>> ERROR: overflow on numeric ABS(value) >= 10^4 for field with precision 4 scale 0 > > Ok, found the offending statement: > UPDATE stock > SET s_quantity = s_quantity - 4 > WHERE s_i_id = 28472 > AND s_w_id = 2 > > s_quantity is defined as NUMERIC(4). So does this means s_quantity must > be a positive number? Should I use a different data type? Oh wait, I got it figured out. I'm getting smaller than -9999... :(