Hello,
you may know psycopg offers server-side cursors, known in
postgres/psycopg as named cursors. If you don't, well, this is good
news :) Named cursors are useful with datasets too big to be handled
by the client, as regular cursors transfer all the data to the client
during the execute() method. Named cursors on the other hand only
transfer the required amount of records to the client (one record with
cur.fetchone(), n with cursor.fetchmany(n)).
There is a shortcoming though: iter(cursor) will fetch the records one
at a time, with a noticeable time overhead in case of large recordsets
(exactly the ones you may want to retrieve with a named cursors...)
Currently the most efficient way to iterate on a named cursor is
something like:
nrecs = 100 # or some other reasonable number
while 1:
recs = cur.fetchmany(nrecs)
if not recs:
break
for rec in recs:
# do something
This would use only the memory used by nrecs record on the client and
require just 1/nrecs of the roundtrips required by a naive operation.
But it make the named cursors harder to use and not a drop-in
replacement for regular cursors that can be idiomatically used with:
for rec in cur:
# do something
So, I'd like to modify the cursor so that in case of __iter__, a
certain number of record is fetched and iteration is performed on
them. The cursor already has the state to keep the dataset so probably
only the code would require change, not so much the data structures.
How do we make the users choose their nrecs? I think the cursor should
have an attribute with a sensible default: 100? 1000? 1024? What
attribute name?
It shouldn't be hard to implement. Does anybody want to try it? I've
open the ticket #33
<http://psycopg.lighthouseapp.com/projects/62710/tickets/33> for any
update.
Comments?
Cheers,
-- Daniele