Django View 导致Psycopg2光标不存在/不存在错误

Question

我运行一个Django站点，该站点具有一个简单的ModelForm类型 View ，该 View 正在生成光标错误。在过去的两天内，此 View 已发布了数百次，并且大约有8％的时间生成了一个错误。尽管我有另一个非常相似的观点，但我仅对此观点有此问题。那是令人沮丧的事情，我还没有弄清楚它有什么特别之处。升级到Django 2.1/2后，我才刚刚开始看到这些错误，但是我认为它们可能已经存在，但并未被看到。

完整的堆栈跟踪在这里:
https://gist.github.com/jplehmann/ad8849572e569991bc26da87c81bb8f4

来自查询[error] (internal users edit) OR (psycopg2 errors cursor)的记录中带有用户名的一些示例，以显示计时:

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

但是，我无法重现此错误。我与之交谈的一位用户说，他们尝试过，并在第三次保存。

您可以看到命名的游标正在大量重复使用，相隔数分钟，我只能认为这是正常的。

版本:

Python python-3.7.2

Django == 2.2.12

psycopg2-binary == 2.8.5

是什么原因造成的？

更新

我们确实使用PG反弹器，并且建议一起禁用所有服务器端游标是可靠的，并且似乎已经奏效。

Answer 1

您是否正在使用pgBouncer或其他某种池化机制？当使用某种形式的连接池来减轻数据库的连接负载时，我通常会遇到此类问题(如果您碰巧有很多客户端，这是很好的建议。)
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.

因此，如果这适用于您的连接，则可以选择:
禁用光标

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

包装到事务中
(不能保证工作，取决于您的池设置)

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

额外的连接
如果需要服务器端游标，还可以使用与数据库的额外直接连接，然后对这些查询使用直接连接。

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