Re: expected behavior of fetchmany()

On 03/03/2010 17:16, Brian Jones wrote:
> 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.

Thanks.

> 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)
[snip]
> 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.

You can't have the results returned in chunked in different
transactions, your query need to be run in the same transaction.

First of all, try to use a named cursor: this won't solve the problem on
the server but will make the client run faster because fetchmany()
always fetch and cache the whole dataset in memory, UNLESS a named
cursor is used. So, in fact, your code is just a long way to write:

curs.fetchall()

Also, if you really don't care about inconsistent results you can
re-issue the query multiple times using increasing values of OFFSET and

data = curs.fetchall()
conn.commit()

before iterating over data to make sure you cache the results in memory
and end the chunk's transaction as soon as possible.

You can also try to put the connection in autocommit but I don't know
what this will changes vs your maintenance problems (can you details?)

federico

-- 
Federico Di Gregorio                                       fog-NGVKUo/i/[email protected]
                           There's no certainty - only opportunity. -- V

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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkuOjjMACgkQvcCgrgZGjetMzQCeJ1dKh5cPHhBd/PFkR21ogU28
SL0AoNBISU8+5WYVmdbBIwNsDMIQaG1/
=KeAm
-----END PGP SIGNATURE-----