Suggested documentation: PG server-side cursors used in .iterator() only work inside a transaction

[Evan Heidtmann]

Hey all,

The docs for qs.iterator() say that, in Django 1.11 on Postgres, a server-side cursor is used.

Oracle and PostgreSQL use server-side cursors to stream results from the database without loading the entire result set into memory.

https://docs.djangoproject.com/en/1.11/ref/models/querysets/#iterator

I discovered that this is only true if the query is run inside a transaction. Outside a transaction, it appears that Django falls back to a regular SELECT query, which could be extremely expensive and is certainly unexpected.

I don't know what happens if you call .iterator() inside a transaction block and then exit that block. Undefined behavior?

Therefore I suggest the following docs edit (changes in italic):

Oracle and PostgreSQL use server-side cursors to stream results from the database without loading the entire result set into memory. On PostgreSQL, server-side cursors are used only if .iterator() is called within a transaction.

And thanks for this new feature -- it's transformative for my workload.

-Evan

[Josh Smeaton]

Yes we should be documenting edge cases and unexpected results. We have a page that discusses some issues with server side cursors: https://docs.djangoproject.com/en/dev/ref/databases/#transaction-pooling-server-side-cursors

Is there anyway we could make SSC work without a transaction? We'd prefer to fix than document if possible.

[Dan Watson]

Declaring a cursor WITH HOLD means it can be used outside transactions, but it seems like the server-side cursors are already set up WITH HOLD when autocommit is on:

https://github.com/django/django/blob/master/django/db/backends/postgresql/base.py#L212

So I'm guessing maybe you've disabled transaction management entirely (AUTOCOMMIT=False)? In that case, you could try enabling autocommit manually when you need to use .iterator(), but maybe the backend could check "connection.autocommit or not connection.in_atomic_block" when setting WITH HOLD, so even if autocommit is off, WITH HOLD would be used if not using an atomic block?

As for the behavior when trying to iterate a cursor after the transaction has been exited, I'd expect an error. The cursor should be destroyed at the end of the transaction (unless WITH HOLD was used, which it shouldn't be inside a transaction), so trying to FETCH from it should raise an error.

Dan