pg8000 and cursor.fetchall() failing to return records if the number of records is moderate

Question

I'm using the adaptor pg8000 to read in records in my db with the following code:

cursor = conn.cursor()
results = cursor.execute("SELECT * from data_" + country + " WHERE date >= %s AND date <= %s AND time >= %s AND time <= %s", (datetime.date(fromdate[0], fromdate[1], fromdate[2]), datetime.date(todate[0], todate[1], todate[2]),datetime.time(fromtime[0],fromtime[1]), datetime.time(totime[0],totime[1])))
results = cursor.fetchall()

The problem emerges when I select a date range that brings in say 100 records. Its not a huge number of records but it is enough to cause the following issue and I cannot see where the issue might come from - as it seems to be dependent on the number of records brought back. For example: results = cursor.fetchall() appears to work just fine and return one result.

The error message I get is:

File "/mnt/opt/Centos5.8/python-2.7.8/lib/python2.7/site-packages/pg8000/core.py", line 1650, in handle_messages
    raise error
pg8000.errors.ProgrammingError: ('ERROR', '34000', 'portal "pg8000_portal_0" does not exist')

Obviously I cannot find a way of fixing this despite exploring.

When using fetchmany(), here are the results:

results = cursor.fetchmany(100) WORKS - limited to 100

results = cursor.fetchmany(101) FAILS - same error as above

Answer 1

In autocommit mode you can't retrieve more rows than the pg8000 cache holds (which is 100 by default).

I've made a commit that gives a better error message when this happens, which will be in the next release of pg8000.

The reason is that if the number of rows returned by a query is greater than the number of rows in the pg8000 cache, the database portal is kept open, and then when the cache is empty, more rows are fetched from the portal. A portal can only exist within a transaction, so in autocommit mode a portal is immediately closed after the first batch of rows is retrieved. If you try and retrieve a second batch from the portal, you get the 'portal does not exist' error that was reported in the question.

Answer 2

It appears this can be solved by setting:

conn.autocommit = False

Now the code looks like:

conn.autocommit = False
cursor = conn.cursor()
results = cursor.execute("SELECT * from data_" + country + " WHERE date >= %s AND date <= %s AND time >= %s AND time <= %s", (datetime.date(fromdate[0], fromdate[1], fromdate[2]), datetime.date(todate[0], todate[1], todate[2]),datetime.time(fromtime[0],fromtime[1]), datetime.time(totime[0],totime[1])))
results = cursor.fetchall()

I'm not sure why this should be the case - but there seems a limit on the number of records of 100 with autocommit set to True