Django View Causes Psycopg2 Cursor Does/Does Not Exist Error

Question

I run a Django site which has a simple ModelForm type view that is generating cursor errors. In the past two days, this view was POSTed to a couple hundred times and about 8% of the time generated an error. I ONLY have this problem with this view, even though I have another which is very similar. That's the frustrating thing is I haven't figured out what's special about it. I just started seeing these errors after upgrading to Django 2.1/2, but I think they may have pre-existed, yet were not seen.

Full stack trace here: https://gist.github.com/jplehmann/ad8849572e569991bc26da87c81bb8f4

Some examples from logging from query [error] (internal users edit) OR (psycopg2 errors cursor) with usernames redacted, to show timing:

Jun 04 12:42:12 ballprice app/web.1: [ERROR] Internal Server Error: /users/a/edit  [log:228]
Jun 04 12:42:12 ballprice app/web.1: psycopg2.errors.InvalidCursorName: cursor "_django_curs_140401754175232_2" does not exist
Jun 04 12:42:12 ballprice app/web.1: psycopg2.errors.InvalidCursorName: cursor "_django_curs_140401754175232_2" does not exist
Jun 04 12:42:27 ballprice app/web.1: [ERROR] Internal Server Error: /users/a/edit  [log:228]
Jun 04 12:42:27 ballprice app/web.1: psycopg2.errors.InvalidCursorName: cursor "_django_curs_140401754175232_3" does not exist
Jun 04 12:57:51 ballprice app/web.3: [ERROR] Internal Server Error: /users/a/edit  [log:228]
Jun 04 12:57:51 ballprice app/web.3: psycopg2.errors.DuplicateCursor: cursor "_django_curs_140092205262592_2" already exists
Jun 04 12:57:51 ballprice app/web.3: psycopg2.errors.InvalidCursorName: cursor "_django_curs_140092205262592_2" does not exist
Jun 04 13:10:50 ballprice app/web.3: [ERROR] Internal Server Error: /users/b/edit  [log:228]
Jun 04 13:10:50 ballprice app/web.3: psycopg2.errors.DuplicateCursor: cursor "_django_curs_140092205262592_2" already exists
Jun 04 15:19:36 ballprice app/web.9: [ERROR] Internal Server Error: /users/c/edit  [log:228]
Jun 04 15:19:36 ballprice app/web.9: psycopg2.errors.InvalidCursorName: cursor "_django_curs_140515167295232_1" does not exist
Jun 04 17:28:22 ballprice app/web.5: [ERROR] Internal Server Error: /users/d/edit  [log:228]
Jun 04 17:28:22 ballprice app/web.5: psycopg2.errors.InvalidCursorName: cursor "_django_curs_140085445728000_2" does not exist
Jun 04 17:28:22 ballprice app/web.5: psycopg2.errors.InvalidCursorName: cursor "_django_curs_140085445728000_2" does not exist
Jun 04 22:49:15 ballprice app/web.1: [ERROR] Internal Server Error: /users/e/edit  [log:228]
Jun 04 22:49:15 ballprice app/web.1: psycopg2.errors.InvalidCursorName: cursor "_django_curs_139902341289728_2" does not exist
Jun 04 22:49:15 ballprice app/web.1: psycopg2.errors.InvalidCursorName: cursor "_django_curs_139902341289728_2" does not exist
Jun 04 23:43:26 ballprice app/web.1: [ERROR] Internal Server Error: /users/f/edit  [log:228]
Jun 04 23:43:26 ballprice app/web.1: psycopg2.errors.DuplicateCursor: cursor "_django_curs_139902341289728_2" already exists
Jun 05 02:49:22 ballprice app/web.1: [ERROR] Internal Server Error: /users/g/edit  [log:228]
Jun 05 02:49:22 ballprice app/web.1: psycopg2.errors.InvalidCursorName: cursor "_django_curs_140092373694208_1" does not exist
Jun 05 02:49:22 ballprice app/web.1: psycopg2.errors.InvalidCursorName: cursor "_django_curs_140092373694208_1" does not exist
Jun 05 02:49:41 ballprice app/web.1: [ERROR] Internal Server Error: /users/g/edit  [log:228]
Jun 05 02:49:41 ballprice app/web.1: psycopg2.errors.DuplicateCursor: cursor "_django_curs_140092373694208_1" already exists

However, I am unable to reproduce this error. One user I talked to said they tried and it saved on the 3rd time.

You can see the named cursors are getting reused quite a bit, many minutes apart, which I can only assume is normal.

Versions:

• Python python-3.7.2
• Django==2.2.12
• psycopg2-binary==2.8.5

What could cause this?

Update

We do use PG bouncer, and advice to disable server side cursors all together was solid, and seems to have worked.

Answer 1

Are you using pgBouncer, or some other pooling mechanism? I usually encountered this kind of issues when some form of connection pooling was used to lessen the connection-load on the database (which is perfectly fine and advisable, if you happen to have lots of clients).

https://docs.djangoproject.com/en/3.0/ref/databases/#transaction-pooling-and-server-side-cursors

Using a connection pooler in transaction pooling mode (e.g. PgBouncer) requires disabling server-side cursors for that connection.

