momoko doesn't reconnect

[yuzvir]

I use tornado (4.2.1) + momoko (2.2.1) + psycopg2 (2.6.1) for small web application and it works ok until PostgreSQL server close connection. Then after every db.execute() command I receive an OperationalError message:

Traceback (most recent call last):
      File "C:\Python27\lib\site-packages\tornado-4.2.1-py2.7-win32.egg\tornado\web.py", line 1415, in _execute
        result = yield result
      File "C:\Python27\lib\site-packages\tornado-4.2.1-py2.7-win32.egg\tornado\gen.py", line 870, in run
        value = future.result()
      File "C:\Python27\lib\site-packages\tornado-4.2.1-py2.7-win32.egg\tornado\concurrent.py", line 215, in result
        raise_exc_info(self._exc_info)
      File "C:\Python27\lib\site-packages\tornado-4.2.1-py2.7-win32.egg\tornado\gen.py", line 876, in run
        yielded = self.gen.throw(*exc_info)
      File "server.py", line 63, in get
        cursor = yield self.db.execute(query)
      File "C:\Python27\lib\site-packages\tornado-4.2.1-py2.7-win32.egg\tornado\gen.py", line 870, in run
        value = future.result()
      File "C:\Python27\lib\site-packages\tornado-4.2.1-py2.7-win32.egg\tornado\concurrent.py", line 215, in result
        raise_exc_info(self._exc_info)
      File "D:\work\program-stat\momoko\connection.py", line 453, in when_available
        future_or_result = method(conn, *args, **kwargs)
      File "D:\work\program-stat\momoko\connection.py", line 743, in execute
        cursor.execute(operation, parameters)
      File "C:\Python27\lib\site-packages\psycopg2\extras.py", line 288, in execute
        return super(NamedTupleCursor, self).execute(query, vars)
    OperationalError: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

Here is an app code:

import os
import tornado.httpserver
import tornado.ioloop
import tornado.options
import tornado.web
import momoko

from tornadotools.route import Route
from psycopg2.extras import NamedTupleCursor

import environments as env

tornado.options.define("port", default=9999, help="run on the given port", type=int)
tornado.options.define("pgsql_host", default=env.DB_HOST, help="database host")
tornado.options.define("pgsql_database", default=env.DB_DATABASE, help="database name")
tornado.options.define("pgsql_user", default=env.DB_LOGIN, help="database user")
tornado.options.define("pgsql_password", default=env.DB_PASSWORD, help="database password")

class Application(tornado.web.Application):
    def __init__(self):
        handlers = Route.routes()
        settings = dict(
            template_path=os.path.join(os.path.dirname(__file__), "templates"),
            static_path=os.path.join(os.path.dirname(__file__), "static"),
            debug=True,
        )
        tornado.web.Application.__init__(self, handlers, **settings)

        ioloop = tornado.ioloop.IOLoop.instance()
        self.db = momoko.Pool(
            dsn='dbname=%s user=%s password=%s '
                'host=%s port=5432' % (
                    tornado.options.options.pgsql_database,
                    tornado.options.options.pgsql_user,
                    tornado.options.options.pgsql_password,
                    tornado.options.options.pgsql_host),
            cursor_factory=NamedTupleCursor,
            size=1,
            ioloop=ioloop,
        )
        future = self.db.connect()
        ioloop.add_future(future, lambda f: ioloop.stop())
        ioloop.start()

class BaseHandler(tornado.web.RequestHandler):
    @property
    def db(self):
        return self.application.db

@Route(r"/")
class HomeHandler(BaseHandler):
    def get(self):
        self.write("<ul>")
        self.write("<li><a href='/test'>Test page</a>")
        self.write("</ul>")
        self.finish()

@Route(r"/test")
class TestHandler(BaseHandler):

    @tornado.web.asynchronous
    @tornado.gen.coroutine
    def get(self):
        cursor = yield self.db.execute("SELECT %s as t;", (1,), cursor_factory=None)
        self.render("test.html", result=cursor.fetchall())

def main():
    tornado.options.parse_command_line()
    http_server = tornado.httpserver.HTTPServer(Application())
    http_server.listen(tornado.options.options.port)
    tornado.ioloop.IOLoop.instance().start()

if __name__ == "__main__":
    main()

I just can't understand where is a bug in momoko or in my app. How could I handle this exception and reconnect to db automatically without restarting app?

[haizaar]

Are you running on mac by any chance?

[yuzvir]

No, app is running on windows 8 and PostgreSQL is on Linux

[haizaar]

What is your usage scenario exactly? Do you

• Run your program
• Hit /test successfully
• Restart Postgres server (and wait until the restart completes)
• Hit /test again and get the error?

[yuzvir]

Yes, but actually I don't know what exactly is happening on postgres server side. For some reason it closes connection after some hours. So my scenario:

• Run my program
• Hit /test successfully for several times
• Postgres server closes connection for unknown reason
• Hit /test again and get the error
• Restart program
• Hit /test successfully again

[haizaar]

To clarify, are you saying always after restarting the program it starts working again?

[yuzvir]

Yes.

[haizaar]

Please try your example on Linux. I would like to understand whether it's OS specific issue and I don't have access to Windows environment.

Also please remove all of the code that is not essential to problem reproduction - like using tornadotools, environments and mysterious test.html file.

[yuzvir]

Ok, I've found that problem occurs on windows after computer went to sleep mode. Also I've found that this code helps to reconnect.

@Route(r"/test2")
class TestHandler2(BaseHandler):

    @tornado.gen.coroutine
    def get(self):
        try:
            cursor = yield self.db.execute("SELECT 1 as t;", cursor_factory=None)
            self.write("SQL: %s<br>" % (repr(cursor.fetchall()),))
        except Exception as error:
            self.write(str(error))
            try:
                self.db.connect()
            except Exception as error:
                self.write(str(error))
        self.finish()

[haizaar]

Great. If I were you I would submit a bug report to psycopg2.