django.db.utils.InterfaceError: connection already closed when updating from django 3.0 to 3.1

Using Django Deployment
21

Well my previous attempt to utilize CONN_HEALTH_CHECKS also gave no fruits… Why this must be so complicated?

However I god deeper and deeper in to a rabbit hole… in fact it’s quiet easy to debug this… you start your long running process and then you restart your postgres server, which in result causes this exception. So by doing that I was able to finally figure out how to fight this back. My solution is custom database backend, whyou you define in your DATABASE settings.

from django.contrib.gis.db.backends.postgis.base import (
    DatabaseWrapper as PostGisPsycopg2DatabaseWrapper
)
from django.db import close_old_connections, connection as db_connection

class DatabaseWrapper(PostGisPsycopg2DatabaseWrapper):

    @async_unsafe
    def create_cursor(self, name=None):
        if not self.is_usable():
            close_old_connections()
            db_connection.connect()
        return super().create_cursor(name=name)

Apparently there is .is_usable() check in standard Postgresql database wrapper… which happens to be not used anywhere… Well done! :slight_smile:

I am not sure if close_old_connections() is necessary here, but I do it anyway if the cursor is no longer usable.

Now this is really well tested and works great! It however does cursor.execute("SELECT 1") to check if database cursor is usable… so it’s adding a little bit of an overhead, but its super marginal and you won’t notice any difference in real life performance unless you are making hundreds of db requests at a very fast pace.

2 Likes
22

If you want a solution that doesn’t have the overhead mentioned in @Simanas solution (great solution btw - set me on the right track) there’s another workaround:

import django.db
from django.db.backends.postgresql.base import (
    DatabaseWrapper as BuiltinPostgresDatabaseWrapper,
)
from psycopg2 import InterfaceError


class DatabaseWrapper(BuiltinPostgresDatabaseWrapper):
    def create_cursor(self, name=None):
        try:
            return super().create_cursor(name=name)
        except InterfaceError:
            django.db.close_old_connections()
            django.db.connection.connect()
            return super().create_cursor(name=name)

This will handle when the connection is broken without having to run an extra query to the DB!

Thanks to everyone on this thread, it was really hard for me to get to a good solution without the help available on here

1 Like
23

@benlee1284 thank you for your solution!
Not only it removes the overhead, it also solves the issue when using such a backend with concurrent inserts with update_or_create methods!

25

I’ve had same issue using processes started from a custom manage.py command. Thank u very much @Simanas for insist and persist Jaja… I really appreaciate that you share this workaround.