[Twisted-Python] adbapi.ConnectionPool - reconnecting to postgresql
Phil Mayers
p.mayers at imperial.ac.uk
Tue Jun 10 13:02:23 MDT 2014
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.
More information about the Twisted-Python
mailing list