SQLAlchemy scoped_session not removed by transaction manager

[ste...@kaptio.com]

Dear list

I am working on a project using pyramid 1.6, pyramid_tm 1.1.1 and SQLAlchemy 1.1.4. 

A thread-local scoped_session object is used as suggested here (knowing that this is probably not the best practice anymore)

DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))

Strangely, I found out that the scoped_session returned by DBSession() is the identical object among several requests—when the request is handled by the same thread.

Apparently, the DBSession.remove() is not called by the transaction manager as (I understand) it should be.

The problem can be resolved by event listeners either on the side of pyramid or SQLAlchemy but I think this should be directly handled by the transaction manager.

Is this a faulty behavior, or did I get this wrong?

Best

Stefan

[Jonathan Vanasco]

1. You probably shouldn't use a global sqlalchemy session like that.  It is the cause of many developer's problems. There should probably be a ticket to remove/update that howto, so it references the current cookiecutter -- which stashes the session onto the request. 

2. pyramid_tm doesn't close/remove the session when finished.  if you look at the code, it doesn't touch sqlalchemy at all.  it just integrates zope.transaction's hooks with pyramid.  the sqlalchemy hookup is handled by passing in the zope_sqlalchemy's extension -- which handles all the hooks if sqlalchemy is used.  That package is using `session.close()` not `session.remove()`, because 'remove' is specific to scoped sessions (and the package supports regular sessions as well) .

tldr; pyramid_tm is a bad place to handle this sort of thing, because there are multiple database backends (sqlalchemy, zope) and multiple session types (scoped, explicit).

[Michael Merickel]

Even if the session object is the same (which it is) I believe transactional state has been properly cleaned up by zope.sqlalchemy invoking rollback/commit/close on the session itself. However the session and any strong refs it's storing may never be released. As far as I know this shouldn't actually cause a problem but it is a smell for sure.

If you're stuck with the global session (of course I recommend switching), you may just want to add a tween OVER pyramid_tm that invokes DBSession.remove(). This can be done very simply. For example:

def tm_cleanup_tween_factory(registry, handler):

    def tween(request):

        try:

            return handler(request)

        finally:

            DBSession.remove()

    return tween

def includeme(config):

    config.add_tween(__name__ + '.tm_cleanup_tween_factory', over='pyramid_tm.tm_tween_factory')

Then in your app just config.include('dotted.path.to.this.module') next to your config.include('pyramid_tm') logic.

--
You received this message because you are subscribed to the Google Groups "pylons-discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pylons-discuss+unsubscribe@googlegroups.com.
To post to this group, send email to pylons-discuss@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/pylons-discuss/6fba4f55-824e-4a31-ae63-409caaa8563a%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Jonathan Vanasco]

On Thursday, June 22, 2017 at 1:16:54 PM UTC-4, Michael Merickel wrote:

Even if the session object is the same (which it is) I believe transactional state has been properly cleaned up by zope.sqlalchemy invoking rollback/commit/close on the session itself. However the session and any strong refs it's storing may never be released. As far as I know this shouldn't actually cause a problem but it is a smell for sure.

yeah. zope.sqlalchemy properly cleans up by invoking a `close()`.  there are a large handful of edge-cases that require scoped sessions to be cleaned up via a `remove()` -- IIRC, most are around forked processes, threading, connection pooling, and server timeouts.

Your tween should probably be the standard practice for anyone using scoped sessions.

[ste...@kaptio.com]

Thank you all for your answers! 

I'll go with the tween solution.

Best

Stefan