Re: expected behavior of fetchmany()

If you have any result set that is by definition "Large" and you
1.) cannot know the absolute resources available or the destination server
architecture or configuration
2.) cannot know the absolute size or potential size of the database or its
memory impact ahead of time

There is no reason to not use a Server-Side cursor.

You should periodically commit the transaction if the overall time running
is an issue.
You can do this and still keep re-using the server-side cursor by
specifying: "WITH HOLD"
See http://www.postgresql.org/docs/current/static/sql-declare.html

Postgres (the database) is VASTLY more efficient at storing and paging the
result set internally in a Cursor than any driver, client, could ever hope
to be.
In fact Postgres has the most advanced map/reduce algorithm in place for
Joins and table paging of any database I know of, but if you don't let it,
it can't use this super intelligence to keep memory consumption low, and
efficiency high.

So its much less of a load on the database to let it do its thing.
Also if other things are "happening" against the table, the desired
operation is to only have the records encountered that were present at the
time of starting your SELECT in most cases.

With a server-side cursor you CAN get more than 1 record returned at a time,
and you can even skip over records.

If you need to perform inserts in-between your long-running server-side
cursor, you would simply need to open another cursor, with Pyscopg2 you
don't need to use a 2nd connection (like with python-mysql)

If you want to see some conditional insert offset / commit code I can show
you some.
I wrote some really gross looking code before I discovered that you could
use fetchmany() to get more than one result from a server side cursor, and
use scroll() to advance the server-side cursor.

If you are worried about blocking (you need to perform other actions while
the cursor is open) you can write an asynchronous query... but using a
server-side cursor mostly solves this.

If you are performing some crazy complex operation against Postgres that
returns a result set that you intend to later interact with, you would be
much better off first performing   ->
INSERT INTO (table / TEMP table) SELECT crazy,complex,query FROM hugetable
WHERE arbitrarysums, joins, conditions.


For some examples, feel free to check out this wiki page I recently
created:  (the last examples hows a server-side cursor)
http://wiki.postgresql.org/wiki/Using_psycopg2_with_PostgreSQL


ss_cursor = conn.cursor('named_cursor',
cursor_factory=psycopg2.extras.DictCursor)
for row in ss_cursor:
  # do stuff

On Wed, Mar 3, 2010 at 11:28 AM, Federico Di Gregorio <fog-NGVKUo/i/[email protected]> wrote:

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

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