[Twisted-Python] Re: twisted.enterprise.adbapi and connection pool problem

[Nicola Larosa]

>> I've noticed a bug(?) or at least misbehaviour in adapi connection pool.
>> I've got a daemon program which keeps a few connections to PostgreSQL
>> database and when a client connects and sends some commands it queries the
>> database and sends back results. Everything works fine, until I restart
>> PostgreSQL. All commands fail and the only trace I've got is the response
>> from the database:

> Attempting reconnects just isn't part of adbapi as far as I can tell. 
> I use psycopg with postgresql and here is how I handle this, maybe
> someone else has a better way of doing it.  This tests the database
> connection every 10 seconds, and attempts to reconnect 3 times if it
> gets an error, otherwise it exits.  It's not very complete but that's
> because I just threw it together today...

Funny, I had the same problem and threw together a similar solution with
the same approach, down to the "SELECT 1" query. :-)

Main difference is, when I detect a problem I do not exit, but just do

self.dbpool.close()
self.dbpool.start()

If you look at the source of adbapi, you see that you can do that even
if the program cannot yet reconnect to PostgreSQL. The connections are
not really established until something tries to use them.

-- 
Nicola Larosa - nico at tekNico.net

The reward for your efforts will be an equivocal one: you will feel as
suffocated programming in C++ as an experienced C++ programmer would
feel programming in Basic. -- Paul Graham, ANSI Common Lisp, 1995