[Zope3-Users] recovering from a lost db connection

[Brian Sutherland]

On Thu, Jan 11, 2007 at 09:55:28PM -0500, Roy Mathew wrote:
> 
> Brian Sutherland writes:
>  > 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.
> 
> Brian, Thanks for your response.
> 
> I am confused - that means that if this is being done *currently* in
> Psycopgda, I should simply be able to repeat my client request and
> succeed in the operation. Or is ZPsycopgda something other than plain
> old psycopgda?

ZPsycopgDA is the zope 2 Product:) didn't realise it would be confused
with the zope 3 version psycopgda. Since I don't do much Zope3/RDB work
anymore, I havn't had a good look inside psycopgda.

> I am essentially stuck once I get this error. The stacktrace above
> shows that a retry was attempted but did not succeed. Sorry that I am
> missing your point, but I don't get it!

Yeah, but that's a retry within the sqlobject/sqlos layer, which
probably is just confusing cruft.

It's because sqlobject is designed to directly manage connections, but
has been shoehorned into using zope.rdb for connections which also manages
connections. Things get a little complex after that.

> 
>  >     * 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!!"