Server-side cursors are local to a connection and remain open at the end of a transaction when AUTOCOMMIT is True. A subsequent transaction may attempt to fetch more results from a server-side cursor. In transaction pooling mode, there’s no guarantee that subsequent transactions will use the same connection. If a different connection is used, an error is raised when the transaction references the server-side cursor, because server-side cursors are only accessible in the connection in which they were created.

One solution is to disable server-side cursors for a connection in DATABASES by setting DISABLE_SERVER_SIDE_CURSORS to True.

To benefit from server-side cursors in transaction pooling mode, you could set up another connection to the database in order to perform queries that use server-side cursors. This connection needs to either be directly to the database or to a connection pooler in session pooling mode.

Another option is to wrap each QuerySet using server-side cursors in an atomic() block, because it disables autocommit for the duration of the transaction. This way, the server-side cursor will only live for the duration of the transaction.

So, if this applies to your connection, your options are:

disable cursors

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'DISABLE_SERVER_SIDE_CURSORS': True,
    }
}

wrap into transaction

(not guaranteed to work, depends on your pooling settings)

with transaction.atomic():
     qs = YourModel.objects.filter()
     for values in qs.values('id', 'x').iterator():
        pass

extra connection

You could also use an extra direct connection to the database if you need server side cursors and then use the direct connection for those queries.

YourModel.objects.using('different_db_connection_id').filter().iterator()

Answer 2

Usually, this error append when the models and the database are not compatible. Like if you changed a model adding a field but didn't migrate it.

Be sure to check that all the applications of your site are in your INSTALLED_APP this error can be caused because the migrations doesn't apply on a new non declared app. Then

python manage.py makemigrations && python manage.py migrate

Answer 3

Each of the below mentioned solutions has its own cons.

• disable cursors: We'll lose benefits of server side cursors (chunked resultset).
• wrap into transaction: This adds overhead of transaction and can decrease the query execution throughput on high traffic sites which uses lot of .iterator() querysets.
• extra connection: Developers have to remember to use separate database for .iterator() queryset.

So better approach could be to use two database settings. One for PgBouncer and second for direct db connection. (Both the database setting should point to same database in the backend) And route .iterator() queryset to use direct db connection based on transaction status.

Note: We should leave DISABLE_SERVER_SIDE_CURSORS=False (for both db settings) as PgBouncer supports server side cursors when iterator queryset is wrapped inside transaction.

DATABASE_URL: 'postgresql://django:xxx@localhost:7432/dbname'   # (pgbouncer connection)
DATABASE_URL_DIRECT: 'postgresql://django:xxx@localhost:6432/dbname' # (direct db connection)

inside settings.py

USE_PGBOUNCER = True
if USE_PGBOUNCER
    if 'migrate' not in sys.argv:
        # django app proccess
        DATABASES = {
            'default': dj_database_url.parse(config['DATABASE_URL']), # (pgbouncer connection)
            'direct_db': dj_database_url.parse(config['DATABASE_URL_DIRECT'])  # (direct db connection)
        }
     else:
        # django migration proccess
        DATABASES = {
            'default': dj_database_url.parse(config['DATABASE_URL_DIRECT'])  # (direct db connection)
        }
else:
    # not using pgbouncer.
    DATABASES = {
        'default': dj_database_url.parse(config['DATABASE_URL'])   # (direct db connection)
    }

while initilizing the django apps (inside AppConfig.ready())

from functools import wraps

from django.apps import AppConfig
from django.conf import settings
from django.db import transaction
from django.db.models.query import ModelIterable, ValuesIterable, ValuesListIterable, \
    NamedValuesListIterable, FlatValuesListIterable


class CommonAppConfig(AppConfig):
    name = 'app_name'

    def ready(self):

        if settings.USE_PGBOUNCER:
            direct_db = 'direct_db'. # DATABASE setting
            ModelIterable.__iter__ = patch_iterator_class(using=direct_db)(ModelIterable.__iter__)
            ValuesIterable.__iter__ = patch_iterator_class(using=direct_db)(ValuesIterable.__iter__)
            ValuesListIterable.__iter__ = patch_iterator_class(using=direct_db)(ValuesListIterable.__iter__)
            NamedValuesListIterable.__iter__ = patch_iterator_class(using=direct_db)(NamedValuesListIterable.__iter__)
            FlatValuesListIterable.__iter__ = patch_iterator_class(using=direct_db)(FlatValuesListIterable.__iter__)


def patch_iterator_class(using):
    def decorator(func):
        @wraps(func)
        def wrapper(self, *args, **kwargs):
            cxn = transaction.get_connection()
            if not self.chunked_fetch or cxn.in_atomic_block:
                # We are already in db transaction so use the same db connection (default) using
                # which db transaction was started to execute iterator query.
                # Or
                # We are neither in db transaction nor it is a chunked_fetch so continue over same db connection
                return func(self, *args, **kwargs)
            # We are not in any db transaction and it is chunked_fetch so redirect iterator query to use
            # direct_db connection to avoid cursor not found exception.
            self.queryset = self.queryset.using(using)  # redirect query to use direct db connection.
            return func(self, *args, **kwargs)
        return wrapper
    return decorator