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