[Twisted-Python] implementing an adbapi connectionpool idle timer

[Jean-Paul Calderone]

On Sun, 10 Jun 2007 13:04:58 +0100, Phil Mayers <p.mayers at imperial.ac.uk> wrote:
>All,
>
>I'm using t.e.adbapi with psycopg against a Postgres 8 database. This 
>database is replicated using Slony-I and I'm hitting this problem:
>
>http://linuxfinances.info/info/faq.html#AEN45927
>
>Basically, holding open SQL connections for a long time causes postgres to 
>cache query plans; these cached plans need to be freed for Slony to rollover 
>its internal SQL replication log table.
>
>So I'd like to add something to ConnectionPool to disconnect unused 
>connections after a certain time. Any suggestions how to start?

Be prepared to spend a lot of time in the test suite.  adbapi's test coverage
isn't very unit-like and relies on being able to connect to a real SQL server
in most cases.

You can take a look at twisted.internet.task.Clock for a mechanism to write
a test which doesn't rely on system time.  You might want to create a fake
DB-API 2.0 module in order to test the code without relying on a real SQL
server.  With those things in place, you might be able to get some reasonable
test coverage, at which point it sounds like a straightforward enough feature
to add.

>
>Also - looking at the ConnectionPool/Transaction code, it appears a 
>ConnectionPool's "connections" dictionary is modified from multiple threads 
>without locking. Is this safe?
>

Python builtins are threadsafe.  So, maybe. ;)  As usual, it is always
possible there is a bug hiding there.  Feel free to point it out if you
notice anything in particular.

Jean-Paul