expected behavior of fetchmany()

Hi all,

First, a hearty congrats on the new website, and the documentation. I've
used psycopg2 on and off for perhaps 4 years, and I discovered so much stuff
I never knew yesterday when I first discovered the docs. Can't thank you
enough. Keep it up.

Second, the docs didn't answer this question :)
I have a query that returns millions of rows. I *was* wrapping the call in a
python generator that uses fetchmany(), but that's causing issues with big
result sets because fetchmany() doesn't create a new transaction for each of
its requests for the next chunk of data (more detail below) -- here's what
my generator looks like right now:

def get_tons_o_data(self, params, limit=1000):
    cur = self.conn.cursor()
    cur.arraysize=limit
    try:
       cur.callproc('sch.get_metric_asston_o_data', params) # params is a
list
       while True:
          rs = cur.fetchmany(limit)
          if not rs:
             break
          for row in rs:
             yield row
     except Exception as out:
          <exception handling code here>

This works really well for smaller result sets and provides a nice interface
for the code that uses it (for row in get_tons_o_data...). The problem comes
when I have millions of rows in the result set: the issue is that
fetchmany() holds a single transaction open until it has fetched the entire
result set instead of opening/closing a transaction for each chunk of data
it requests.

I imagine this is probably expected behavior, but with so many rows being
returned, the transaction has the potential to stay open so long that it
keeps some database maintenance operations from running (some operations in
pgsql won't run if there's an open transaction, and won't kill the
transaction either).

I guess I was looking more for something that would abstract out a sort of
'limit/offset' loop for me. I'm updating the db function to take a limit and
offset, and then I'll have to create a loop to a) get data b) process data
c) update limit/offset d) rinse and repeat

I just wanted to make sure I didn't miss some magical setting that would
cause fetchmany() to request each chunk of data in a separate transaction
before I started making alterations to my code.

Thanks all.
brian

-- 
Brian K. Jones
Python Magazine  http://www.pythonmagazine.com
My Blog          http://www.protocolostomy.com

_______________________________________________
Psycopg mailing list
Psycopg-IAPFreCvJWPBWskQ1e/[email protected]
http://lists.initd.org/mailman/listinfo/psycopg