[Django] #34839: OperationalError from Psycopg when using Connection Pool (Digital Ocean) Django 4.2.3 psycopg 3.1.9

8 views

Skip to first unread message

Django

unread,

Sep 13, 2023, 2:58:04 PM9/13/23

to django-...@googlegroups.com

#34839: OperationalError from Psycopg when using Connection Pool (Digital Ocean)
Django 4.2.3 psycopg 3.1.9
-------------------------------------+-------------------------------------
Reporter: Wes | Owner: nobody
Garlock |
Type: Bug | Status: new
Component: Database | Version: 4.2
layer (models, ORM) | Keywords: postgresql,
Severity: Normal | psycopg, ORM
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Hello,

I'm experimenting with Django 4.2 and psycopg v3. I found a strange error
condition while deploying the application to digital ocean using there
postgresql database service and their database pools setup in transaction
mode.

From my understanding after reviewing the docs, the following settings
should be sufficient. This would disable server side cursors, triggering a
new cursor with every request.

{{{
DATABASES = {
'default': {
**dj_database_url.config(default=env('API_DATABASE_URL',
default="")),
"DISABLE_SERVER_SIDE_CURSORS": True,
}
}
}}}

After deploying the app to my staging environment, these settings are
valid for about 2~3 hours then, I start receiving OperationalError
exceptions which are originating in psycopg3.

I extended the settings to the following below hoping the connection
health checks settings would handle the issue, but that setting didn't
resolve the OperationalError.
{{{
DATABASES = {
'default': {
**dj_database_url.config(default=env('API_DATABASE_URL',
default="")),
"DISABLE_SERVER_SIDE_CURSORS": True,
"CONN_HEALTH_CHECKS": True
}
}
}}}

For reference this occurs inside of an async view run on uvicorn with only
one worker.

I created a temporary work around in my staging environment where I
subclass the postgresql backend to handle this OperationalError
specifically. I added some sentry logging so I can detect when this error
is happening. I more or less copied the is_usable method on the
DatabaseWrapper, {{{cursor.execute("SELECT 1")}}}, but don't call it in my
work around because create_cursor calls is_usable down stream. This work
around seems to be working, but it "feels" wrong.

{{{
from django.db.backends.postgresql.base import DatabaseWrapper as
OGDatabaseWrapper
from django.utils.asyncio import async_unsafe
from psycopg.errors import OperationalError
from django.db.backends.signals import connection_created
import sentry_sdk

class DatabaseWrapper(OGDatabaseWrapper):
def get_connection_params(self):
self._conn_params = super().get_connection_params()
return self._conn_params

@async_unsafe
def create_cursor(self, name=None):
try:
cursor = super().create_cursor(name=name)
cursor.execute("SELECT 1")
except OperationalError as e:
sentry_sdk.capture_exception(e)
sentry_sdk.capture_message("Recovery attempt for db connection
made")
self.connection = self.get_new_connection(self._conn_params)
self.set_autocommit(self.settings_dict["AUTOCOMMIT"])
self.init_connection_state()
connection_created.send(sender=self.__class__, connection=self)
self.run_on_commit = []
cursor = super().create_cursor(name=name)
return cursor
}}}

The specific psycogp v3 error captured by sentry is
OperationalError('consuming input failed: EOF detected').

I was curious if this has been reported by anyone else, or if I'm missing
some piece of the puzzle. I can try to create a repo to reproduce this
error if the Django team think's it's worth investigating deeper.

Best,
Wes

--
Ticket URL: <https://code.djangoproject.com/ticket/34839>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Sep 13, 2023, 11:22:35 PM9/13/23

to django-...@googlegroups.com

#34839: OperationalError from Psycopg when using Connection Pool (Digital Ocean)
Django 4.2.3 psycopg 3.1.9
-------------------------------------+-------------------------------------

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* status: new => closed
* resolution: => duplicate

Comment:

Thanks for this ticket. It's hard to say without a sample project, but
this is probably a duplicate of #24810 (check out also
[https://code.djangoproject.com/ticket/33297#comment:1 comment]).