Django-ORM DB connections refresh before each query

Question

One of my python modules(which is not directly associated with Django application, same project though) is supposed to use Django models(that belong to original Django application) for convenient DB queries via Django-ORM. However, this only works well until there is a DB or site-to-site connection issue.

Reproduced 100% times with following steps:

1. Create models.
2. Import models by setting up Django in any python module

 os.environ['DJANGO_SETTINGS_MODULE'] = 'ServiceName.settings'
 django.setup()
 from ServiceName.models import ModelName

3. Use models via django ORM to fetch some data from DB on service startup(lets call it step 1) and later on by some triggers(lets call it step 2). In my case data is fetched once on startup, and all the subsequent times on certain Kafka event. e.g.

query_results = ModelName.objects.filter(your_filter=some_value)

4.Shutdown PostgreSQL instance after step one, but before step 2 Not sure if related to the issue, but we are using pg-bouncer as a connection point.

5.Start PostgreSQL, trigger step2.

First attemp to fetch the data fails with the following trace:

Traceback (most recent call last):
File "/opt/services/_init_.py", line 113, in observe_events
if callback(event):
File "services_logparser_core.py", line 193, in process_event
r_handler.update_rules()
File "services_logparser_core.py", line 48, in update_rules
self.rules_list = self._fetch_enabled_rules()
File "services_logparser_core.py", line 69, in _fetch_enabled_rules
for sr in enabled_search_rules:
File "/usr/local/lib/python3.8/dist-packages/django/db/models/query.py", line 280, in _iter_
self._fetch_all()
File "/usr/local/lib/python3.8/dist-packages/django/db/models/query.py", line 1324, in _fetch_all
self._result_cache = list(self._iterable_class(self))
File "/usr/local/lib/python3.8/dist-packages/django/db/models/query.py", line 51, in _iter_
results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
File "/usr/local/lib/python3.8/dist-packages/django/db/models/sql/compiler.py", line 1175, in execute_sql
cursor.execute(sql, params)
File "/usr/local/lib/python3.8/dist-packages/django/db/backends/utils.py", line 66, in execute
return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
File "/usr/local/lib/python3.8/dist-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
return executor(sql, params, many, context)
File "/usr/local/lib/python3.8/dist-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
File "/usr/local/lib/python3.8/dist-packages/django/db/utils.py", line 90, in _exit_
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/usr/local/lib/python3.8/dist-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
django.db.utils.OperationalError: SSL SYSCALL error: EOF detected

All the subsequent attempts to fetch the data fail with:

Traceback (most recent call last):
  File "/opt/services/__init__.py", line 113, in observe_events
    if callback(event):
  File "services_logparser_core.py", line 193, in process_event
    r_handler.update_rules()
  File "services_logparser_core.py", line 48, in update_rules
    self.rules_list = self._fetch_enabled_rules()
  File "services_logparser_core.py", line 69, in _fetch_enabled_rules
    for sr in enabled_search_rules:
  File "/usr/local/lib/python3.8/dist-packages/django/db/models/query.py", line 280, in __iter__
    self._fetch_all()
  File "/usr/local/lib/python3.8/dist-packages/django/db/models/query.py", line 1324, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/usr/local/lib/python3.8/dist-packages/django/db/models/query.py", line 51, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/usr/local/lib/python3.8/dist-packages/django/db/models/sql/compiler.py", line 1173, in execute_sql
    cursor = self.connection.cursor()
  File "/usr/local/lib/python3.8/dist-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
  File "/usr/local/lib/python3.8/dist-packages/django/db/backends/base/base.py", line 259, in cursor
    return self._cursor()
  File "/usr/local/lib/python3.8/dist-packages/django/db/backends/base/base.py", line 237, in _cursor
    return self._prepare_cursor(self.create_cursor(name))
  File "/usr/local/lib/python3.8/dist-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.8/dist-packages/django/db/backends/base/base.py", line 237, in _cursor
    return self._prepare_cursor(self.create_cursor(name))
  File "/usr/local/lib/python3.8/dist-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
  File "/usr/local/lib/python3.8/dist-packages/django/db/backends/postgresql/base.py", line 236, in create_cursor
    cursor = self.connection.cursor()
django.db.utils.InterfaceError: connection already closed

Application restart fixes the problem.

I don't set up a connection to the db manually anywhere(I only setup DB credentials), all the connections are handled by Django ORM. Is there any django/psycopg setting can be configured inside the project so that new connection is established each time db query is required?

Django==3.2.6

Answer 1

from django import db

And

db.close_old_connections()

before

query_results = ModelName.objects.filter(your_filter=some_value)

Did the trick. close_old_connections() method does not just close old connections, but also check if current connection is still usable. In case connection returns an error it will be closed. New connection opens automatically.