OverflowError in pyPgSQL when accessing tables with many rows

  • Thread starter Paolo Alexis Falcone
  • Start date
P

Paolo Alexis Falcone

Whenever I try to access a table with many rows using PgSQL's
fetchall(), this happens:
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

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

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?

My test system is on Debian Sid with the following python and
postgresql package versions:
ii postgresql 7.3.3-1 Object-relational SQL database,
descended fr
ii postgresql-cli 7.3.3-1 Front-end programs for PostgreSQL
ii postgresql-con 7.3.3-1 Additional facilities for PostgreSQL
ii python2.2 2.2.3-2.1 An interactive high-level
object-oriented la
ii python2.2-egen 2.0.4-1 Date and time handling routines for
Python 2
ii python2.2-egen 2.0.4-1 A collection of new builtins for
Python 2.2
ii python2.2-nume 23.0-5 Numerical (matrix-oriented)
Mathematics for
ii python2.2-nume 23.0-5 Extension modules for Numeric Python
ii python2.2-pgsq 2.3.0-2 A Python DB-API 2.0 interface to
PostgreSQL

--> paolo

Paolo Alexis Falcone
(e-mail address removed)
 
?

=?ISO-8859-1?Q?Gerhard_H=E4ring?=

Paolo said:
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 :)
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
(e-mail address removed) 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.
 
B

Billy G. Allie

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


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

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

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?
Paolo,

The problem is not the number of rows, but the fact the conversion of a
PostgreSQL numeric to a PgNumeric is failing. This problem has been
fixed in the code in the CVS repository for the pyPgSQL project
<http://sourceforge.net/cvs/?group_id=16528> on SourceForge. We will
also be releaseing a new version of pyPgSQL within the next couple of weeks.

--
___________________________________________________________________________
____ | Billy G. Allie | Domain....: (e-mail address removed)
| /| | 7436 Hartwell | MSN.......: (e-mail address removed)
|-/-|----- | Dearborn, MI 48126|
|/ |LLIE | (313) 582-1540 |
 
P

Paolo Alexis Falcone

Gerhard Häring said:
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:

I've tried it and it already works like a charm :)

Thanks!

-->paolo

Paolo Alexis Falcone
(e-mail address removed)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Staff online

Members online

Forum statistics

Threads
473,731
Messages
2,569,432
Members
44,832
Latest member
GlennSmall

Latest Threads

Top