[Django] #28062: Using iterators behind pgBouncer leads to non-existent cursor erros
Django
-------------------------------------+-------------------------------------
Reporter: Sergey | Owner: nobody
Fursov |
Type: | Status: new
Uncategorized |
Component: Database | Version: 1.11
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Hi!
In our application stack we connect from our web/background workers to
postgres database through dedicated pgBouncer service.
pgBouncer is running in transaction pooling mode and django connections
work in autocommit mode.
After upgrading to 1.11 version with new server-side cursors for queryset
iterators, many of our DB requests were started failing with "Cursor
_django_curs_<id> does not exist'.
According to pgBouncer docs (https://wiki.postgresql.org/wiki/PgBouncer),
it doesn't support using WITH HOLD cursors with transaction pooling mode.
But it doesn't seems reasonable to run pgBouncer in session pooling mode.
We can just switch to direct persistent connections to postgres DB, but in
this case we have to increase minimum number of DB connection to number of
our workers (from 5 to ~30-40) to prevent blocking requests and this will
violate recommendation to have (core_count * 2) + effective_spindle_count
max connections to DB
(https://wiki.postgresql.org/wiki/Number_Of_Database_Connections).
Introducing server-side cursors seems like breaking change in our case
(and i guess many django apps use pgBouncer behind Postgres DB)
So is there some recommendations how efficiently work with postgres DB in
django 1.11?
Looks like another possible option is wrapping all iterator() calls in
their own transactions, 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.
Thanks,
Sergey
--
Ticket URL: <https://code.djangoproject.com/ticket/28062>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: Sergey Fursov | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* type: Uncategorized => Bug
* severity: Normal => Release blocker
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/28062#comment:1>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: josh.smeaton@… (added)
* severity: Release blocker => Normal
Comment:
I don't think documentation is a good enough solution for this. Lots of
people use pgbouncer, and any of them using .iterator() are about to start
experiencing crashes with what was perfectly working code. It's not fair
to ask people to change code or configuration for what should have been a
transparent performance (memory) improvement.
I think our options are limited, but I'd be keen to hear from others how
we should approach this particular bug.
- We could revert the change, and revisit for Django 2.0.
- We could also add an argument to .iterator() that allows users to opt-in
to the new server side cursors. We'd need to document the limitations, and
suggest ideal scenarios (use transaction.atomic, or
.using('non_pgbouncer_transaction_connection')).
For your particular setup, I'd maybe recommend running a second pgbouncer
in session mode (or not use pgbouncer), and for queries that use
.iterator(), using that specific connection. As long as iterator() isn't
used in the majority of your requests, the number of connections shouldn't
spike too high.
--
Ticket URL: <https://code.djangoproject.com/ticket/28062#comment:2>
Django
Reporter: Sergey Fursov | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* severity: Normal => Release blocker
--
Ticket URL: <https://code.djangoproject.com/ticket/28062#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: Sergey Fursov | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Sergey Fursov):
Hi Josh,
> For your particular setup, I'd maybe recommend running a second
pgbouncer in session mode (or not use pgbouncer), and for queries that use
.iterator(), using that specific connection. As long as iterator() isn't
used in the majority of your requests, the number of connections shouldn't
spike too high.
This is reasonable suggestion, thanks
> We could also add an argument to .iterator() that allows users to opt-in
to the new server side cursors. We'd need to document the limitations, and
suggest ideal scenarios (use transaction.atomic, or
.using('non_pgbouncer_transaction_connection')).
This sound like the best option for me (at least compared to reverting
changes)
Another my point was the fact, that django use iterator internally in
ModelChoiceField for optimizing iteration over queryset and in several
serializer methods.
For my particular case it would be enough to add additional
ModelChoiceIterator to override default ModelChoiceField.iterator field
(we don't use DB serialization much, so i don't know, does serialization
part have big impact on production using). But i think this point should
be considered while working on this issue, too, because it might break
apps in production.
--
Ticket URL: <https://code.djangoproject.com/ticket/28062#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: Sergey Fursov | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: mail@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/28062#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: Sergey Fursov | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Florian Apolloner):
Replying to [comment:2 Josh Smeaton]:
> - We could also add an argument to .iterator() that allows users to opt-
in to the new server side cursors. We'd need to document the limitations,
and suggest ideal scenarios (use transaction.atomic, or
.using('non_pgbouncer_transaction_connection')).
Opting in seems like a reasonable thing to do, though given the fact that
it will generally not work for anyone using pgbouncer in a specific mode,
it might make more sense if we'd also provide a switch to turn this off/on
globally (database options).
--
Ticket URL: <https://code.djangoproject.com/ticket/28062#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: Sergey Fursov | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by holvianssi):
Opt-in is a good solution for end user code, but then library code can't
opt-in, as library code can't know if it's running in pgBouncer setup or
not. A database setting would get rid of this problem, but if there are
cases where you want to use cursors for some cases, but not all of the
cases, then you are screwed. The complex solution is to add both the opt-
in flag and database settings. The default for the opt-in flag is None,
meaning use the default from database settings. True forces this feature
on, and False forces the feature off. Now, this covers all cases, but
might create a situation where it's not easy for users to understand when
exactly cursors are used.
I'm slightly favouring the opt-in + database setting approach, next would
be opt-in without database setting, and last database setting without opt-
in. All of those solutions are fine for me, but I don't like the idea of
removing the feature.
--
Ticket URL: <https://code.djangoproject.com/ticket/28062#comment:7>
Django
-------------------------------------+-------------------------------------
| Freitag
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => François Freitag
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/28062#comment:8>
Django
-------------------------------------+-------------------------------------
Reporter: Sergey Fursov | Owner: François
| Freitag
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
Comment:
[https://github.com/django/django/pull/8430 PR]
Working on this issue, I came to the conclusions that {{{None}}} is not
necessary and I prefer the simpler scheme where:
* the use of server-side cursors is disabled for a connection if the
setting is {{{False}}}.
* when the setting is {{{True}}}, server-side cursors are used by default
and users and library code can opt-out specifying
{{{chunked_fetch=False}}}.
I believe auto opt-in server-side cursors is acceptable, since it's a
memory improvement in most case. The issue here is that server-side
cursors cannot be used with transaction pooling, which is global to a
connection. Setting {{{ENABLE_SERVER_SIDE_CURSORS}}} to {{{False}}} for
the whole connection makes sense and should disable the use of server-side
cursors regardless of the {{{chunked_fetch}}} keyword argument.
For use cases where server-side cursors are not desirable, the user /
library-code can now turn them off explicitly using
{{{chunked_fetch=False}}}.
I think this solution is simpler and covers the use cases I can think of.
Am I missing something?
--
Ticket URL: <https://code.djangoproject.com/ticket/28062#comment:9>
Django
-------------------------------------+-------------------------------------
Reporter: Sergey Fursov | Owner: François
| Freitag
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: lcannon@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/28062#comment:10>
Django
-------------------------------------+-------------------------------------
Reporter: Sergey Fursov | Owner: François
| Freitag
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Release blocker | Resolution:
| 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/28062#comment:11>
Django
-------------------------------------+-------------------------------------
Reporter: Sergey Fursov | Owner: François
| Freitag
Component: Database layer | Version: 1.11
(models, ORM) |
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"88336fdbb5e101fa25825b737169c0d6af2faa93" 88336fdb]:
{{{
#!CommitTicketReference repository=""
revision="88336fdbb5e101fa25825b737169c0d6af2faa93"
Fixed #28062 -- Added a setting to disable server-side cursors on
PostgreSQL.
When a connection pooler is set up in transaction pooling mode, queries
relying on server-side cursors fail. The DISABLE_SERVER_SIDE_CURSORS
setting in DATABASES disables server-side cursors for this use case.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28062#comment:12>
Django
-------------------------------------+-------------------------------------
Reporter: Sergey Fursov | Owner: François
| Freitag
Type: Bug | Status: closed
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham <timograham@…>):
In [changeset:"6a2624286b0501942522726bfdcb18537a86ce7c" 6a26242]:
{{{
#!CommitTicketReference repository=""
revision="6a2624286b0501942522726bfdcb18537a86ce7c"
[1.11.x] Fixed #28062 -- Added a setting to disable server-side cursors on
PostgreSQL.
When a connection pooler is set up in transaction pooling mode, queries
relying on server-side cursors fail. The DISABLE_SERVER_SIDE_CURSORS
setting in DATABASES disables server-side cursors for this use case.
Backport of 88336fdbb5e101fa25825b737169c0d6af2faa93 from master
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28062#comment:13>
Django
-------------------------------------+-------------------------------------
Reporter: Sergey Fursov | Owner: François
| Freitag
Type: Bug | Status: closed
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Luke Plant):
There really needs to be a note about this in the 1.11 release notes, as
it's a breaking change that affects a pretty common set up. There is a
note only in the 1.11.1 notes, which is not where people look.
--
Ticket URL: <https://code.djangoproject.com/ticket/28062#comment:14>
Django
-------------------------------------+-------------------------------------
Reporter: Sergey Fursov | Owner: François
| Freitag
Type: Bug | Status: closed
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham):
[https://github.com/django/django/pull/9863 PR] for an addition to the
1.11 release notes.
--
Ticket URL: <https://code.djangoproject.com/ticket/28062#comment:15>
Django
-------------------------------------+-------------------------------------
Reporter: Sergey Fursov | Owner: François
| Freitag
Type: Bug | Status: closed
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by GitHub <noreply@…>):
In [changeset:"2919a08c20d5ae48e381d6bd251d3b0d400d47d9" 2919a08c]:
{{{
#!CommitTicketReference repository=""
revision="2919a08c20d5ae48e381d6bd251d3b0d400d47d9"
Refs #28062 -- Doc'd PostgreSQL server-side cursors as a backwards
incompatible change.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28062#comment:16>
Django
-------------------------------------+-------------------------------------
Reporter: Sergey Fursov | Owner: François
| Freitag
Type: Bug | Status: closed
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham <timograham@…>):
In [changeset:"2e7b1e32976127bbe71320c848ce89b6e4b7e1a3" 2e7b1e32]:
{{{
#!CommitTicketReference repository=""
revision="2e7b1e32976127bbe71320c848ce89b6e4b7e1a3"
[2.0.x] Refs #28062 -- Doc'd PostgreSQL server-side cursors as a backwards
incompatible change.
Backport of 2919a08c20d5ae48e381d6bd251d3b0d400d47d9 from master
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28062#comment:17>
Django
-------------------------------------+-------------------------------------
Reporter: Sergey Fursov | Owner: François
| Freitag
Type: Bug | Status: closed
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham <timograham@…>):
In [changeset:"0037cd1fa0f45c9a8545dcc16bdee7f71f94c773" 0037cd1f]:
{{{
#!CommitTicketReference repository=""
revision="0037cd1fa0f45c9a8545dcc16bdee7f71f94c773"
[1.11.x] Refs #28062 -- Doc'd PostgreSQL server-side cursors as a
backwards incompatible change.
Backport of 2919a08c20d5ae48e381d6bd251d3b0d400d47d9 from master
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28062#comment:18>
Django
-------------------------------------+-------------------------------------
Reporter: Sergey Fursov | Owner: François
| Freitag
Type: Bug | Status: closed
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by jdlourenco):
I am running into what seems to be the same problem, however I'm not using
''pgBouncer''.
I'm connecting directly to a **redshift** database and I get this error
using either the **django.db.backends.postgresql_psycopg2** or the
**django_redshift_backend** engines.
{{{
'redshift': {
'NAME': 'dbname',
...
'ENGINE': 'django.db.backends.postgresql_psycopg2',
'HOST': 'xxxxxxxxxx.redshift.amazonaws.com',
'PORT': 5439,
}
}}}
I've enabled DEBUG logging and I can see Django creating a server side
cursor by using a ''DECLARE WITH HOLD'' statement. It then issues two
queries and the second fails with a ''cursor "_django_curs_XXXXXXX" does
not exist'' error message. Setting **DISABLE_SERVER_SIDE_CURSORS=true**
seems to solve the issue.
According the Django documentation, this happens due to transaction
pooling resulting in Django using a different connection for which the
named cursor does not exist the second time it issues the query. However I
can't understand why this is happening since afaik I'm not using
''pgBouncer'' nor any other middleware implementing transaction pooling.
Can you help me understand why this is happening?
--
Ticket URL: <https://code.djangoproject.com/ticket/28062#comment:19>