How to access Postgresql database from multiple threads in a Django test?

Question

I have a Django website with a PostgreSQL database that runs some background tasks with multithreading. However, I am having troubles testing this multithreading part.

I followed https://docs.djangoproject.com/en/3.1/intro/tutorial01/ and https://docs.djangoproject.com/en/3.1/intro/tutorial02/ to start a new Django website. Then I added polls/tests.py as follows:

import datetime

from django.test import TestCase, TransactionTestCase
from django.utils import timezone

from .models import Question

import threading, time

class QuestionModelTests(TransactionTestCase):  # line 10
# class QuestionModelTests(TestCase):           # line 11

    def test_was_published_recently_with_future_question(self):
        Question(question_text='asdf', pub_date=datetime.datetime.now()).save()
        print('a', Question.objects.filter())
        def thread1():
            print('b', Question.objects.filter())
        t = threading.Thread(target=thread1, daemon=True)
        t.start()
        
        time.sleep(1)
        assert not t.is_alive()

I also changed settings.py to switch to the new database:

if 'POSTGRESQL':    # line 79
    DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.postgresql_psycopg2',
            'NAME': 'mysite',
            'USER': 'postgres',
            'PASSWORD': 'my_postgresql_password',
            'HOST': '127.0.0.1',
            'PORT': '5432',
        }
    }
else:               # line 90
    DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.sqlite3',
            'NAME': BASE_DIR / 'db.sqlite3',
        }
    }

However, if I run python3 manage.py test, I get this error:

(tmp) [user@localhost mysite]$ python3 manage.py test
Creating test database for alias 'default'...
Got an error creating the test database: database "test_mysite" already exists

Type 'yes' if you would like to try deleting the test database 'test_mysite', or 'no' to cancel: yes
Destroying old test database for alias 'default'...
System check identified no issues (0 silenced).
/home/user/.local/lib/python3.9/site-packages/django/db/models/fields/__init__.py:1367: RuntimeWarning: DateTimeField Question.pub_date received a naive datetime (2021-03-24 23:16:39.936567) while time zone support is active.
  warnings.warn("DateTimeField %s received a naive datetime (%s)"
a ]>
b ]>
.
----------------------------------------------------------------------
Ran 1 test in 1.156s

OK
Destroying test database for alias 'default'...
/home/user/.local/lib/python3.9/site-packages/django/db/backends/postgresql/base.py:304: RuntimeWarning: Normally Django will use a connection to the 'postgres' database to avoid running initialization queries against the production database when it's not needed (for example, when running tests). Django was unable to create a connection to the 'postgres' database and will use the first PostgreSQL database instead.
  warnings.warn(
Traceback (most recent call last):
  File "/home/user/.local/lib/python3.9/site-packages/django/db/backends/utils.py", line 82, in _execute
    return self.cursor.execute(sql)
psycopg2.errors.ObjectInUse: database "test_mysite" is being accessed by other users
DETAIL:  There is 1 other session using the database.


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/user/.local/lib/python3.9/site-packages/django/db/backends/postgresql/base.py", line 302, in _nodb_cursor
    yield cursor
  File "/home/user/.local/lib/python3.9/site-packages/django/db/backends/base/creation.py", line 293, in _destroy_test_db
    cursor.execute("DROP DATABASE %s"
  File "/home/user/.local/lib/python3.9/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/user/.local/lib/python3.9/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/user/.local/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/home/user/.local/lib/python3.9/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/user/.local/lib/python3.9/site-packages/django/db/backends/utils.py", line 82, in _execute
    return self.cursor.execute(sql)
django.db.utils.OperationalError: database "test_mysite" is being accessed by other users
DETAIL:  There is 1 other session using the database.


During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/user/mysite/manage.py", line 22, in 
    main()
  File "/home/user/mysite/manage.py", line 18, in main
    execute_from_command_line(sys.argv)
  File "/home/user/.local/lib/python3.9/site-packages/django/core/management/__init__.py", line 401, in execute_from_command_line
    utility.execute()
  File "/home/user/.local/lib/python3.9/site-packages/django/core/management/__init__.py", line 395, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/user/.local/lib/python3.9/site-packages/django/core/management/commands/test.py", line 23, in run_from_argv
    super().run_from_argv(argv)
  File "/home/user/.local/lib/python3.9/site-packages/django/core/management/base.py", line 330, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/user/.local/lib/python3.9/site-packages/django/core/management/base.py", line 371, in execute
    output = self.handle(*args, **options)
  File "/home/user/.local/lib/python3.9/site-packages/django/core/management/commands/test.py", line 53, in handle
    failures = test_runner.run_tests(test_labels)
  File "/home/user/.local/lib/python3.9/site-packages/django/test/runner.py", line 705, in run_tests
    self.teardown_databases(old_config)
  File "/home/user/.local/lib/python3.9/site-packages/django/test/runner.py", line 645, in teardown_databases
    _teardown_databases(
  File "/home/user/.local/lib/python3.9/site-packages/django/test/utils.py", line 298, in teardown_databases
    connection.creation.destroy_test_db(old_name, verbosity, keepdb)
  File "/home/user/.local/lib/python3.9/site-packages/django/db/backends/base/creation.py", line 277, in destroy_test_db
    self._destroy_test_db(test_database_name, verbosity)
  File "/home/user/.local/lib/python3.9/site-packages/django/db/backends/base/creation.py", line 293, in _destroy_test_db
    cursor.execute("DROP DATABASE %s"
  File "/usr/lib64/python3.9/contextlib.py", line 166, in __exit__
    raise RuntimeError("generator didn't stop after throw()")
RuntimeError: generator didn't stop after throw()
(tmp) [user@localhost mysite]$

It appears that after the test, the thread still has the database connection, so Django cannot remove the test database.

If I use the default SQLite3 database (i.e. disable line 79 and enable line 90), then it works fine. I also tried to use TestCase instead of TransactionTestCase (see line 10 and line 11), but it does not work well:

For PostgreSQL, the thread can no longer see the new entry added to the database (https://stackoverflow.com/a/31652691/7709727)
For SQLite3, I get another error (sqlite3.OperationalError: database table is locked: polls_question)

So my question is, how show I write my test so that the test can run successfully with PostgreSQL as the server. Note that I put the multithreading part into the tests.py for simplicity. Actually it comes from another module that is to be tested.

Update: Complete code: https://gist.github.com/lxylxy123456/5a36719faf32736431f3ee444bf4c17c I am using Django 3.1.7

user_7826824 · Answer

You need to close the database connections when you are through using them.

from django import db
db.connections.close_all()

So in your case you need to terminate the thread and have the thread close the Django DB connections.

user_1396314 · Answer

Close the DB connection inside the target function for the thread. So in your sample code, it looks like this:

def thread1():
    # ...Your code here...
    from django.db import connection
    connection.close()

...

t = threading.Thread(target=thread1)
t.start()
t.join()

How to access Postgresql database from multiple threads in a Django test?

2 Answers