[Twisted-Python] adbapi.ConnectionPool - reconnecting to postgresql

[Glyph]

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 <dustin at 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.mayers at 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 at twistedmatrix.com
>> http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
> 
> _______________________________________________
> Twisted-Python mailing list
> Twisted-Python at twistedmatrix.com
> http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python

-------------- next part --------------
An HTML attachment was scrubbed...
URL: </pipermail/twisted-python/attachments/20140610/3ed474dd/attachment-0002.html>