Django OperationalError: the connection is closed

[nicokant]

I have a django application using procrastinate with the following dependencies:

python 3.11

psycopg[binary]==3.1.19
django==5.0.6
procrastinate[django]==2.9.1

When I start the worker it works fine, but after some time I start getting this error

Logs:

INFO 2024-07-30 08:42:38,237 worker 21 139709328389952 Job import_dataset[25](id='35') ended with status: Error, to retry, lasted 0.001 s
Traceback (most recent call last):
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/base/base.py", line 294, in _cursor
    return self._prepare_cursor(self.create_cursor(name))
                                ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/postgresql/base.py", line 332, in create_cursor
    cursor = self.connection.cursor()
             ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/psycopg/connection.py", line 852, in cursor
    self._check_connection_ok()
  File "/usr/local/lib/python3.11/site-packages/psycopg/connection.py", line 485, in _check_connection_ok
    raise e.OperationalError("the connection is closed")
psycopg.OperationalError: the connection is closed

The above exception was the direct cause of the following exception:
Traceback (most recent call last):
 File "/usr/local/lib/python3.11/site-packages/procrastinate/worker.py", line 281, in run_job
    task_result = await await_func(*job_args, **job.task_kwargs)
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/procrastinate/utils.py", line 108, in sync_to_async
    return await sync.sync_to_async(func)(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/asgiref/sync.py", line 468, in __call__
    ret = await asyncio.shield(exec_coro)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/concurrent/futures/thread.py", line 58, in run
    result = self.fn(*self.args, **self.kwargs)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/asgiref/sync.py", line 522, in thread_handler
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/app/viltkamera_admin/wild_cameras/tasks.py", line 237, in import_dataset
    dataset = models.Dataset.objects.get(id=id)
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/models/manager.py", line 87, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/models/query.py", line 645, in get
    num = len(clone)
          ^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/models/query.py", line 382, in __len__
    self._fetch_all()
  File "/usr/local/lib/python3.11/site-packages/django/db/models/query.py", line 1928, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
                         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/models/query.py", line 91, in __iter__
    results = compiler.execute_sql(
              ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/models/sql/compiler.py", line 1560, in execute_sql
    cursor = self.connection.cursor()
             ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/base/base.py", line 316, in cursor
    return self._cursor()
           ^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/base/base.py", line 293, in _cursor
    with self.wrap_database_errors:
  File "/usr/local/lib/python3.11/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/base/base.py", line 294, in _cursor
    return self._prepare_cursor(self.create_cursor(name))
                                ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/postgresql/base.py", line 332, in create_cursor
    cursor = self.connection.cursor()
             ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/psycopg/connection.py", line 852, in cursor
    self._check_connection_ok()
  File "/usr/local/lib/python3.11/site-packages/psycopg/connection.py", line 485, in _check_connection_ok
    raise e.OperationalError("the connection is closed")
django.db.utils.OperationalError: the connection is closed

I'm running the worker using the following command:

python manage.py procrastinate worker -n import -q import

[medihack]

I had something similar in a long-running task. After some time, the Django database connection times out, and that error occurs. I worked around this issue by calling django.db.close_old_connections() in code locations I knew when no database requests were expected for a longer time. That closes the connections explicitly, and Django will reopen them when needed. Does this help in your case, too?

[nicokant]

Thank you for the advice, I'll try using the same approach.
I'll also try to change the CONN_MAX_AGE from 0 to None inside the queue to make sure the connection is persistent

[ewjoachim]

@paulzakin recently asked something on CONN_MAX_AGE, maybe you both have the same issue?

[medihack]

@nicokant Is the problem solved by django.db.close_old_connections() or CONN_MAX_AGE? If so, I will add a note to our official FAQ and close the issue.

[paulzakin]

We switched to using the database pool introduced in Django 5.1 - which seems to work well. Worth a shot for anyone else

[stephane]

The release 2.13.0 (until 2.13.2) doesn't work anymore with the new connection pool of Django 5.1.
My test suite passes with 2.12.0.

connection = self.pool.getconn()

raises a PoolTimeout.

[medihack]

@stephane Since 2.13.0, we run a sync task in its own thread (otherwise, sync tasks would block each other in concurrency mode as all run in the main thread). Where do you initialize the pool? Do you use the Django command ./manage.py procrastinate worker to start the worker? I wonder if we need some hook in the worker to help with this. I am not using the new pool feature yet, but maybe we can work something out together.
We are also considering switching sync tasks to separate processes (#1161) for a version 3 release, but every sync task would need its own connection pool (and there are other pitfalls, too).

[spapas]

Hello friends! Unfortunately I also stumbled upon this error. :(

I have added

         "OPTIONS": {
            "pool": True,
        },

to my django database connection but I still get this error! It seems that when I start the worker it is fine but after some time (haven't tested how much time) it breaks and gives me error:

Nov 28 09:44:46 isktheea2 python[1180272]: django.db.utils.OperationalError: consuming input failed: server closed the connection unexpectedly
Nov 28 09:44:46 isktheea2 python[1180272]:         This probably means the server terminated abnormally
Nov 28 09:44:46 isktheea2 python[1180272]:         before or while processing the request.

Is there a way to fix / improve this ? If not, is there a way to check if the connection is closed and re-open it ?

TIA