You might want to just try using SQLAlchemy via <https://pypi.python.org/pypi/alchimia> if you don't want to do your own thread-pool management as buildbot appears to be.
-glyph On Jun 10, 2014, at 6:02 PM, Dustin J. Mitchell <dus...@v.igoro.us> wrote: > 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
_______________________________________________ Twisted-Python mailing list Twisted-Python@twistedmatrix.com http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
