On Sun, 10 Jun 2007 13:04:58 +0100, Phil Mayers <p.mayers@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