[Django] #16614: Support server-side cursors for queryset iteration in database backends
Django
----------------------------+----------------------------------------------
Reporter: toofishes | Owner: nobody
Type: New feature | Status: new
Milestone: | Component: Database layer (models, ORM)
Version: 1.3 | Severity: Normal
Keywords: memory cursors | Triage Stage: Unreviewed
database | Easy pickings: 0
Has patch: 1 |
UI/UX: 0 |
----------------------------+----------------------------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/16614>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: toofishes | Owner: nobody
Type: New | Status: new
feature | Component: Database layer
Milestone: | (models, ORM)
Version: 1.3 | Severity: Normal
Resolution: | Keywords: memory cursors
Triage Stage: | database
Unreviewed | Has patch: 1
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Changes (by toofishes):
* cc: dpmcgee@… (added)
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:1>
Django
Reporter: toofishes | Owner: nobody
feature | Component: Database layer
Version: 1.3 | Severity: Normal
Triage Stage: | database
Unreviewed | Has patch: 1
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
* cc: nikolai@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:2>
Django
Reporter: toofishes | Owner: nobody
feature | Component: Database layer
Version: 1.3 | Severity: Normal
Resolution: | Keywords: memory cursors
Triage Stage: Accepted | database
Needs documentation: 1 | Has patch: 1
Patch needs improvement: 0 | Needs tests: 1
UI/UX: 0 | Easy pickings: 0
-------------------------------------+-------------------------------------
Changes (by aaugustin):
* needs_docs: 0 => 1
* needs_tests: 0 => 1
* stage: Unreviewed => Accepted
Comment:
Based on slides 55-63 of http://thebuild.com/presentations/unbreaking-
django.pdf I think it's a good idea.
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:3>
Django
Type: New feature | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: memory cursors | Needs documentation: 1
database | Patch needs improvement: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by akaariai):
I did a very similar implementation based on discussions in
https://groups.google.com/group/django-
developers/browse_thread/thread/f19040e2e3229d7a
The main difference is that my version adds a queryset method .chunked(),
which will use named cursor for PostgreSQL and will not fetch all the
results in one go for SQLite3. SQLite3 has a problem if not fetching all
the results in one go as updates done while iterating the results will be
seen in the results.
The idea for the .chunked() method is that it will be documented as having
backend-specific limitations which the .iterator() approach does not have.
The abovementioned SQLite3 limitation is one, PostgreSQL has at least two:
- As long as the cursor is open (you have a reference to the iterator in
the code), server side resources will be tied. This might be important for
cases where you open a lot of cursors and then iterate them in a template.
- The named cursor is not iterable at all outside of a transaction (or
you need to use WITH HOLD cursors, which will tie the resources even
longer). This means named cursor will not be usable in autocommit mode.
I did not yet include anything for MySQL. There was some discussions about
MySQL and it seems it could have some deadlocking problems.
The above limitations are not backwards compatible with current behavior
of .iterator(). So, there might be some reason not to expose this as a
default / settings behavior, but as a different queryset method you can
use when you really need it. The conditions when you need this are
exceptional, in normal HTML generation you will almost never want to use
named cursors.
The patch is at:
http://www.google.com/url?sa=D&q=https://github.com/akaariai/django/commit/8990e20df50ce110fe6ddbbdfed7a98987bb5835&usg=AFQjCNGtHz9sSHT0tOIWKXAZAypfvPIHyw
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:4>
Django
Type: New feature | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: memory cursors | Needs documentation: 1
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by anonymous):
Has there been any movement on this issue? Do the core developers have any
plans to merge this patch in the near future?
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:5>
Django
Type: New feature | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: memory cursors | Needs documentation: 1
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by lukeplant):
akaariai is now a core developer, and could do this if he is still
interested. The idea as described in comment 4 sounds solid to me. It
would also be fine to have this implemented for some backends and not
others IMO.
The link for the patch no longer works. Hopefully Anssi has a record of it
somewhere.
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:6>
Django
Type: New feature | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: memory cursors | Needs documentation: 1
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by akaariai):
I renamed the repo to django-old when Django was moved to github. Here is
a working link: https://github.com/akaariai/django-
old/commit/8990e20df50ce110fe6ddbbdfed7a98987bb5835
I can take care of final polish & commit, but I am not too interested in
doing a lot of work on this right now. To get this committed making sure
the patch works on current git HEAD, and writing some docs & tests is the
way to get this into core.
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:7>
Django
Reporter: toofishes | Owner: nobody
Type: New feature | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: memory cursors | Needs documentation: 1
Has patch: 1 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* cc: trbs@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:8>
Django
Reporter: toofishes | Owner: nobody
Type: New feature | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: memory cursors | Needs documentation: 1
Has patch: 1 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* cc: benth (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:9>
Django
Reporter: toofishes | Owner: nobody
Type: New feature | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: memory cursors | Needs documentation: 1
Has patch: 1 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by aaugustin):
See also http://thebuild.com/blog/2010/12/13/very-large-result-sets-in-
django-using-postgresql/
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:10>
Django
Reporter: toofishes | Owner: nobody
Type: New feature | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: memory cursors | Needs documentation: 1
Has patch: 1 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* cc: charettes (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:11>
Django
Reporter: toofishes | Owner: nobody
Type: New feature | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: memory cursors | Needs documentation: 1
Has patch: 1 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* cc: macek@… (added)
Comment:
Is it really in stage Accepted? :)
I think the missing server-side cursor support keeps the Django ORM
down...
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:12>
Django
Reporter: toofishes | Owner: nobody
Type: New feature | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: memory cursors | Needs documentation: 1
Has patch: 1 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* cc: riccardo@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:13>
Django
Reporter: toofishes | Owner: auvipy
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: memory cursors | Triage Stage: Accepted
database |
Has patch: 1 | Needs documentation: 1
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => assigned
* owner: nobody => auvipy
* version: 1.3 => master
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:14>
Django
Reporter: toofishes | Owner:
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: memory cursors | Triage Stage: Accepted
database |
Has patch: 1 | Needs documentation: 1
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: auvipy =>
* status: assigned => new
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:15>
Django
Reporter: toofishes | Owner:
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: memory cursors | Triage Stage: Accepted
database |
Has patch: 1 | Needs documentation: 1
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: axel.rau@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:16>
Django
Reporter: toofishes | Owner:
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: memory cursors | Triage Stage: Accepted
database |
Has patch: 1 | Needs documentation: 1
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by keturn):
I've updated akaariai's patch from 2012 to
[https://github.com/keturn/django/tree/server_side_cursor_16614
server_side_cursor_16614].
(not yet ready to submit, still wants docs and tests)
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:17>
Django
Reporter: toofishes | Owner:
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: memory cursors | Triage Stage: Accepted
database |
Has patch: 1 | Needs documentation: 1
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by keturn):
also, it renames the DatabaseFeatures `can_use_chunked_reads` to
`has_safe_chunked_reads`, is that a private interface that can be renamed
freely, or is it a public interface we need to cautious around?
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:18>
Django
Reporter: toofishes | Owner:
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: memory cursors | Triage Stage: Accepted
database |
Has patch: 1 | Needs documentation: 1
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by carljm):
Technically that's not public API and we can change it if we need to, but
it does cause problems for third-party database backends (and I think
we've said that we'll mention such changes in the release notes?). So we
should only change it if it's really valuable to do so. I'm not
immediately seeing a gain in clarity from that change that would justify
it, but maybe I'm missing some subtle reason why the new name is more
accurate? Unless that's so, I'd lean towards leaving it unchanged.
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:19>
Django
Reporter: toofishes | Owner:
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: memory cursors | Triage Stage: Accepted
database |
Has patch: 1 | Needs documentation: 1
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by keturn):
Okay, I've added
[https://github.com/keturn/django/commit/dd1c959eb817a7502c2e5f6405938301d05c277f
#diff-db5fb8fdfbba5c6ac7595dd636d9b558R93 a test], which is better than no
tests, but still leaves some to be desired. That is, this test makes sure
that a call to `chunked()` does not fail horribly, but it doesn't test the
salient difference between `chunked` and `iterator`, that `chunked` uses a
server-side cursor and doesn't load large result sets in to memory all at
once.
Other things that it seems like might be valuable to test:
* what if you have two `chunked()` queries at once, and alternate taking
objects from each of them, do they successfully not get in each other's
way?
* that may be the same as: if you took the `_named_cursor_idx` out of the
chunked_cursor name, what would break?
Considering the API, is it necessary to add a new `QuerySet.chunked()`, or
can we change `QuerySet.iterator()` to have this behaviour by default?
(because it's what I always ''assumed'' that `iterator` did until I ran
out of RAM.)
It does change the run-time characteristics to some degree, but I'm having
a hard time coming up with a situation where it would really break an
existing use case.
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:20>
Django
Reporter: Dan McGee | Owner: (none)
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: memory cursors | Triage Stage: Accepted
database |
Has patch: 1 | Needs documentation: 1
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: clokep@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:21>
Django
Reporter: Dan McGee | Owner: (none)
(models, ORM) |
Severity: Normal | Resolution:
Keywords: memory cursors | Triage Stage: Accepted
database |
Has patch: 1 | Needs documentation: 1
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: josh.smeaton@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:22>
Django
Reporter: Dan McGee | Owner: (none)
(models, ORM) |
Severity: Normal | Resolution:
Keywords: memory cursors | Triage Stage: Accepted
database |
Has patch: 1 | Needs documentation: 1
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: olivier.tabone@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:23>
Django
Reporter: Dan McGee | Owner: (none)
(models, ORM) |
Severity: Normal | Resolution:
Keywords: memory cursors | Triage Stage: Accepted
database |
Has patch: 1 | Needs documentation: 1
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Josh Smeaton):
For what it's worth I'm keen on the postgres implementation using SSC from
the iterator method. That is, no need to implement another queryset method
to support this. Testing will need to be done with and without
transactions though.
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:24>
Django
Reporter: Dan McGee | Owner: (none)
(models, ORM) |
Severity: Normal | Resolution:
Keywords: memory cursors | Triage Stage: Accepted
database |
Has patch: 1 | Needs documentation: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: mail@… (added)
* needs_tests: 1 => 0
Comment:
I have written a draft for this feature based on previous patch by Anssi
and Kevin.
[https://github.com/django/django/pull/7587 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:25>
Django
-------------------------------------+-------------------------------------
Reporter: Dan McGee | Owner: (none)
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: memory cursors | Triage Stage: Accepted
database |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by François Freitag):
* needs_docs: 1 => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:26>
Django
-------------------------------------+-------------------------------------
Reporter: Dan McGee | Owner: (none)
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: memory cursors | Triage Stage: Accepted
database |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham <timograham@…>):
In [changeset:"ee1c1c696b422f482a1611344f8c0a9db16f8808" ee1c1c69]:
{{{
#!CommitTicketReference repository=""
revision="ee1c1c696b422f482a1611344f8c0a9db16f8808"
Made prefetch_related SQL inspection tests less brittle.
After refs #16614, integers might appear outside the WHERE clause.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:27>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
database | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:28>
Django
-------------------------------------+-------------------------------------
Reporter: Dan McGee | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version: 1.3
(models, ORM) |
Severity: Normal | Resolution:
Keywords: memory cursors | Triage Stage: Ready for
database | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham <timograham@…>):
In [changeset:"f3b7c059367a4e82bbfc7e4f0d42b10975e79f0c" f3b7c05]:
{{{
#!CommitTicketReference repository=""
revision="f3b7c059367a4e82bbfc7e4f0d42b10975e79f0c"
Refs #16614 -- Made QuerySet.iterator() use server-side cursors on
PostgreSQL.
Thanks to Josh Smeaton for the idea of implementing server-side cursors
in PostgreSQL from the iterator method, and Anssi Kääriäinen and Kevin
Turner for their previous work. Also Simon Charette and Tim Graham for
review.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:29>
Django
-------------------------------------+-------------------------------------
Reporter: Dan McGee | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version: 1.3
(models, ORM) |
Severity: Normal | Resolution:
database |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 1 => 0
* stage: Ready for checkin => Accepted
Comment:
The ticket remains open for consideration of using server-side cursors on
MySQL.
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:30>
Django
-------------------------------------+-------------------------------------
Reporter: Dan McGee | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version: 1.3
(models, ORM) |
Severity: Normal | Resolution:
Keywords: memory cursors | Triage Stage: Accepted
database |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham <timograham@…>):
In [changeset:"6b6be692fcd102436c7abef1d7b3fa1d37ad4bdf" 6b6be69]:
{{{
#!CommitTicketReference repository=""
revision="6b6be692fcd102436c7abef1d7b3fa1d37ad4bdf"
Refs #16614 -- Prevented database errors from being masked by cursor
close.
When an error occurred during the cursor.execute statement, the cursor
is closed. This operation did not fail with client-side cursors. Now,
with server-side cursors, the close operation might fail (example
below). The original error should be raised, not the one raised by
cursor.close(), this is only clean-up code.
For example, one can attempt to create a named cursor for an invalid
query. psycopg will raise an error about the invalid query and the
server-side cursor will not be created on PostgreSQL. When the code
attempts to cursor.close(), it asks psycopg to close a cursor that was
not created. pyscopg raises a new error: psycopg2.OperationalError:
cursor "_django_curs_140365867840512_20" does not exist.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:31>
Django
-------------------------------------+-------------------------------------
Reporter: Dan McGee | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version: 1.3
(models, ORM) |
Severity: Normal | Resolution:
Keywords: memory cursors | Triage Stage: Accepted
database |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham <timograham@…>):
In [changeset:"05bdf4f44dc80ba2481699860fa0a73de80694ae" 05bdf4f4]:
{{{
#!CommitTicketReference repository=""
revision="05bdf4f44dc80ba2481699860fa0a73de80694ae"
Refs #16614 -- Called _prepare_cursor() on every created cursor.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:32>
Django
-------------------------------------+-------------------------------------
Reporter: Dan McGee | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version: 1.3
(models, ORM) |
Severity: Normal | Resolution:
Keywords: memory cursors | Triage Stage: Accepted
database |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Sergey Fursov):
Hi!
I want to raise one case, which had our team after upgrading to 1.11
version:
- our app connects to DB through pgbouncer in transaction pooling mode
- after applying changes introduced in this ticket, we started receive
errors about non-existent named cursors
- this obviously related to the fact, that pgBouncer can't work with
cursors run "WITH HOLD" option in transaction pooling mode
(more details here: https://groups.google.com/forum/#!topic/django-
users/E39ycUilQ3c)
Our team really want to upgrade to 1.11, but in this case we have to
remove pgBouncer as connection pooling tool and connect to postgres DB
directly.
Looks like another possible option is wrapping all iterator() calls in
their own transaction, but django internally use cursors in several places
(e.g. in ModelChoiceIterator).
I think this possible problem should be documented in some way, and it
would be great to describe some options for projects to migrate to new
version.
(I'm sorry for cross-posting this problem here, but i guess our stack
could be common for many other projects)
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:33>
Django
-------------------------------------+-------------------------------------
Reporter: Dan McGee | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version: 1.3
(models, ORM) |
Severity: Normal | Resolution:
Keywords: memory cursors | Triage Stage: Accepted
database |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Josh Smeaton):
Hi Sergey,
This is bad. Most people that use pgbouncer would be using it in
transaction mode, which makes running any query with .iterator() a
breaking change with no option to skip server side cursors. It was
actually my suggestion to transparently use server side cursors, but I
never considered the impact on something like pgbouncer.
Can you please create a new ticket for this problem, and link it back
here? I'm not sure how we'd go about making the situation right, but let's
track that discussion on the other ticket.
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:34>
Django
-------------------------------------+-------------------------------------
Reporter: Dan McGee | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version: 1.3
(models, ORM) |
Severity: Normal | Resolution:
Keywords: memory cursors | Triage Stage: Accepted
database |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Sergey Fursov):
Hi Josh,
Thanks for quick answer and confirming my concerns
Here is the new ticket for tracking discussion related to my problem -
#28062
--
Ticket URL: <https://code.djangoproject.com/ticket/16614#comment:35>