id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
26935	DB connections stuck in closed state when using Django ORM in daemon	Mike Charnoky	nobody	"'''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()
}}}





"	Bug	closed	Database layer (models, ORM)	1.9	Normal	duplicate			Unreviewed	0	0	0	0	0	0