SQLAlchemy makes this kind of pessimistic reconnection pretty easy; we do it for MySQL in https://github.com/buildbot/buildbot/blob/master/master/buildbot/db/enginestrategy.py I'm not sure why we don't do it for Postgres, but we've never heard a complaint from a postgres user, so perhaps other layers are handling it.
In general, I consider this a requirement for a production-ready database layer, along with correctly setting SO_KEEPALIVE. Dustin On Tue, Jun 10, 2014 at 3:02 PM, Phil Mayers <p.may...@imperial.ac.uk> wrote: > On 10/06/2014 19:24, Jonathan Vanasco wrote: >> >> >> I had to restart postgresql , and that triggered some endless errors in my >> logs. >> >> i don't have anything that can handle a suddenly dropped / resumed >> connection. >> >> does anyone have a recommendation for trying to reconnect or handle this >> in general >> >> from what I can tell from my logs... >> >> When I catch an error, my cleanup code tries to >> >> self._connection.rollback() >> >> which raises >> >> psycopg2.InterfaceError: connection already closed > > > Yes, this is irritating. > > Related tickets see: > > http://twistedmatrix.com/trac/ticket/4404 > http://twistedmatrix.com/trac/ticket/4964 > > You absolutely want to be setting cp_reconnect=1 on ConnectionPool. Anything > else is asking for major trouble, and I really think this should be the > default. > > cp_reconnect will not avoid all problems - for a thread pool of size N, the > next N transactions will have to fail to throw away all the dead connections > - but it might stop them happing forever. > > In some other, non-Twisted contexts, people have had problem even with > reconnecting, because some versions of some DBAPI drivers emit a connection > from .close() if it's already closed, which interrupts the reconnection > logic :o( > > It seems your version of psycopg is doing this - how old is it? I thought > that was fixed these days? > > You might also look at something like pgbouncer, which you can configure to > try a "good" SQL query on the outbound connections before handing to an > inbound client; this probes the connections. It adds latency, but personally > I would far rather pay the small number of milliseconds on "select 1" than > bomb out to the upper layers. > > An annoying problem. > > > _______________________________________________ > Twisted-Python mailing list > Twisted-Python@twistedmatrix.com > http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python _______________________________________________ Twisted-Python mailing list Twisted-Python@twistedmatrix.com http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
