OverflowError in pyPgSQL when accessing tables with many rows

[Gerhard Häring]

Paolo Alexis Falcone wrote:
> Whenever I try to access a table with many rows using PgSQL's
> fetchall(), this happens:

First, I'd recommend you ask on the pyPgSQL mailing list in such cases. 
Otherwise it might happen that the pyPgSQL developers miss a post on the 
newsgroup :-)

>>>>from pyPgSQL import PgSQL
>>>>db = PgSQL.connect("192.168.0.8:5432:whitegold","dondon","dondon")
>>>>PgSQL.NoPostgresCursor = 1
>>>>cur = db.cursor()
>>>>cur.execute("SELECT * FROM customer")
>>>>data = cur.fetchall()
> 
> Traceback (most recent call last):
>   File "<stdin>", line 1, in ?
>   File "/usr/lib/python2.2/site-packages/pyPgSQL/PgSQL.py", line 3106,
> in fetchall
>     return self.__fetchManyRows(self._rows_, _list)
>   File "/usr/lib/python2.2/site-packages/pyPgSQL/PgSQL.py", line 2684,
> in __fetchManyRows
>     _j = self.__fetchOneRow()
>   File "/usr/lib/python2.2/site-packages/pyPgSQL/PgSQL.py", line 2660,
> in __fetchOneRow
>     _r.getvalue(self._idx_, _i)))
>   File "/usr/lib/python2.2/site-packages/pyPgSQL/PgSQL.py", line 717,
> in typecast
>     return PgNumeric(value, _p, _s)
>   File "/usr/lib/python2.2/site-packages/pyPgSQL/PgSQL.py", line 1335,
> in __init__
>     raise OverflowError, "value too large for PgNumeric"
> OverflowError: value too large for PgNumeric

So here's a specific string coming from the database for which the 
PgNumeric class thinks it's too large. This smells like a bug to me.

> The aforementioned table, customer, only has 1023 entries with the
> following structure:
> 
> CREATE TABLE customer (ccustcode varchar(80), cgroupcode varchar(10),
> clastname varchar(80), cfirstname     varchar(80), cmi varchar(10),
> ccompany varchar(80), caddress1 varchar(80), caddress2 varchar(80),
> ccity varchar(80), cprovince varchar(80), czipcode varchar(10), iterms
> integer, ycredit_limit numeric, npenalty_rate numeric,
> default_routecode varchar(10), lisdirector boolean);

Aha. Could you perhaps send me the contents of the table? I'm only 
interested in the NUMERIC columns. Just omit the rest, as it's likely a 
privacy issue otherwise.

> PgSQL's fetchone() fortunately works though, as well as using
> fetchall() on tables with few rows. Is there any alternative way of
> using PyPgSQL that would not overflow in this situation?

It's a problem with the way PgNumeric thinks how large NUMERIC values 
can become. pyPgSQL gets information from the backend in the 
.description field of the cursor and infers the valid range of the 
NUMERICs in the columns from this information. There must be a problem 
with this.

Can you perhaps first try the CVS version of pyPgSQL?

It seems to me that the bug you've encountered is bug #697221, which is 
already fixed in the CVS version:

http://sourceforge.net/tracker/index.php?func=detail&aid=697221&group_id=16528&atid=116528

If the problem persists with the CVS version, please send me more info, 
like a dump of the NUMERIC columns.

If you want to contact me personally, please also CC 
gerhard.haering at gmx.de for the following days, as the DNS entries for my 
mail server haven't catched up to my switching the server yet. [1]

-- Gerhard

[1] Damn (IDE) hard disks. This is the second HD crash on two different 
servers within three months :-( But at least *this* time I have recent 
backups ... Anybody got an idea about the failure rate of IDE hard 
drives in medium-used servers? My ISP puts Excelstor HDs in the boxes 
and claims they were the stablest ones in their tests.