Обсуждение: 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...  :(