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:
What could cause this?
We do use PG bouncer, and advice to disable server side cursors all together was solid, and seems to have worked.
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
DATABASESby settingDISABLE_SERVER_SIDE_CURSORSto 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()