id,summary,reporter,owner,description,type,status,component,version,severity,resolution,keywords,cc,stage,has_patch,needs_docs,needs_tests,needs_better_patch,easy,ui_ux 16614,Support server-side cursors for queryset iteration in database backends,Dan McGee,nobody,"This is related to concerns raised in #5423 as well as documentation issues noted in #13869. Attached is a very rough first cut of a possible patch that adds server-side iteration for all backends that need it, as well as turning it on by default in the results_iter() codepaths, which is only used in the iterator() methods of the various QuerySet classes defined in `django.db.models.query`. Observations: * SQLite appears to do iteration right out of the box when using fetchmany(), so no changes are needed. * Oracle (via cx_Oracle) also does it right, fetching by default in batches of 50 when fetchmany() is used- see http://cx-oracle.sourceforge.net/html/cursor.html#Cursor.arraysize * PostgreSQL gets functionality for usage of named cursors (http://initd.org/psycopg/docs/connection.html#connection.cursor), which leave the result set on the server side. The default fetch size of sets fetched this way is 2000, but we always use `GET_ITERATOR_CHUNK_SIZE`, so adjusting this is not necessary. A named cursor can only be used once. * MySQL gets functionality for usage of SSCursor objects (http://mysql-python.sourceforge.net/MySQLdb-1.2.2/public/MySQLdb.cursors.SSCursor-class.html). This uses `mysql_fetch_row()` under the covers (http://dev.mysql.com/doc/refman/5.5/en/mysql-fetch-row.html). There are two cons here- it appears the library does not support batch fetches because it uses fetch_(single)_row under the covers, so this could result in significantly more chatter. MySQL also has no real concept of a cursor, so while you are using this server-side ""cursor"", you are not allowed to perform any other simultaneous queries. Thoughts and feedback are welcome- I can imagine only enabling this by default for PostgreSQL only, as MySQL's implementation leaves something to be desired. I could also see never doing this by default and allowing it to be configured in DATABASE settings. This was mildy tested with a random dumpdata operation on a random project using PostgreSQL. The max memory used by the dumpdata after applying this patch and the one from FS#5423, piping to /dev/null, went from 50MB to 26MB.",New feature,new,"Database layer (models, ORM)",1.3,Normal,,memory cursors database,dpmcgee@… nikolai@… trbs@… benth Simon Charette macek@… riccardo@… axel.rau@… clokep@… josh.smeaton@… olivier.tabone@… mail@…,Accepted,0,0,0,0,0,0