DB connections stuck in closed state when using Django ORM in daemon

[Mike Charnoky]

Situation:

1. A long-running daemon process is using the Django ORM
2. The underlying database gets restarted, causing existing db connection(s) to become invalid

Problem:
A subsequent db read using the ORM fail with the exception:

  File "main.py", line 22, in <module>
    testread()
  File "main.py", line 14, in testread
    user = User.objects.all()[0]
  File "/home/noky/Downloads/django-standalone/venv/local/lib/python2.7/site-packages/django/db/models/query.py", line 297, in __getitem__
    return list(qs)[0]
  File "/home/noky/Downloads/django-standalone/venv/local/lib/python2.7/site-packages/django/db/models/query.py", line 258, in __iter__
    self._fetch_all()
  File "/home/noky/Downloads/django-standalone/venv/local/lib/python2.7/site-packages/django/db/models/query.py", line 1074, in _fetch_all
    self._result_cache = list(self.iterator())
  File "/home/noky/Downloads/django-standalone/venv/local/lib/python2.7/site-packages/django/db/models/query.py", line 52, in __iter__
    results = compiler.execute_sql()
  File "/home/noky/Downloads/django-standalone/venv/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 848, in execute_sql
    cursor.execute(sql, params)
  File "/home/noky/Downloads/django-standalone/venv/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/home/noky/Downloads/django-standalone/venv/local/lib/python2.7/site-packages/django/db/utils.py", line 95, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/home/noky/Downloads/django-standalone/venv/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
OperationalError: terminating connection due to administrator command
server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.

Further subsequent db reads using the ORM yield a different (yet consistent) exception:

  File "main.py", line 22, in <module>
    testread()
  File "main.py", line 14, in testread
    user = User.objects.all()[0]
  File "/home/noky/Downloads/django-standalone/venv/local/lib/python2.7/site-packages/django/db/models/query.py", line 297, in __getitem__
    return list(qs)[0]
  File "/home/noky/Downloads/django-standalone/venv/local/lib/python2.7/site-packages/django/db/models/query.py", line 258, in __iter__
    self._fetch_all()
  File "/home/noky/Downloads/django-standalone/venv/local/lib/python2.7/site-packages/django/db/models/query.py", line 1074, in _fetch_all
    self._result_cache = list(self.iterator())
  File "/home/noky/Downloads/django-standalone/venv/local/lib/python2.7/site-packages/django/db/models/query.py", line 52, in __iter__
    results = compiler.execute_sql()
  File "/home/noky/Downloads/django-standalone/venv/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 846, in execute_sql
    cursor = self.connection.cursor()
  File "/home/noky/Downloads/django-standalone/venv/local/lib/python2.7/site-packages/django/db/backends/base/base.py", line 233, in cursor
    cursor = self.make_cursor(self._cursor())
  File "/home/noky/Downloads/django-standalone/venv/local/lib/python2.7/site-packages/django/db/backends/base/base.py", line 206, in _cursor
    return self.create_cursor()
  File "/home/noky/Downloads/django-standalone/venv/local/lib/python2.7/site-packages/django/db/utils.py", line 95, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/home/noky/Downloads/django-standalone/venv/local/lib/python2.7/site-packages/django/db/backends/base/base.py", line 206, in _cursor
    return self.create_cursor()
  File "/home/noky/Downloads/django-standalone/venv/local/lib/python2.7/site-packages/django/db/backends/postgresql/base.py", line 210, in create_cursor
    cursor = self.connection.cursor()
InterfaceError: connection already closed

Environment:

• Ubuntu 14.04
• PostgreSQL 9.5
• Django 1.9.8
• psycopg2 2.6.2

To reproduce problem:

• Download attached project files and untar
• Create a PostgreSQL database testdb
• Edit settings.py to setup db settings
• Run setup.sh to create virtualenv, install Django and run the "daemon"
• The daemon simply writes a record to the db, then constantly queries the db using the Django ORM. Every 5 seconds, a db query is made and the result is printed
• Stop PostgreSQL and restart it: sudo service postgresql restart
• The daemon application will fail to reconnect to the database and the above exceptions will be output
• It is interesting to note that if you modify the daemon process to write to the db in it's main loop, the problem does not manifest (uncomment the testwrite() line in main.py to see this behavior).

Workaround:
I have come up with the following patch to workaround the problem, though it is PostgreSQL specific (it makes use of the connection.closed attribute unique to psycopg). Not sure of a general fix for this problem.

--- django/db/backends/base/base.py.orig	2016-07-22 15:30:36.655291625 -0400
+++ django/db/backends/base/base.py	2016-07-22 15:37:03.419337590 -0400
@@ -197,6 +197,9 @@
         if self.connection is None:
             with self.wrap_database_errors:
                 self.connect()
+        elif self.connection.closed:
+            with self.wrap_database_errors:
+                self.connect()

[Mike Charnoky]

barebones django "daemon" project that reproduces problem

[Tim Graham]

I think this is a duplicate of #24810. Can you confirm?

[Aymeric Augustin]

As usual when this topic comes up, the patch throws transactional integrity to the wind, which isn't acceptable.