[Django] #29760: Cursors are closing explicitly in autocommit mode
Django
-------------------------------------+-------------------------------------
Reporter: dhargan | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 2.1
layer (models, ORM) | Keywords: cursor, sql,
Severity: Normal | database, autocommit, transactions,
Triage Stage: | postrgresql, psycopg
Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Recently we started to get this exception time to time:
{{{
OperationalError: cursor “_django_curs_<id>” does not exist
}}}
especially when our traffic is higher than usual. Each time the error is
in a different line of the code so it is not easy to follow but
appearantly it is a synchronisation issue; the cursor is being closed
before the transaction ended.
In django.db.models.sql.compiler.SQLAggregateCompiler:
{{{
def cursor_iter(cursor, sentinel, col_count, itersize):
"""
Yield blocks of rows from a cursor and ensure the cursor is closed
when
done.
"""
try:
for rows in iter((lambda: cursor.fetchmany(itersize)), sentinel):
yield rows if col_count is None else [r[:col_count] for r in
rows]
finally:
cursor.close()
}}}
According to [http://initd.org/psycopg/docs/usage.html#server-side-cursors
psycopg documentation], server-side cursors should not be closed
explicitly if it is in autocommit mode.
According to
[https://docs.djangoproject.com/en/2.1/topics/db/transactions/#autocommit
Django documentation], Django sets autocommit mode true in default
settings.
Of course it is possible to set withhold setting to false. In this case,
cursor.close() must be called at the end but otherwise, according to the
documentation, it should not be called at all. So if I am not mistaken
there should be a conditional statement before calling cursor.close().
--
Ticket URL: <https://code.djangoproject.com/ticket/29760>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: dhargan | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: cursor, sql, | Triage Stage:
database, autocommit, | Unreviewed
transactions, postrgresql, |
psycopg |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
--
Ticket URL: <https://code.djangoproject.com/ticket/29760#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: dhargan | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: cursor, sql, | Triage Stage:
database, autocommit, | Unreviewed
transactions, postrgresql, |
psycopg |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham):
Duplicate of #29257?
--
Ticket URL: <https://code.djangoproject.com/ticket/29760#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: dhargan | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: cursor, sql, | Triage Stage:
database, autocommit, | Unreviewed
transactions, postrgresql, |
psycopg |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* version: 2.1 => 1.11
Old description:
> Recently we started to get this exception time to time:
>
> {{{
> OperationalError: cursor “_django_curs_<id>” does not exist
> }}}
>
> especially when our traffic is higher than usual. Each time the error is
> in a different line of the code so it is not easy to follow but
> appearantly it is a synchronisation issue; the cursor is being closed
> before the transaction ended.
>
> In django.db.models.sql.compiler.SQLAggregateCompiler:
>
> {{{
> def cursor_iter(cursor, sentinel, col_count, itersize):
> """
> Yield blocks of rows from a cursor and ensure the cursor is closed
> when
> done.
> """
> try:
> for rows in iter((lambda: cursor.fetchmany(itersize)), sentinel):
> yield rows if col_count is None else [r[:col_count] for r in
> rows]
> finally:
> cursor.close()
> }}}
>
> According to [http://initd.org/psycopg/docs/usage.html#server-side-
> cursors psycopg documentation], server-side cursors should not be closed
> explicitly if it is in autocommit mode.
>
> According to
> [https://docs.djangoproject.com/en/2.1/topics/db/transactions/#autocommit
> Django documentation], Django sets autocommit mode true in default
> settings.
>
> Of course it is possible to set withhold setting to false. In this case,
> cursor.close() must be called at the end but otherwise, according to the
> documentation, it should not be called at all. So if I am not mistaken
> there should be a conditional statement before calling cursor.close().
New description:
Recently we started to get this exception time to time:
{{{
OperationalError: cursor “_django_curs_<id>” does not exist
}}}
especially when our traffic is higher than usual. Each time the error is
in a different line of the code so it is not easy to follow but
appearantly it is a synchronisation issue; the cursor is being closed
before the transaction ended.
In django.db.models.sql.compiler.SQLAggregateCompiler:
{{{
def cursor_iter(cursor, sentinel, col_count, itersize):
"""
Yield blocks of rows from a cursor and ensure the cursor is closed
when
done.
"""
try:
for rows in iter((lambda: cursor.fetchmany(itersize)), sentinel):
yield rows if col_count is None else [r[:col_count] for r in
rows]
finally:
cursor.close()
}}}
According to [http://initd.org/psycopg/docs/usage.html#server-side-cursors
psycopg documentation], server-side cursors should not be closed
explicitly if it is in autocommit mode.
According to
[https://docs.djangoproject.com/en/1.11/topics/db/transactions/#autocommit
Django documentation], Django sets autocommit mode true in default
settings.
Of course it is possible to set withhold setting to false. In this case,
cursor.close() must be called at the end but otherwise, according to the
documentation, it should not be called at all. So if I am not mistaken
there should be a conditional statement before calling cursor.close().
--
--
Ticket URL: <https://code.djangoproject.com/ticket/29760#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: Ali Teoman Unay | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: cursor, sql, | Triage Stage:
database, autocommit, | Unreviewed
transactions, postrgresql, |
psycopg |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Ali Teoman Unay):
Replying to [comment:2 Tim Graham]:
> Duplicate of #29257?
No they are not the same so I don't believe it's a duplicate. This issue
is not about the creation of the cursor but closing of the cursor. It
should not be closed explicitly at all if the autocommit mode is set to
true. Yet, the solution (wrapping with try/except) might be okay for this
issue also.
--
Ticket URL: <https://code.djangoproject.com/ticket/29760#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: Ali Teoman Unay | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: cursor, sql, | Triage Stage:
database, autocommit, | Unreviewed
transactions, postrgresql, |
psycopg |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham):
Can you give steps to reproduce the error?
I read the psycopg2 docs that you linked to but I didn't spot the place
where it says "server-side cursors should not be closed explicitly if it
is in autocommit mode."
--
Ticket URL: <https://code.djangoproject.com/ticket/29760#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: Ali Teoman Unay | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: cursor, sql, | Triage Stage:
database, autocommit, | Unreviewed
transactions, postrgresql, |
psycopg |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Ali Teoman Unay):
Replying to [comment:5 Tim Graham]:
> Can you give steps to reproduce the error?
>
> I read the psycopg2 docs that you linked to but I didn't spot the place
where it says "server-side cursors should not be closed explicitly if it
is in autocommit mode."
"Named cursors are usually created WITHOUT HOLD, meaning they live only as
long as the current transaction. Trying to fetch from a named cursor after
a commit() or to create a named cursor when the connection is in
autocommit mode will result in an exception. It is possible to create a
WITH HOLD cursor by specifying a True value for the withhold parameter to
cursor() or by setting the withhold attribute to True before calling
execute() on the cursor. It is extremely important to always close() such
cursors, otherwise they will continue to hold server-side resources until
the connection will be eventually closed. Also note that while WITH HOLD
cursors lifetime extends well after commit(), calling rollback() will
automatically close the cursor."
from [http://initd.org/psycopg/docs/usage.html#server-side-cursors here]
It is hard to reproduce the error since it appears each time in a
different line of our code and it is very rare. I believe it appears when
the cursor is destroyed before the transaction ended and it points to a
synchronization error which might be related to this issue.
--
Ticket URL: <https://code.djangoproject.com/ticket/29760#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: Ali Teoman Unay | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
database, autocommit, |
transactions, postrgresql, |
psycopg |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Unreviewed => Accepted
Comment:
Yes, I saw that text but I don't understand how it translates into what
you said. I guess we can accept the ticket even if the resolution is
unclear.
--
Ticket URL: <https://code.djangoproject.com/ticket/29760#comment:7>
Django
-------------------------------------+-------------------------------------
Reporter: Ali Teoman Unay | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: cursor, sql, | Triage Stage: Accepted
database, autocommit, |
transactions, postrgresql, |
psycopg |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by David Dahl):
I had this happen to me where my models.py module had been updated but I
had not yet run makemigrations or migrate. Once I migrated, everything
cleared up.
--
Ticket URL: <https://code.djangoproject.com/ticket/29760#comment:8>
Django
-------------------------------------+-------------------------------------
Reporter: Ali Teoman Unay | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: cursor, sql, | Triage Stage: Accepted
database, autocommit, |
transactions, postrgresql, |
psycopg |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Kurt Wheeler):
I'm getting the same error, while using the `iterator()` method of a
queryset:
{{{
Traceback (most recent call last):
File "/usr/local/lib/python3.5/dist-packages/django/db/utils.py", line
96, in inner
return func(*args, **kwargs)
psycopg2.OperationalError: cursor "_django_curs_140027036677888_1" does
not exist
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/usr/local/lib/python3.5/dist-
packages/django/db/models/sql/compiler.py", line 1512, in cursor_iter
for rows in iter((lambda: cursor.fetchmany(itersize)), sentinel):
File "/usr/local/lib/python3.5/dist-
packages/django/db/models/sql/compiler.py", line 1512, in <lambda>
for rows in iter((lambda: cursor.fetchmany(itersize)), sentinel):
File "/usr/local/lib/python3.5/dist-packages/django/db/utils.py", line
96, in inner
return func(*args, **kwargs)
File "/usr/local/lib/python3.5/dist-packages/django/db/utils.py", line
89, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/usr/local/lib/python3.5/dist-packages/django/db/utils.py", line
96, in inner
return func(*args, **kwargs)
django.db.utils.OperationalError: cursor "_django_curs_140027036677888_1"
does not exist
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/home/user/data_refinery_workers/processors/utils.py", line 369,
in run_pipeline
last_result = processor(last_result)
File "/home/user/data_refinery_workers/processors/qn_reference.py", line
241, in _update_caches
for experiment in unique_experiments.iterator():
File "/usr/local/lib/python3.5/dist-packages/django/db/models/query.py",
line 341, in _iterator
yield from self._iterable_class(self, chunked_fetch=use_chunked_fetch,
chunk_size=chunk_size)
File "/usr/local/lib/python3.5/dist-packages/django/db/models/query.py",
line 72, in __iter__
for row in compiler.results_iter(results):
File "/usr/local/lib/python3.5/dist-
packages/django/db/models/sql/compiler.py", line 1515, in cursor_iter
cursor.close()
psycopg2.OperationalError: cursor "_django_curs_140027036677888_1" does
not exist
}}}
This is the line of code that is causing that error
https://github.com/AlexsLemonade/refinebio/blob/dev/workers/data_refinery_workers/processors/qn_reference.py#L241
Any idea why my cursor is disappearing? I don't think I'm doing anything
unusual in my code there. The queryset it's iterating over seems to be
~20k records.
The only other thing I can think of that might be non-standard about my
configuration is that I have a pg_bouncer instance set up to batch
database connections. It seems to do a good job of pretending to be the
postgres server itself, but maybe this is a place that it doesn't?
--
Ticket URL: <https://code.djangoproject.com/ticket/29760#comment:9>
Django
-------------------------------------+-------------------------------------
Reporter: Ali Teoman Unay | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: cursor, sql, | Triage Stage: Accepted
database, autocommit, |
transactions, postrgresql, |
psycopg |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
For the record the above comment was related to the use of `pg_bouncer`
which is documented to be causing issue with `iterator()`.
> I believe it appears when the cursor is destroyed before the transaction
ended and it points to a synchronization error which might be related to
this issue.
Ali, I'm not sure I'm following along here. The ticket mentions autocommit
but you're a mentioning transactions here. Do you happen to use connection
polling as well?
--
Ticket URL: <https://code.djangoproject.com/ticket/29760#comment:10>
Django
-------------------------------------+-------------------------------------
Reporter: Ali Teoman Unay | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.11
(models, ORM) |
Keywords: cursor, sql, | Triage Stage: Accepted
database, autocommit, |
transactions, postrgresql, |
psycopg |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => duplicate
Comment:
I work with the OP and can confirm that this is a duplicate of #28062.
--
Ticket URL: <https://code.djangoproject.com/ticket/29760#comment:11>