Re: expected behavior of fetchmany()

Thanks for the reply, Ben - please remember to cc/reply all to the list!

As for server side cursors, it's not clear that it solves the problem of
holding a single transaction open. I imagine that it performs all of its
work in a single transaction. I admit to being more familiar with pgsql than
psycopg2, so enlightenment is hereby solicited.

brian

On Wed, Mar 3, 2010 at 11:18 AM, Ben DeMott <[email protected]> wrote:

> Or you could use a Server-Side cursor, is this out of the question?
>
> On Wed, Mar 3, 2010 at 11:16 AM, Brian Jones <[email protected]> wrote:
>
>> 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
>>
>>
>


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