Re: fetchmany weirdness?

Brian Jones wrote:
>
> I'm using the latest psycopg2 download against a pgsql 8.4 server, and
> I'm getting a really odd behavior. It's not clear whether it's related
> to psycopg2 directly, but I figured someone here could help me out
> (and if it's a bug, all the better that I report the issue here).
> ...
> Here's the deal:
> *** db.get_users_by_joindate is a generator function. It calls a
> server-side function, uses fetchmany() to get back results, and then
> yield to return the id's.
>
> *** If I insert a "continue" after the "print idcount" line, I'll see
> that about 3000 records are returned for my test case.
>
> *** If I *don't* insert a continue, then db.is_good_user is only
> called 100 times.
>
> The place where I'm stuck debugging is this:  db.get_users_by_joindate
> sets self.curs.arraysize to 100, so fetchmany() will grab 100 results
> at a time. This is the same number of results that get processed by
> db.is_good_user if I left it as-is above. I've changed the
> self.curs.arraysize to 10 as well, and then only 10 results are
> processed by db.is_good_user.
>
> Since the two database calls are methods living in the same object,
> using the same connection and cursor, it seems like there's some kind
> of weird interplay here that seems off to me. In the above code, I'm
> calling db.is_good_user once for each iteration of the for loop,
> passing one parameter each time. So why is it only executing 100 times
> instead of 3000?

I'm guessing from your description that db.is_good_user is doing a new
query, by calling execute on the same cursor.  If so, then it should be
clear what the problem is.

The cursor is stateful; it only contains information about the last
query that was executed.  On your first call to "fetchmany", you fetch a
block of results from the original query, and cache them.  Then,
db.is_good_user calls "execute" again.  The cursor now throws away all
of the information about your first query, and fetches a new set of
results.  Presumably, is_good_user then consumes that dataset and
returns.  Now, the cursor is position at end of results.  The rows you
cached get returned by your iterator, then you call fetchmany again, but
there's nothing left to fetch.


> Only thing I can think of is that there's some odd reuse thing going
> on where, since the cursor is still processing
> db.get_users_by_joindate, it'll only pass 100 results to
> db.is_good_user or something? Can someone help me figure out how to
> avoid this issue, and help me understand it better?

It's not an "odd reuse" thing, it reuse by the very design of a DBAPI
cursor.  It's a pointer into the recordset from the last query that was
executed.  If you execute a new query, you get a new recordset.

So, the lesson is if you need a new recordset, you create a new cursor.

-- 
Tim Roberts, [email protected]
Providenza & Boekelheide, Inc.