OperationalError from Psycopg when using Connection Pool (Digital Ocean) Django 4.2.3 psycopg 3.1.9

[Wes Garlock]

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

[Mariusz Felisiak]

Thanks for this ticket. It's hard to say without a sample project, but this is probably a duplicate of #24810 (check out also comment).