OverflowError in pyPgSQL when accessing tables with many rows

Discussion in 'Python' started by Paolo Alexis Falcone, Jun 28, 2003.

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

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

    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
     
    Paolo Alexis Falcone, Jun 28, 2003
    #1
    1. Advertising

  2. 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
    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.
     
    =?ISO-8859-1?Q?Gerhard_H=E4ring?=, Jun 28, 2003
    #2
    1. Advertising

  3. Paolo Alexis Falcone wrote:

    >Whenever I try to access a table with many rows using PgSQL's
    >fetchall(), this happens:
    >
    >
    >>>>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
    >
    >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....:
    | /| | 7436 Hartwell | MSN.......:
    |-/-|----- | Dearborn, MI 48126|
    |/ |LLIE | (313) 582-1540 |
     
    Billy G. Allie, Jun 28, 2003
    #3
  4. Gerhard Häring <> wrote in message news:<>...

    > 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
     
    Paolo Alexis Falcone, Jun 29, 2003
    #4
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Marco Ippolito
    Replies:
    0
    Views:
    2,587
    Marco Ippolito
    Oct 11, 2004
  2. Timo Virkkala

    Problems with pyPgSQL

    Timo Virkkala, Sep 13, 2003, in forum: Python
    Replies:
    0
    Views:
    351
    Timo Virkkala
    Sep 13, 2003
  3. Rene Pijlman
    Replies:
    2
    Views:
    684
    Rene Pijlman
    Nov 3, 2003
  4. Manuel Huesser

    PyPGSQL - OID

    Manuel Huesser, May 27, 2004, in forum: Python
    Replies:
    1
    Views:
    397
    Michael Fuhr
    May 27, 2004
  5. Ray
    Replies:
    0
    Views:
    173
Loading...

Share This Page