Cursors are being closed explicitly in autocommit mode

[Ali Teoman Unay]

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 ​psycopg documentation, server-side cursors should not be closed explicitly if it is in autocommit mode.

According to ​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().

[Tim Graham]

Duplicate of #29257?

[Ali Teoman Unay]

Replying to 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.

[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."

[Ali Teoman Unay]

Replying to 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 ​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.

[Tim Graham]

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.

[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.

[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?

[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?

[Barış Yeşilçiçek]

I work with the OP and can confirm that this is a duplicate of #28062.