postgres connection does not get closed with socket.io requests

[michael-hy]

Hello,
i have the problem that my postgres connections dont get closed. If i set max_connections of my postgres server to 5, then i can reload my page five times successfully. After that postgres complains about to many open connections.

I am using
django gevent socketio with the following versions:

In [2]: socketio.__version__
Out[2]: (0, 3, 5)
In [5]: django.get_version()
Out[5]: '1.5.5'
In [7]: gevent.__version__
Out[7]: '0.13.8'

My debugging sessions so far show that i have the following problem:
if i have a normal request, i get the stack trace like this:

gevent.pywsgi.WSGIHandler.handle()
gevent.pywsgi.WSGIHandler.handle_one_request()
socketio.handler.SocketIOHandler.handle_one_response()
gevent.pywsgi.WSGIHandler.handle_one_response()
gevent.pywsgi.WSGIHandler.run_application()
django.core.handlers.WSGIHandler.__call__()
django.core.handlers.BaseHandler.get_response()
...

and in gevent.pywsgi.WSGIHandler.handle_one_response() there is a call to

result.close()

which closes open database connections. But when i get a socket.io request, the function gevent.pywsgi.WSGIHandler.handle_one_response() does not get called at all. The stack looks like:

greenlet.run
socketio.server.SocketIOServer.handle()
gevent.pywsgi.WSGIHandler.handle_one_request()
socketio.handler.SocketIOHandler.handle_one_response()
  here a new greenlet is spawned by:
            if socket.wsgi_app_greenlet is None:
                # TODO: why don't we spawn a call to handle_one_response here ?
                #       why call directly the WSGI machinery ?
                start_response = lambda status, headers, exc=None: None
                socket.wsgi_app_greenlet = gevent.spawn(self.application,
                                                        self.environ,
                                                        start_response)

and here the new greenlet stack

greenlet.py
django.core.handlers.wsgi.WSGIHandler.__call__()
django.core.handlers.base.BaseHandler.get_response()
...

So where should the database connection be closed in the socket.io case? The distinction between socket.io calls and regular ones seems to happen in:

# socketio.handler.SocketIOHandler.handle_one_response()
...
        if not path.lstrip('/').startswith(self.server.resource):
            return super(SocketIOHandler, self).handle_one_response()
...

my runserver.py looks like this:

#!/usr/bin/env python
# -*- coding: utf8 -*-


from __future__ import division
from __future__ import print_function
from __future__ import absolute_import

from gevent import monkey; monkey.patch_all()
import gevent

import os

os.environ.setdefault("DJANGO_SETTINGS_MODULE", "settings")
import settings

port = settings.DJANGO_PORT

import django.core.handlers.wsgi
application = django.core.handlers.wsgi.WSGIHandler()

from socketio.server import SocketIOServer

def main():
    server = SocketIOServer(
        ("", port),
        application,
        resource="socket.io"
    )
    server.serve_forever()


if __name__ == '__main__':
    main()

I know the information i provided is a little unstructured. I just wanted to post my debugging status so far and hope that someone sees the problem while i am going on to find the bug.

Thanks in advance
Michael

[lgarvey]

Hi Michael,

I've also noticed the same issue. I thought using pgbouncer in transaction mode might have solved the problem, but it does not appear to. I'm keenly awaiting some progress on this issue.

Lyndon

[michael-hy]

But is there some workaround or is this library just not usable at all because of this bug? I mean i really cannot use it like this, since every 100 page reloads i get a server error and need to restart django or postgres. How could anyone try to use it in production?
Shouldn't this issue be high priority?

[PaoloC68]

I'm facing this issue too, any workarounds to suggest? Thanks!

[PaoloC68]

@michael1e23
I am trying to solve this issue and I have decided to give a try to Django 1.6 which has a better db connection management.
Running the application using this approach indeed under 1.6 you'll soon get this message:

