Random OperationalError when using FastCGI (+ possible solutions)

[Henrique C. Alves]

Original discussions:

​http://stackoverflow.com/questions/393637/django-fastcgi-randomly-raising-operationalerror/
​http://stackoverflow.com/questions/1573579/psycopg2-disconnects-from-server

---

Possible solution: ​http://groups.google.com/group/django-users/browse_thread/thread/2c7421cdb9b99e48

Until recently I was curious to test this on Django 1.1.1. Will this 
exception be thrown again... surprise, there it was again. 
It took me some time to debug this, helpful hint was that it only 
shows when (pre)forking. 
So for those who getting randomly those exceptions, I can say... fix 
your code :) 
Ok.. seriously, there are always few ways of doing this, so let me 
firs explain where is a problem first. 
If you access database when any of your modules will import as, e.g. 
reading configuration from database then you will get this error. 
When your fastcgi-prefork application starts, first it imports all 
modules, and only after this forks children. 
If you have established db connection during import all children 
processes will have an exact copy of that object. 
This connection is being closed at the end of request phase 
(request_finished signal). 
So first child which will be called to process your request, will 
close this connection. 
But what will happen to the rest of the child processes? 
They will believe that they have open and presumably working 
connection to the db, so any db operation will cause an exception.

Unfortunately, the recommended way to fix this is unacceptable:

Other option, in my opinion quite clean, is to write somewhere in your 
application small piece of code: 
from django.db import connection 
from django.core import signals 
def close_connection(**kwargs): 
    connection.close() 
signals.request_started.connect(close_connection) 

Connecting twice on each request is a major impact on performance, and also makes the use of initialization SQL impossible.

---

While trying to find a solution for this, I expected to overcome the problem using connection pooling (pgbouncer in this case), so connections from PostgreSQL are pooled and permanent, and pgbouncer being able to handle the quick connect/disconnect from the FastCGI daemons. The problem is that then I stumbled on another weird behaviour from Django + psycopg2.

Apparently, not much users complain about this because they just deploy with Apache + mod_wsgi. Unfortunately, those bugs are rendering the use of Django with FastCGI + Postgres in production inviable.

Original discussion: ​http://stackoverflow.com/questions/393637/django-fastcgi-randomly-raising-operationalerror/2503925#2503925

---

Possible solution: using connection pooling (pgpool, pgbouncer), so you have DB connections pooled and stable, and handed fast to your FCGI daemons.
The problem is that this triggers another bug, psycopg2 raising an *InterfaceError* because it's trying to disconnect twice (pgbouncer already handled this).

Now the culprit is Django signal *request_finished* triggering *connection.close()*, and failing loud even if it was already disconnected. I don't think this behavior is desired, as if the request already finished, we don't care about the DB connection anymore. A patch for correcting this should be simple.

The relevant traceback:

/usr/local/lib/python2.6/dist-packages/Django-1.1.1-py2.6.egg/django/core/handlers/wsgi.py in call(self=<django.core.handlers.wsgi.WSGIHandler object at 0x24fb210>, environ={'AUTH_TYPE': 'Basic', 'DOCUMENT_ROOT': '/storage/test', 'GATEWAY_INTERFACE': 'CGI/1.1', 'HTTPS': 'off', 'HTTP_ACCEPT': 'application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,*/*;q=0.5', 'HTTP_ACCEPT_ENCODING': 'gzip, deflate', 'HTTP_AUTHORIZATION': 'Basic dGVzdGU6c3VjZXNzbw==', 'HTTP_CONNECTION': 'keep-alive', 'HTTP_COOKIE': 'utma=175602209.1371964931.1269354495.126938948...none); sessionid=a1990f0d8d32c78a285489586c510e8c', 'HTTP_HOST': 'www.rede-colibri.com', ...}, start_response=<function start_response at 0x24f87d0>)

246 response = self.apply_response_fixes(request, response)
247 finally:
248 signals.request_finished.send(sender=self.class)
249
250 try:

global signals = <module 'django.core.signals' from '/usr/local/l.../Django-1.1.1-py2.6.egg/django/core/signals.pyc'>, signals.request_finished = <django.dispatch.dispatcher.Signal object at 0x1975710>, signals.request_finished.send = <bound method Signal.send of <django.dispatch.dispatcher.Signal object at 0x1975710>>, sender undefined, self = <django.core.handlers.wsgi.WSGIHandler object at 0x24fb210>, self.class = <class 'django.core.handlers.wsgi.WSGIHandler'>

/usr/local/lib/python2.6/dist-packages/Django-1.1.1-py2.6.egg/django/dispatch/dispatcher.py in send(self=<django.dispatch.dispatcher.Signal object at 0x1975710>, sender=<class 'django.core.handlers.wsgi.WSGIHandler'>, named={})

164
165 for receiver in self._live_receivers(_make_id(sender)):
166 response = receiver(signal=self, sender=sender, named)
167 responses.append((receiver, response))
168 return responses

response undefined, receiver = <function close_connection at 0x197b050>, signal undefined, self = <django.dispatch.dispatcher.Signal object at 0x1975710>, sender = <class 'django.core.handlers.wsgi.WSGIHandler'>, named = {}

/usr/local/lib/python2.6/dist-packages/Django-1.1.1-py2.6.egg/django/db/init.py in close_connection(kwargs={'sender': <class 'django.core.handlers.wsgi.WSGIHandler'>, 'signal': <django.dispatch.dispatcher.Signal object at 0x1975710>})

63 # when a Django request is finished.
64 def close_connection(kwargs):
65 connection.close()
66 signals.request_finished.connect(close_connection)
67

global connection = <django.db.backends.postgresql_psycopg2.base.DatabaseWrapper object at 0x17b14c8>, connection.close = <bound method DatabaseWrapper.close of <django.d...ycopg2.base.DatabaseWrapper object at 0x17b14c8>>

/usr/local/lib/python2.6/dist-packages/Django-1.1.1-py2.6.egg/django/db/backends/init.py in close(self=<django.db.backends.postgresql_psycopg2.base.DatabaseWrapper object at 0x17b14c8>)

74 def close(self):
75 if self.connection is not None:
76 self.connection.close()
77 self.connection = None
78

self = <django.db.backends.postgresql_psycopg2.base.DatabaseWrapper object at 0x17b14c8>, self.connection = <connection object at 0x1f80870; dsn: 'dbname=co...st=127.0.0.1 port=6432 user=postgres', closed: 2>, self.connection.close = <built-in method close of psycopg2._psycopg.connection object at 0x1f80870>

Exception handling here could add more leniency:

/usr/local/lib/python2.6/dist-packages/Django-1.1.1-py2.6.egg/django/db/init.py

63 # when a Django request is finished.
64 def close_connection(kwargs):
65 connection.close()
66 signals.request_finished.connect(close_connection)

Or it could be handled better on psycopg2, so to not throw fatal errors if all we're trying to do is disconnect and it already is:

/usr/local/lib/python2.6/dist-packages/Django-1.1.1-py2.6.egg/django/db/backends/init.py

74 def close(self):
75 if self.connection is not None:
76 self.connection.close()
77 self.connection = None

Other than that, I'm short on ideas.

---

So, while I'm not sure the behaviour is caused by Django itself, it's unexpected to have such different behaviour when using FastCGI prefork + Postgres. A possible solution could be fixing the way prefork is done (which I believe is hard), or at least fixing the bugs related to using pooling, and recommending deploying with pooling in this particular setup, so people have a working solution.

[Russell Keith-Magee]

Duplicate of #13214