2

I'm using a Python script that does certain flow control on our outgoing mail messages, mostly checking whether a user is sending spam.

The script establishes a persistent connection with a database via a SQLObject. Under certain circumstances, the connection is dropped by a third-party (e.g. our firewall closes the connection due to excess idle), and the SQLObject doesn't notice it has been closed and it continues sending queries on a dead TCP handler, resulting in log entries like these:

Feb 06 06:56:07 mailsrv2 flow: ERROR Processing request error: [Failure instance: Traceback: <class 'psycopg2.InterfaceError'>: connection already closed#012/usr/lib/python2.7/threading.py:524:__bootstrap#012/usr/lib/python2.7/threading.py:551:__bootstrap_inner#012/usr/lib/python2.7/threading.py:504:run#012--- <exception caught here>---#012
/opt/scripts/virtualenv/local/lib/python2.7/site-packages/twisted/python/threadpool.py:191:_worker#012
/opt/scripts/virtualenv/local/lib/python2.7/site-packages/twisted/python/context.py:118:callWithContext#012
/opt/scripts/virtualenv/local/lib/python2.7/site-packages/twisted/python/context.py:81:callWithContext#012
/opt/scripts/flow/server.py:91:check#012
/opt/scripts/flow/flow.py:252:check#012
/opt/scripts/flow/flow.py:155:append_to_log#012
/opt/scripts/virtualenv/local/lib/python2.7/site-packages/SQLObject-1.3.1-py2.7.egg/sqlobject/main.py:1226:__init__#012
/opt/scripts/virtualenv/local/lib/python2.7/site-packages/SQLObject-1.3.1-py2.7.egg/sqlobject/main.py:1274:_create#012
/opt/scripts/virtualenv/local/lib/python2.7/site-packages/SQLObject-1.3.1-py2.7.egg/sqlobject/main.py:1298:_SO_finishCreate#012
/opt/scripts/virtualenv/local/lib/python2.7/site-packages/SQLObject-1.3.1-py2.7.egg/sqlobject/dbconnection.py:468:queryInsertID#012
/opt/scripts/virtualenv/local/lib/python2.7/site-packages/SQLObject-1.3.1-py2.7.egg/sqlobject/dbconnection.py:327:_runWithConnection#012
/opt/scripts/virtualenv/local/lib/python2.7/site-packages/SQLObject-1.3.1-py2.7.egg/sqlobject/postgres/pgconnection.py:191:_queryInsertID#012]

This makes me think that indeed there must be some callback for this kind of situation, otherwise that log entry wouldn't be written. I'd use that callback to establish a new connection to the database. I've been unable to find any piece of documentation about that.

Does anyone know if it's even possible to implement that callback and how to declare it?

Thanks.

user590028
  • 11,364
  • 3
  • 40
  • 57
nKn
  • 13,691
  • 9
  • 45
  • 62

1 Answers1

3

We're more regular users of SQLAlchemy rather than SQLObject. According to this thread from 2010 (http://sourceforge.net/p/sqlobject/mailman/message/26460439), SQLObject does not support reconnection logic for PostgreSQL. It's an old thread, but there does not appear to be any discussion about solving this from within SQLObject.

I have three suggested solutions.

The first solution is to explore Connection Pools. It might provide a way to open a new connection object when SQLObject detects the psycopg2 has disconnected. I can't guarantee it will, but if it does this solution would be your best best as it requires the least amount of changes on your part.

The second solution is to switch your backend from Postgres to MySQL. The SQLObject docs provide information on how use the reconnection logic of the mysql driver - http://sourceforge.net/p/mysql-python/feature-requests/9

The third solution is to switch to SQLAlchemy as your ORM and to use their version of Connection Pools. According to the core event documentation, when using pools if a connection is dropped or closed a new connection will opened -- http://docs.sqlalchemy.org/en/rel_0_9/core/exceptions.html#sqlalchemy.exc.DisconnectionError

Best of luck

user590028
  • 11,364
  • 3
  • 40
  • 57
  • Thanks. The second solution is discarded because we only work with PgSQL, however the other two are quite interesting. I'll get back and write some feedback about it. – nKn Feb 08 '15 at 22:19
  • We finally switched to `SQLAlchemy`, connection pools works nice, thanks :-) – nKn Feb 13 '15 at 22:11
  • Sorry you couldn't find a way to get SQLObject to bend to your needs -- but you'll find SQLAlchemy to be a good tool. – user590028 Feb 14 '15 at 00:45