While working on a Simpycity + Pylons environment the other day, I noticed that my app was leaking Postgres connection handles. This is not behaviour you ever want to see, especially in software as vital as Simpycity. Investigation and testing demonstrated pretty conclusively here, and here that the Python garbage collector was not immediately cleaning up dead/unreferenced objects. Specifically, in the test case below,
As the Postgres handles are being created (by default) READ COMMITTED, they are left in the state " in transaction". This part can be handled via calling .commit() on all handles, but, still prone to the leaking/cleanup issue. At its core, the leaking issue stems from design choices behind Simpycity; specifically, abstracting away the connection management logic leaves us in the position where automatic management cannot depend purely on the Python garbage collector for handle cleanup. This lifecycle issue affects Simpycity under Pylons, as well.
By relying on the Python garbage collection, a request's Postgres connections fall out of scope, but are not reaped until later, when the mod_wsgi subprocess accepts and handles a new request. Once we realized how this happens in the interactive interpreter, we saw that in an environment such as mod_wsgi or mod_python, with multiple active subprocesses, lurking/hanging sessions will inevitably occur.
When Lacey and I studied SQLAlchemy, it appeared vulnerable to the same behaviour; Reading over their pooling logic, as well as examining a Pylons app configured to use SQLAlchemy in the Paster template showed they are explicitly cleaning up all DB connections after every Pylons request. This was implemented using a
Discussion with James Bennett (ubernostrum, irc.freenode.net) shows that the Django ORM has a similar issue. Django resolves this with an asynchronous message-passing system, which, at the end of a Request cycle, notifies the ORM backend that the opened connections should be terminated. Therefore: Reliance on Python's garbage-collection cycle is fickle at best, especially in long-running, asynchronous processes such as Pylons. Based on the SQLAlchemy mechanisms within Pylons, specifically the end-of-request session cleanup, it seems reasonable to implement a session manager for Simpycity.
This would exist to only to handle long-running scenarios where automatic garbage collection would not be able to consistently run. The connection manager would need to do little more than maintain a list of all active/open handles, and offer a .cleanup() call to free all handles at the end of the request. Coupled with the explicit try-finally cleanup pattern that SQLAlchemy uses within the Pylons WSGIController, a connection manager would succinctly solve the problem. This would maintain correct transactional integrity, because implicit commits are never issued to the PostgreSQL backend. The leaking handle issues would vanish, because we know that the only handles used within Simpycity will be tracked within the connection management object, and correctly cleaned up after each request.
In short-run applications, this is largely a non-issue, as the exiting of the interpreter closes all extant handles automatically. As this is a subtle issue, we'll be providing an upstream patch for Pylons, to provide a Paster template specifically to set up a connection management environment for Simpycity. This will also be documented on the Simpycity wiki, discussing how to set up a connection manager and use it for long-running programs.
def foo():
r = Raw("SELECT count(*) as cnt, usename, current_query FROM
pg_catalog.pg_stat_activity GROUP BY usename,current_query ORDER BY cnt
DESC")
return r()
foo()
when executed in the Python interactive interpreter, a dangling (unassigned) ResultSet object in remained in memory. Even though no references to the object exist, and it should be cleaned up, it will not be cleaned up until another garbage collection event is triggered. Calling locals(), globals() or by forcibly executing the garbage collection via import gc; gc.collect() will clean up the stray ResultSet as expected. Model-based objects exhibit the same behaviour. As the Postgres handles are being created (by default) READ COMMITTED, they are left in the state " in transaction". This part can be handled via calling .commit() on all handles, but, still prone to the leaking/cleanup issue. At its core, the leaking issue stems from design choices behind Simpycity; specifically, abstracting away the connection management logic leaves us in the position where automatic management cannot depend purely on the Python garbage collector for handle cleanup. This lifecycle issue affects Simpycity under Pylons, as well.
By relying on the Python garbage collection, a request's Postgres connections fall out of scope, but are not reaped until later, when the mod_wsgi subprocess accepts and handles a new request. Once we realized how this happens in the interactive interpreter, we saw that in an environment such as mod_wsgi or mod_python, with multiple active subprocesses, lurking/hanging sessions will inevitably occur.
When Lacey and I studied SQLAlchemy, it appeared vulnerable to the same behaviour; Reading over their pooling logic, as well as examining a Pylons app configured to use SQLAlchemy in the Paster template showed they are explicitly cleaning up all DB connections after every Pylons request. This was implemented using a
try:
...
finally:
...
block in the core Pylons controller, as well as the global initialization of a db connection during Pylons setup (see model/meta.py, model/__init__.py, lib/base.py in a default Pylons project setup as an example). As Pylons + SQLAlchemy is not our default environment, this was initially missed.Discussion with James Bennett (ubernostrum, irc.freenode.net) shows that the Django ORM has a similar issue. Django resolves this with an asynchronous message-passing system, which, at the end of a Request cycle, notifies the ORM backend that the opened connections should be terminated. Therefore: Reliance on Python's garbage-collection cycle is fickle at best, especially in long-running, asynchronous processes such as Pylons. Based on the SQLAlchemy mechanisms within Pylons, specifically the end-of-request session cleanup, it seems reasonable to implement a session manager for Simpycity.
This would exist to only to handle long-running scenarios where automatic garbage collection would not be able to consistently run. The connection manager would need to do little more than maintain a list of all active/open handles, and offer a .cleanup() call to free all handles at the end of the request. Coupled with the explicit try-finally cleanup pattern that SQLAlchemy uses within the Pylons WSGIController, a connection manager would succinctly solve the problem. This would maintain correct transactional integrity, because implicit commits are never issued to the PostgreSQL backend. The leaking handle issues would vanish, because we know that the only handles used within Simpycity will be tracked within the connection management object, and correctly cleaned up after each request.
In short-run applications, this is largely a non-issue, as the exiting of the interpreter closes all extant handles automatically. As this is a subtle issue, we'll be providing an upstream patch for Pylons, to provide a Paster template specifically to set up a connection management environment for Simpycity. This will also be documented on the Simpycity wiki, discussing how to set up a connection manager and use it for long-running programs.