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 =
pgconnection.PostgresConnection.connectionFromURI('postgres://[EMAIL
PROTECTED]/web')
but either of these lines cause major leakage:
# postgres = PackageHub('postgresql')
# postgres = AutoConnectHub('postgres://[EMAIL PROTECTED]/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:
try:
hub.threadConnection = connectionFromURI(...)
do stuff...
finally:
hub.threadConnection = None
Additionally in the trunk there's
http://svn.colorstudy.com/SQLObject/trunk/sqlobject/wsgi_middleware.py
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:
table='web_user'
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.
Suggestions?
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 / [EMAIL PROTECTED] / http://blog.ianbicking.org
-------------------------------------------------------
This SF.Net email is sponsored by xPML, a groundbreaking scripting language
that extends applications into web and mobile media. Attend the live webcast
and join the prime developer group breaking into this new coding territory!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
_______________________________________________
sqlobject-discuss mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
