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

[Phil Mayers]

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.