[Twisted-Python] adbapi.ConnectionPool - reconnecting to postgresql
Glyph
glyph at twistedmatrix.com
Tue Jun 10 23:23:56 MDT 2014
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: <http://twistedmatrix.com/pipermail/twisted-python/attachments/20140610/3ed474dd/attachment.html>
More information about the Twisted-Python
mailing list