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

Dustin J. Mitchell dustin at v.igoro.us
Tue Jun 10 19:02:32 MDT 2014


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



More information about the Twisted-Python mailing list