10

I'm running Django 1.3 with PostgreSQL 9.1/PostGIS 1.5, psycopg2 2.4.2 and pgbouncer 1.4.2.

On every single connection to the database I get a log entry in pgbouncer.log:

2011-11-20 02:15:25.027 29538 LOG S-0x96c2200: app_db/postgres@192.168.171.185:5432 closing because: unclean server (age=0).

I can't find any solution to this problem - anybody have an idea why? I've tried reconfiguring pgbouncer (session/transaction mode, different timeouts etc), but to no avail.

Shashank Bharadwaj
  • 411
  • 3
  • 9
Dick
  • 1,228
  • 13
  • 21

1 Answers1

16

Ok, I think I've figured this out. The problem lies with a long-standing issue with Django and Psycopg2. Basically, Psycopg2 will automatically issue a BEGIN statement to the DB. However, if Django thinks no data-modification has occurred, it won't issue a COMMIT at the end of a transaction.

There are a few solutions to this problem, look at http://www.slideshare.net/OReillyOSCON/unbreaking-your-django-application for more details. Ideally you turn off automatic commits (by setting autocommit = True in your DB settings, awkward naming convention). This prevents transactions on read-only functions, but also on write functions so you need to manually wrap those functions in a @commit_on_success decorator.

Alternatively, just add the django.middleware.transaction.TransactionMiddleware to your Middleware classes. This will wrap every request in a transaction. This means also unnecessarily wrapping read-only requests in a transaction, but it's a quick-and-dirty solution.

Dick
  • 1,228
  • 13
  • 21
  • Ok just found out the hard way, WARNING: don't just do this if you use celery (or something else) for background task processing. There will likely be race conditions in which you pass the primary key of an object to a task running async. The task can start running before the request is finished (and the transaction committed), so the task can't find the object. I think the only solution to this is manual transaction management. – Dick Nov 29 '11 at 19:51
  • 1
    Just a note to say thanks for linking to my presentation... I'm glad it was useful! :) – Christophe Feb 14 '12 at 06:47
  • Of course! It was a great presentation; I find it stunning that so little is known about the 'broken' transaction management in Django. – Dick Feb 22 '12 at 22:44
  • 1
    I don't see why you put broken in quotes. Django's transaction management is _broken_. – Andrew Apr 27 '12 at 21:09
  • This is so helpful! Awesome presentation @Cristophe. I immediately implemented your recommendation. One fuzzy thing though: does setting the database autocommit=True affect admin list/edit/create views at all or do they manually manage transactions and unbreak themselves, regardless of whether transaction middleware is enabled or not & autocommit=True? – B Robster Mar 12 '13 at 04:07
  • @Christophe: I have a related question here: http://stackoverflow.com/questions/40821994/pgbouncer-unclean-server-error-in-django-app would be great if you can give your point of view. – Hassan Baig Nov 26 '16 at 19:23