#33297: ASGI: Dead persistent postgres connections are not closed sometimes
-------------------------------+--------------------------------------
Reporter: Matt Davis | Owner: nobody
Type: Bug | Status: closed
Component: Uncategorized | Version: 3.2
Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------+--------------------------------------
Comment (by Azzonith):
Hi there,
Can confirm issue exists.
Reproduced 100% times on the 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
}}}
6. Application restart fixes the problem as it was previously stated.
Is there any django/psycopg setting can be configured inside the project
so that new connection is established each time db query is required?
--
Ticket URL: <https://code.djangoproject.com/ticket/33297#comment:4>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/0107017ffa02c1e8-d56aa9db-72bc-4bb2-a2ad-3b18fd7f1cf3-000000%40eu-central-1.amazonses.com.
