Database "is being accessed by other users" error when using ThreadPoolExecutor with Django

Question

I'm working on a project where we parse a somewhat large file and process each row asynchronously (we make API calls for each row) using ThreadPoolExecutor. This used to be done synchronously and we had a passing test suite. Now, however, when running the tests Django's default test runner errors out in teardown_databases:

Traceback (most recent call last):
  File "manage.py", line 34, in <module>
    execute_from_command_line(sys.argv)
  File "/usr/local/lib/python3.5/site-packages/django/core/management/__init__.py", line 367, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python3.5/site-packages/django/core/management/__init__.py", line 359, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python3.5/site-packages/django/core/management/commands/test.py", line 29, in run_from_argv
    super(Command, self).run_from_argv(argv)
  File "/usr/local/lib/python3.5/site-packages/django/core/management/base.py", line 294, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python3.5/site-packages/django/core/management/base.py", line 345, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python3.5/site-packages/django/core/management/commands/test.py", line 72, in handle
    failures = test_runner.run_tests(test_labels)
  File "/usr/local/lib/python3.5/site-packages/django/test/runner.py", line 551, in run_tests
    self.teardown_databases(old_config)
  File "/usr/local/lib/python3.5/site-packages/django/test/runner.py", line 526, in teardown_databases
    connection.creation.destroy_test_db(old_name, self.verbosity, self.keepdb)
  File "/usr/local/lib/python3.5/site-packages/django/db/backends/base/creation.py", line 264, in destroy_test_db
    self._destroy_test_db(test_database_name, verbosity)
  File "/usr/local/lib/python3.5/site-packages/django/db/backends/base/creation.py", line 283, in _destroy_test_db
    % self.connection.ops.quote_name(test_database_name))
  File "/usr/local/lib/python3.5/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.5/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/usr/local/lib/python3.5/site-packages/django/utils/six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.5/site-packages/django/db/backends/utils.py", line 62, in execute
    return self.cursor.execute(sql)
django.db.utils.OperationalError: database "test_sftpm_db" is being accessed by other users
DETAIL:  There are 10 other sessions using the database.

(We're using 10 workers)

I've tried to close the connections manually in many places in the code but to no avail. Is there any proper way of fixing this?

Answer 1

Make sure you are calling connection.close() in your code when working with threads. This solved the issue for me, where other proposed solutions (decorator for the test methods, changing db settings, DB functions like shown by Nick above, restarting postgres) did not. Useful example

Answer 2

This looks like more Django's issue than Postgres' -- see for example this ticket: https://code.djangoproject.com/ticket/22420

From what you provided, I see that Django didn't close all connections to test database before making attempt to drop it. In this case, Postges tries to protect other sessions from data losses, so it cannot drop/rename the database before they all disconnect.

If you want, you can drop the test database manually, using pg_terminate_backend(..) function and pg_stat_activity view you already used:

select pg_terminate_backend(pid) 
from pg_stat_activity 
where
  datname = 'DATABASE_NAME'
;

drop database DATABASE_NAME;

If, by some reason, somebody is very quick and manages to connect between theese two commands, drop database will fail again. In such case, you can repeat it, but before that revoke rights to connect to this database from public -- this will prevent connections to it:

revoke connect on database DATABASE_NAME from public;

...and then repeat operations described above.

Answer 3

Connections to the database are thread local. I ended up fixing this problem by adding a callback to each future returned by the executor.

from django.db import connections

def on_done(future):
    # Because each thread has a connection, so here you can call close_all() to close all connections under this thread name.
    connections.close_all()

def main():
    # ...
    with ThreadPoolExecutor() as executor:
        while True:
            future = executor.submit(do, get_a_job())
            future.add_done_callback(on_done)

More found here: https://www.programmersought.com/article/3618244269/

Another thing that could be the problem is that you are subclassing TestCase which holds a global lock on your test. SubclassingTransactionTestCase will fix this problem and allow any threads your test may spawn to communicate with the database