[Zope3-Users] recovering from a lost db connection

[Brian Sutherland]

On Wed, Jan 10, 2007 at 08:13:27PM -0500, Roy Mathew wrote:
> What is the recommended way to recover from a db connection that is
> held by a Psycopgda adapter instance? If for some reason postgres
> goes down, I get the following error:
> 
>   File "/var/lib/zope3/instances/instance.barry2007/lib/python/sqlos/adapter.py", line 83, in _runWithConnection
>     val = meth(conn, *args)
>   File "/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1814-py2.4.egg/sqlobject/dbconnection.p ", line 351, in _queryOne
>     self._executeRetry(conn, c, s)
>   File "/var/lib/zope3/instances/instance.barry2007/lib/python/sqlos/adapter.py", line 77, in _executeRetry
>     raise DatabaseException(str(exc.args))
>   DatabaseException: ('no connection to the server\n',).
> 
> I then have no recourse but to restart the zope server. In the Java
> world one would put a try/catch around the the SQL operation and
> attempt a reconnect, but I am wondering if there is a better way
> (read: automatic recovery) in a z3 world.

The way ZPsycopgDA does it is exactly that. But it can be a little
complex when you look at the detail.

In the Z3 case, I would:
    * If the error is a "concurrent update" error, raise a Retry
      exception. The same request gets tried again.
    * If the error is an OperationalError, close the connection and
      re-raise the error (perhaps as a Retry error, though I can think
      of cases where this is a bad idea). On the next request, the
      connection is re-opened.
    * On other errors, let the error propagate.

All this stuff should probably be done at the zope.rdb level.

> 
> -- 
> Thanks,
> Roy Mathew.
> _______________________________________________
> Zope3-users mailing list
> Zope3-users at zope.org
> http://mail.zope.org/mailman/listinfo/zope3-users
> 

-- 
Brian Sutherland

Metropolis - "it's the first movie with a robot. And she's a woman.
              And she's EVIL!!"