DatabaseError: DatabaseWrapper objects created in a thread can only be used in that same thread. The object with alias 'default' was created in thread id 4390603664 and this is thread id 4390601264.

so this means that really Django does it's best to recycle the connection even with gevent patched threads.

At this point is only about enable Django to share the connection between different threads

connections['default'].allow_thread_sharing = True

The final code to run the server should then look like this

#!/usr/bin/env python
from gevent import monkey
from socketio.server import SocketIOServer
import django.core.handlers.wsgi
import os
import sys
from django.db import connections

monkey.patch_all()

########## This should do the trick ###########
connections['default'].allow_thread_sharing = True

try:
    import settings
except ImportError:
    sys.stderr.write("Error: Can't find the file 'settings.py' in the directory containing %r. It appears you've customized things.\nYou'll have to run django-admin.py, passing it your settings module.\n(If the file settings.py does indeed exist, it's causing an ImportError somehow.)\n" % __file__)
    sys.exit(1)

PORT = 9000

os.environ['DJANGO_SETTINGS_MODULE'] = 'settings'

application = django.core.handlers.wsgi.WSGIHandler()

sys.path.insert(0, os.path.join(settings.PROJECT_ROOT, "apps"))

if __name__ == '__main__':
    print 'Listening on http://127.0.0.1:%s and on port 10843 (flash policy server)' % PORT
    SocketIOServer(('', PORT), application, resource="socket.io").serve_forever()

This may be working and solve the issue, I'll test this on my end, can you please do the same and confirm?

@lgarvey Can you please test this too?

@abourget Does it sound crazy or can make some sense?

Thanks!

[dn0]

So where should the database connection be closed in the socket.io case?

I would like to return to the original request of properly closing DB connections, which I think should ideally happen after/during disconnect(). I'm using PgBouncer and psycogreen and it would be nice if each socket.io green thread would have its own DB connection during the thread's lifetime.

Now the allow_thread_sharing = True workaround works, because green threads are run pseudo-concurrently (unless combined with native threading or multiprocessing), right? But by using green psycopg we should be able to wait for the DB IO in parallel.
I'm spawning a custom green thread from gunicorn.post_fork() after setting allow_thread_sharing = True. Since this green thread was doing some DB queries I was able to get a ProgrammingError exception ^{(execute cannot be used while an asynchronous query is underway)}. I think that this could also happen when multiple green socket.io threads would access the DB using the same connection.

Now I'm using another workaround and trying to close the DB connection after calling on_* and recv_* methods:

from django.db import close_old_connections  # Django 1.6

class MyNamespace(BaseNamespace):
    ...
    def exception_handler_decorator(self, fun):
        def wrap(*args, **kwargs):
            try:
                return fun(*args, **kwargs)
            finally:
                close_old_connections()
        return wrap
    ...

And closing DB connections in the view too:

def socketio(request):
    ...
    socketio_manage(request.environ, namespaces={'': MyNamespace}, request=request)
    try:
        return HttpResponse(None)
    finally:
        close_old_connections()

This solution works for me, but it is not a good answer to the original question.

[m4l]

I struggle to make the solution work in production with gunicorn started by supervisord, I fail to make my gunicorn_conf.py see my django settings. It looks like this at the moment (not working):

sys.path.append('/opt/project_root/')
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "project.settings")

monkey.patch_all()
connections['default'].allow_thread_sharing = True

I get:

ImproperlyConfigured: The SECRET_KEY setting must not be empty.

What I also tried was:

from project import settings as project_settings
settings.configure(default_settings=project_settings)

Any help greatly appreciated :-/

[m4l]

Ok, I could fix the IDLE connections building up with this, thanks to @dn0:

from django.db import close_old_connections
class MyNamespace(BaseNamespace):
...

def recv_disconnect(self):
    close_old_connections()

def exception_handler_decorator(self, fun):
    def wrap(*args, **kwargs):
        try:
            return fun(*args, **kwargs)
        finally:
            close_old_connections()
    return wrap