Django单元测试在PostgreSQL下报connection already closed错误

环境信息 #

• Python 3.6
• Django 2.2.28
• 单元测试在SQLite环境运行完全正常，切换到PostgreSQL时失败

报错堆栈 #

.tox/commonenv/lib/python3.6/site-packages/django/http/response.py:169: in set_cookie
    self.cookies[key] = value
/usr/local/lib/python3.6/http/cookies.py:518: in __setitem__
    if isinstance(value, Morsel):
.tox/commonenv/lib/python3.6/site-packages/django/utils/functional.py:256: in inner
    self._setup()
.tox/commonenv/lib/python3.6/site-packages/django/utils/functional.py:392: in _setup
    self._wrapped = self._setupfunc()
.tox/commonenv/lib/python3.6/site-packages/django/contrib/auth/middleware.py:24: in <lambda>
    request.user = SimpleLazyObject(lambda: get_user(request))
.tox/commonenv/lib/python3.6/site-packages/django/contrib/auth/middleware.py:12: in get_user
    request._cached_user = auth.get_user(request)
.tox/commonenv/lib/python3.6/site-packages/django/contrib/auth/__init__.py:189: in get_user
    user = backend.get_user(user_id)
.tox/commonenv/lib/python3.6/site-packages/django/contrib/auth/backends.py:102: in get_user
    user = UserModel._default_manager.get(pk=user_id)
.tox/commonenv/lib/python3.6/site-packages/django/db/models/manager.py:82: in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
.tox/commonenv/lib/python3.6/site-packages/django/db/models/query.py:402: in get
    num = len(clone)
.tox/commonenv/lib/python3.6/site-packages/django/db/models/query.py:256: in __len__
    self._fetch_all()
.tox/commonenv/lib/python3.6/site-packages/django/db/models/query.py:1242: in _fetch_all
    self._result_cache = list(self._iterable_class(self))
.tox/commonenv/lib/python3.6/site-packages/django/db/models/query.py:55: in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
.tox/commonenv/lib/python3.6/site-packages/django/db/models/sql/compiler.py:1140: in execute_sql
    cursor = self.connection.cursor()
.tox/commonenv/lib/python3.6/site-packages/django/db/backends/base/base.py:256: in cursor
    return self._cursor()
.tox/commonenv/lib/python3.6/site-packages/django/db/backends/base/base.py:235: in _cursor
    return self._prepare_cursor(self.create_cursor(name))
.tox/commonenv/lib/python3.6/site-packages/django/db/utils.py:89: in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
.tox/commonenv/lib/python3.6/site-packages/django/db/backends/base/base.py:235: in _cursor
    return self._prepare_cursor(self.create_cursor(name))
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <django.db.backends.postgresql.base.DatabaseWrapper object at 0x7fd3e015f710>, name = None

    def create_cursor(self, name=None):
        if name:
            # In autocommit mode, the cursor will be used outside of a
            # transaction, hence use a holdable cursor.
            cursor = self.connection.cursor(name, scrollable=False, withhold=self.connection.autocommit)
        else:
>           cursor = self.connection.cursor()
E           django.db.utils.InterfaceError: connection already closed

.tox/commonenv/lib/python3.6/site-packages/django/db/backends/postgresql/base.py:223: InterfaceError

依赖包列表 #

deps = pip == 20.2.4
       setuptools == 45.1.0
       pytest == 6.1.2
       pytest-django == 4.4.0
       faker == 5.0.0
       factory_boy == 2.12.0
       pytest-factoryboy == 2.0.3
       pytest-cov == 2.8.1
       pytest-xdist == 1.34.0
       pylint == 2.4.2
       pylint-django == 2.0.11
       pylint-celery == 0.3

单元测试代码 #

@pytest.mark.django_db
@pytest.mark.small
@pytest.mark.parametrize("url", [
    "/site-url/preview/{}/",
    "/console/sites/{0}/pages/{1}"
])
def test_renders_userid(admin_client, content_version_factory, url):
    version = content_version_factory()
    if "{1}" in url:
        url = url.format(version.page.site.pk, version.page.pk)
    else:
        url = url.format(version.uuid)
    assert admin_client.cookies.get("userId") is None
    response = admin_client.get(url)
    assert response.status_code == 200
    response_user_id = response.cookies.get("userId")
    assert response_user_id is not None
    assert response_user_id.value == "admin"
    admin_user_id = admin_client.cookies.get("userId")
    assert admin_user_id is not None
    assert admin_user_id.value == "admin"
    admin_client.cookies["userId"] = "some.user"
    response = admin_client.get(url)
    assert response.status_code == 200
    response_user_id = response.cookies.get("userId")
    assert response_user_id is None
    assert admin_user_id is not None
    assert admin_user_id.value == "some.user"
    admin_client.logout()
    assert admin_client.cookies.get("userId") is None

解决方案 #

1. 排查pytest-xdist并行测试的连接冲突 #

PostgreSQL的数据库连接管理和SQLite差异很大，并行测试时多个worker可能复用或抢占连接导致异常。

• 先尝试单进程运行测试，验证是否是并行导致的问题：

  pytest tests/your_test_module.py --numprocesses 1
  

• 如果单进程正常，修改pytest配置文件（pytest.ini），启用数据库复用并调整并行策略：

  [pytest]
  django_db_reuse_db = True
  addopts = --reuse-db
  

  或者在运行时添加--reuse-db参数，避免每次测试重新创建数据库，减少连接开销。

2. 修正测试中客户端cookie的操作方式 #

直接修改admin_client.cookies可能绕过Django的连接状态管理，导致后续请求时连接异常：

• 替换手动修改cookie的逻辑，改用标准的登录流程：

  # 先登出原admin用户
  admin_client.logout()
  # 创建测试用户（可通过factory提前创建）
  test_user = UserFactory(username="some.user")
  # 使用标准登录方法
  admin_client.login(username="some.user", password="test_password")
  # 发起请求
  response = admin_client.get(url)
  

• 如果必须手动操作cookie，在修改后尝试重置客户端的连接状态，或者重新获取客户端实例。

3. 确保PostgreSQL驱动版本兼容 #

Django 2.2.28需要匹配兼容的psycopg2版本，Python3.6推荐使用psycopg2-binary==2.8.6：

• 在依赖列表中添加该驱动：

  psycopg2-binary == 2.8.6
  

  安装后重新运行测试。

4. 检查Django数据库连接配置 #

确保settings.py中的PostgreSQL配置正确，尤其是连接池和超时设置：

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'test_db',
        'USER': 'db_user',
        'PASSWORD': 'db_pass',
        'HOST': 'localhost',
        'PORT': '5432',
        # 测试环境建议设置为0，避免连接复用导致的异常
        'CONN_MAX_AGE': 0,
    }
}

内容的提问来源于stack exchange，提问作者Andrei Lupuleasa