Django, Ajax long polling, Postgresql: idle transaction

Question

I implemented a chat, using ajax long polling and Gevent. To read, the client ajax the update view and wait with Gevent.event.wait for an update.

Problem: The Postgresql transaction opened by Django at the beginning of a request (to get session information) isn't closed until the end of the request. And those idle transactions take a lot of memory.

What would be the cleanest way to close the Postgresql transaction without closing the request ? I'm currently sending the request_finished signal manually but it feels like a hack.

Palmette

Answer 1

The way you're doing it is probably the the best way within the framework of your hack anyway. Is there any reason you're trying to shoe-horn long-poll into the request-response process instead of using something like django-socketio?

Sandy

We lost a long time trying to make socketio work through nginx (front end) with gevent/gunicorn/apache (back end). Nginx isn't able to do that without a high quantity of mods. And even with those, we weren't able to link the socketio user id with the django session id, so we weren't able to get user information. If you have a complete tutorial to recommend, we would love to see it. Most of the socketio - chat tutorial we found, don't use django user information or a front end. – Palmette

As far as making SocketIO and django auth backends work together: gist.github.com/fd8e9631368e447de702 – Antaeus

To be honest, we won't rollback right now, but we will definitively keep that for later. Thank you. – Palmette

Answer 2

See here: https://docs.djangoproject.com/en/dev/topics/db/transactions/#django.db.transaction.commit_manually

@transaction.commit_manually
def yourview(request):
    # do your db actions
    transaction.commit()

Or if you prefer context managers:

def yourview(request):
    ...
    with transaction.commit_manually():
         # do your db actions
    ...

Also if you're having memory issues holding PostgreSQL connections open you should look a pooling solution using pgbouncer or the various gevent connection pools that exist. You should see some sizable performance gains from doing this.

Antaeus

We tried with rollback, we'll make a test with commit and validate the answer if it works. And have a look to the technos you recommended. Thanks for the answer! – Palmette

It's not working for us. I guess the transaction opened inside the commit_manually and the one opened previously by django aren't the same (or there is something we don't understand). We still have the idle connection when we use this technic instead of ours (ugly) hack. – Palmette