"Database test_postgres_gw6 couldn't be flushed." when running tests

[comonadd]

Running tests in a fairly large codebase, which mostly does integration/functional tests, etc. sets up Django application, make API calls to it, sets up database entities through mocks beforehand, etc. There are tests that use @pytest.mark.django_db(transaction=True), most don't.

The tests run mostly okay, but some times, for some reason, the exception about not being able to flush the database pops up. (attached at the bottom).

I have thought about the following: Maybe this happens because we are using Celery, and tests obviously run Celery tasks sometimes too. And Celery runs them in a separate thread, and when test finishes, task is still not finished, and we are trying to flush, and that's how it fails. So I tried fixing it by waiting for threads to finish before tearing down:

import threading
from typing import Any

pytest_plugins = (
    'api.test_utils.fixtures.auto',
    'api.test_utils.fixtures.common',
)


def pytest_runtest_teardown(item: Any, nextitem: Any) -> None:
    """
    Wait for all non-daemon threads to finish before test cleanup.

    This should fix "couldn't flush the database" error that sometimes happens...
    """

    # Get all active threads except main thread
    active_threads = [
        t for t in threading.enumerate() if t != threading.main_thread() and not t.daemon
    ]

    if active_threads:
        logger.info('\nWaiting for threads to finish...', threads_num=len(active_threads))

        for thread in active_threads:
            logger.info('Waiting for thread', thread_name=thread.name)
            thread.join(timeout=30)

            if thread.is_alive():
                logger.info("Warning: Thread didn't finish in time", thread_name=thread.name)

This seemed to work... at first, but we're still getting the same error once in a couple of days. I couldn't find any correlation.

Looking for help and advice on this one!

Python version: 3.10.18
Package versions:

Django = "==4.2.24"
django-extensions = "==3.2.3"
psycopg2-binary = "==2.9.9"
celery = "==5.4.0"
pytest-django = "==4.8.0"
pytest-mock = "==3.14.0"
pytest = "==7.4.0"

This is the exception that happens:

__ ERROR at teardown of test_abc ___
[gw6] linux -- Python 3.10.18 /usr/local/bin/python
/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py:87: in _execute
    return self.cursor.execute(sql)
E   psycopg2.errors.QueryCanceled: canceling statement due to statement timeout

The above exception was the direct cause of the following exception:
/usr/local/lib/python3.10/site-packages/django/core/management/commands/flush.py:73: in handle
    connection.ops.execute_sql_flush(sql_list)
/usr/local/lib/python3.10/site-packages/django/db/backends/base/operations.py:451: in execute_sql_flush
    cursor.execute(sql)
/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py:67: in execute
    return self._execute_with_wrappers(
/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py:80: in _execute_with_wrappers
    return executor(sql, params, many, context)
/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py:84: in _execute
    with self.db.wrap_database_errors:
/usr/local/lib/python3.10/site-packages/django/db/utils.py:91: in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py:87: in _execute
    return self.cursor.execute(sql)
E   django.db.utils.OperationalError: canceling statement due to statement timeout

The above exception was the direct cause of the following exception:
/usr/local/lib/python3.10/site-packages/pytest_django/fixtures.py:254: in _django_db_helper
    test_case._post_teardown()
/usr/local/lib/python3.10/site-packages/django/test/testcases.py:1279: in _post_teardown
    self._fixture_teardown()
/usr/local/lib/python3.10/site-packages/django/test/testcases.py:1313: in _fixture_teardown
    call_command(
/usr/local/lib/python3.10/site-packages/django/core/management/__init__.py:194: in call_command
    return command.execute(*args, **defaults)
/usr/local/lib/python3.10/site-packages/django/core/management/base.py:458: in execute
    output = self.handle(*args, **options)
/usr/local/lib/python3.10/site-packages/django/core/management/commands/flush.py:75: in handle
    raise CommandError(
E   django.core.management.base.CommandError: Database test_postgres_gw6 couldn't be flushed. Possible reasons:
E     * The database isn't running or isn't configured correctly.
E     * At least one of the expected database tables doesn't exist.
E     * The SQL was invalid.
E   Hint: Look at the output of 'django-admin sqlflush'. That's the SQL this command wasn't able to run.

[kingbuzzman]

Your traceback shows Django’s flush getting cancelled by Postgres’ statement_timeout, which almost always means something in your own test process is still holding a lock or running a long query when teardown tries to sqlflush. Given your description (Celery tasks, threads, some @pytest.mark.django_db(transaction=True) tests), this is consistent with user behavior rather than a framework issue. In order to debug this we would need a lot more code, to trace the true issue. Sounds like you're doing integration tests with pytest as the orchestrator?