[Pypgsql-devel] [ pypgsql-Bugs-697221 ] OverflowError: value too large for PgNumeric

[SourceForge.net <no...@so...>]

Bugs item #697221, was opened at 2003-03-04 07:49
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=116528&aid=697221&group_id=16528

Category: PgNumeric
Group: None
>Status: Pending
>Resolution: Fixed
Priority: 7
Submitted By: Michael Howitz (icemac)
Assigned to: Nobody/Anonymous (nobody)
Summary: OverflowError: value too large for PgNumeric

Initial Comment:
I create a table:

CREATE TABLE test_bug (
  t NUMERIC(10,2)
);

Then I insert two values:
INSERT INTO test_bug VALUES (24.11);
INSERT INTO test_bug VALUES (305.03);

Now I query the values through ZOPE using:
SELECT t FROM test_bug
UNION SELECT 0
ORDER BY t ASC

The result is: 
OverflowError: value too large for PgNumeric

Traceback (innermost last):
    * Module ZPublisher.Publish, line 98, in publish
    * Module ZPublisher.mapply, line 88, in mapply
    * Module ZPublisher.Publish, line 39, in call_object
    * Module Shared.DC.ZRDB.DA, line 331, in manage_test
    * Module Shared.DC.ZRDB.DA, line 309, in manage_test
    * Module Shared.DC.ZRDB.DA, line 443, in __call__
    * Module Products.ZpyPgSQLDA.db, line 148, in query
    * Module pyPgSQL.PgSQL, line 3074, in fetchmany
    * Module pyPgSQL.PgSQL, line 2688, in __fetchManyRows
    * Module pyPgSQL.PgSQL, line 2664, in __fetchOneRow
    * Module pyPgSQL.PgSQL, line 719, in typecast
    * Module pyPgSQL.PgSQL, line 1336, in __init__


The Exception does not happen, when I leave out the
UNION-statment (but I need it) or if I order by desc
(but this is no solution).

It seems that the precision for the column is computed
as the precision of the first value.

my Versions:
Zope Version: (unreleased version, python 2.2.2, linux2)
Python Version: 2.2.2 (#3, Dec 15 2002, 15:08:50) [GCC
2.95.4 20011002 (Debian prerelease)] 

I use ZpyPgSQLDA.

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

>Comment By: Billy G. Allie (ballie01)
Date: 2003-03-07 01:48

Message:
Logged In: YES 
user_id=8500

The problem has to do with the way that PostgreSQL returns
scale and precision information.  The 0 (from the union
select 0) does not have any scale or precision associated
with it, causing PgSQL to give a precision of 1 and a scale
of 0.  Since this was the first item in the result set, the
precision and scale in the crsor.description was set to 1
and 0 (which is wrong, it should be 10 and 2).

There is no way to fix the problem due to the way that
postgreSQL is returning the precision and scale, but a
work-around can and is implemented in version 1.32 of
PgSQL.py.  If the conversion to a PgNumeric fails in the
typecasting routine, the value is re-cast into a PgNumeric
without tusing the precision and scale from the
cursor.descrioption apptribute.

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

Comment By: Michael Howitz (icemac)
Date: 2003-03-04 07:53

Message:
Logged In: YES 
user_id=725802

I forgot to mention my version of pyPgSQL:
__version__ = "2.3"

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

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=116528&aid=697221&group_id=16528