Chas Emerick wrote:
> Hi all. We're moving into heavy testing for our new website and a few
> accompanying apps (all based on pylons/sqlobject/kid), but we've run
> into a serious problem with leaking database connections.
> Briefly put, if we use a
> sqlobject.postgres.pgconnection.PostgresConnection as the _connection
> attribute on our SQLObject subclasses, then all is well. However,
> using any kind of ConnectionHub (ConnectionHub, AutoConnectHub, or
> PackageHub) yields behaviour where a database connection is leaked per
> request. This is based on watching the results of 'select count(1)
> from pg_stat_activity;', and then inevitably getting an error when we
> max out the database connection limit.
> Specifically, we've been successful with this line (<3 connections ever
> show as being open in pg_stat_activity, even under load):
> postgres =
> but either of these lines cause major leakage:
> # postgres = PackageHub('postgresql')
> # postgres = AutoConnectHub('postgres://web@...>')
I'm not familiar with AutoConnectHub? But the key part is how you
connect and disconnect your connection from the hub. I usually do it like:
hub.threadConnection = connectionFromURI(...)
hub.threadConnection = None
Additionally in the trunk there's
which should handle this logic in a WSGI context.
> For completeness, here's one of our SQLObject classes:
> class User (SQLObject):
> _connection = postgres
> class sqlmeta:
> username=StringCol(length=64, alternateID=True)
> [blah blah]
> I spent a few hours digging around on the web, looking at the source
> code for the connection hubs, sqlobject.dbconnection.DBAPI, and not
> seeing anything glaringly obvious. Further investigation turned up the
> wiki page related to SQLObject connection issues
> (http://wiki.sqlobject.org/connections.html), but suggestions and links
> from that weren't effective.
> There's tons of examples out there that use PackageHub, and we would
> most definitely like to use its cleaner transaction support.
> If this can't be worked out before our release, is there any reason why
> using a PostgresConnection instance as our class' _connection attribute
> would be inadvisable?
As long as you are only accessing one database in a process, that's fine.
Ian Bicking / ianb@... / http://blog.ianbicking.org