[mod_python] avoiding database connection leak on module (re)import

Mark Harrison wrote ..
> Whenever the source file is modified, mod_python re-imports the
> file.  A naive program will have a connection leak if it just
> has a line like this:
> 
> myconnection = mydbpackage.connect(...)
> 
> 
> So, I'm closing and reopening the connection on each re-import:
> 
> try:
>      myconnection.close()
> except NameError:
>      pass
> myconnection = mydbpackage.connect(...)
> 
> Is there a better, more  mod_pythonic way to handle this type of
> situation?

This is a known issue. See ISSUE 12 in:

  http://www.dscpl.com.au/wiki/ModPython/Articles/ModuleImportingIsBroken

What exactly happens actually depends on which version of mod_python you are
using.

If you are using mod_python <3.2 when a reload occurs the new module is
reloaded on top of the existing module. Thus, the instance of the connection
object you created as a result of the prior load of the module will be replaced,
with close() not being able to be explicitly called on the connection object. If the
particular database connection object you are using does not play nicely and
automatically call close() when the Python object is dereferenced for the last
time, you will leak a database connection.

For these versions of mod_python you can use:

  if not globals().has_key('myconnection'):
    myconnection = mydbpackage.connect(...)

What this results in is that when the module is reloaded, it will detect that the
database connection object already exists and not bother to replace it and instead
will use the one inherited from the previous load of the module.

BTW, I should point out though that creating a database connection object at
global scope within a module like this is possibly bad practice. The reason is that
a failure to create the database connection will cause loading of the whole module
to fail. You are better better off having a singleton type object which is accessed
by actual request handlers when it needs the connection object, with the connection
object only being created the first time it is required. But then, even this will only
suffice in a non threaded Apache MPM. If using Windows of a multithreaded MPM
such as 'worker' on UNIX, you will need a more sophisticated pooling arrangement
or simply create the connection object for every request.

If you are using mod_python 3.2, the above applies only for modules pertaining to
custom handlers you have written yourself. If you are using mod_python.publisher
in 3.2, it is using its own module loader which creates an empty module into which
to load the new module. Thus, the existing connection object isn't overwritten,
but there also isn't any means to access the old connection object and close it
properly either. Thus, if the connection object doesn't call close() automatically when
Python object dereferenced, it will leak a database connection.

In mod_python 3.3, all modules, whether be they custom handler modules or
mod_python.publisher modules, will be loaded into a new empty module on a
reload of a module. In 3.3 however, you can supply hook functions to transfer data
from the old module instance to the new module instance. You thus use a combination
of what was required for versions <3.2 and the hook function.

  if not globals().has_key('myconnection'):
    myconnection = mydbpackage.connect(...)

  def __mp_clone__(module):
    module.myconnection = myconnection

If the connection object doesn't call close() properly as explained above, just in case
there is a problem with the clone hook and it raises an exception, you probably also
want to add:

  def __mp_purge__(module):
    try:
      myconnection.close()
    except:
      pass

This is the final fallback case where a module couldn't be cloned due to some failure
and it was necessary to discard both the old and new module instances and start
over fresh. The purge hook would allow you to call close() explicitly for the connection
object where it doesn't clean itself up properly.

In summary, things can be a bit messy for mod_python <3.3 and it is only 3.3
where what happens is a bit more defined and can be controlled.

Now, which version of mod_python are you using and is this a custom handler or
mod_python.publisher?

As a general suggestion, put the creation of your connection object in a module
that is not a candidate for automatic module reloading. That or just create it only
for the current request and then close() is explicitly when finished with it for that
request.

Graham