Uncatchable InvalidRequestError within view depending on order of SQLAlchemy queries

[Zsolt Ero]

My project is based on standard SQLAlchemy scaffold, ZTE, using ORM. Pyramid 1.5.8.

I'm trying to catch an IntegrityError (unique key constraint), I've made a very minimal example

@view_config(route_name='exception_test', renderer='json')

def exception_test(request):

    user = DBSession.query(User).filter(User.id == 1).first()

    user.tutorial_done = True

    DBSession.query(Map).first()

    map_obj = Map(user=request.user)

    map_obj.name = u'a'

    map_obj.slug = 'a'

    try:

        DBSession.add(map_obj)

        DBSession.flush()

    except Exception:

        return {'success': False}

    return {

        'id': map_obj.id,

        'success': True

    }

This results in a InvalidRequestError, which cannot be catched, even with an except Exception.

04:34:25,180 ERROR [waitress] Exception when serving /exception_test

Traceback (most recent call last):

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/waitress/channel.py", line 336, in service

    task.service()

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/waitress/task.py", line 169, in service

    self.execute()

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/waitress/task.py", line 388, in execute

    app_iter = self.channel.server.application(env, start_response)

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/paste/translogger.py", line 69, in __call__

    return self.application(environ, replacement_start_response)

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/pyramid/router.py", line 242, in __call__

    response = self.invoke_subrequest(request, use_tweens=True)

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/pyramid/router.py", line 217, in invoke_subrequest

    response = handle_request(request)

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/pyramid/tweens.py", line 21, in excview_tween

    response = handler(request)

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/pyramid_tm/__init__.py", line 101, in tm_tween

    reraise(*exc_info)

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/pyramid_tm/__init__.py", line 90, in tm_tween

    manager.commit()

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/transaction/_manager.py", line 111, in commit

    return self.get().commit()

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/transaction/_transaction.py", line 280, in commit

    reraise(t, v, tb)

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/transaction/_transaction.py", line 271, in commit

    self._commitResources()

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/transaction/_transaction.py", line 417, in _commitResources

    reraise(t, v, tb)

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/transaction/_transaction.py", line 394, in _commitResources

    rm.tpc_vote(self)

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/zope/sqlalchemy/datamanager.py", line 103, in tpc_vote

    self.tx.commit()

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 390, in commit

    self._assert_active(prepared_ok=True)

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 214, in _assert_active

    % self._rollback_exception

InvalidRequestError: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (psycopg2.IntegrityError) duplicate key value violates unique constraint "uq_maps_user_id"

DETAIL:  Key (user_id, slug)=(1, a) already exists.

If I put a DBSession.rollback() in the exception block, it results in:

04:37:58,625 ERROR [waitress] Exception when serving /exception_test

Traceback (most recent call last):

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/waitress/channel.py", line 336, in service

    task.service()

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/waitress/task.py", line 169, in service

    self.execute()

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/waitress/task.py", line 388, in execute

    app_iter = self.channel.server.application(env, start_response)

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/paste/translogger.py", line 69, in __call__

    return self.application(environ, replacement_start_response)

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/pyramid/router.py", line 242, in __call__

    response = self.invoke_subrequest(request, use_tweens=True)

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/pyramid/router.py", line 217, in invoke_subrequest

    response = handle_request(request)

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/pyramid/tweens.py", line 21, in excview_tween

    response = handler(request)

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/pyramid_tm/__init__.py", line 101, in tm_tween

    reraise(*exc_info)

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/pyramid_tm/__init__.py", line 90, in tm_tween

    manager.commit()

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/transaction/_manager.py", line 111, in commit

    return self.get().commit()

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/transaction/_transaction.py", line 280, in commit

    reraise(t, v, tb)

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/transaction/_transaction.py", line 271, in commit

    self._commitResources()

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/transaction/_transaction.py", line 417, in _commitResources

    reraise(t, v, tb)

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/transaction/_transaction.py", line 394, in _commitResources

    rm.tpc_vote(self)

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/zope/sqlalchemy/datamanager.py", line 103, in tpc_vote

    self.tx.commit()

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 390, in commit

    self._assert_active(prepared_ok=True)

  File "/Users/user/.virtualenvs/maphub_web/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 223, in _assert_active

    raise sa_exc.ResourceClosedError(closed_msg)

ResourceClosedError: This transaction is closed

Moreover, the weird part is that it all seems to depend on seemingly meaningless (to me at least) order of the queries:

Moving the query(Map) to be before user.tutorial_done

user = DBSession.query(User).filter(User.id == 1).first()

DBSession.query(Map).first()

user.tutorial_done = True

Results in the correct, catchable IntegrityError.

However, removing the .filter() part makes it broken again:

user = DBSession.query(User).first()

DBSession.query(Map).first()

user.tutorial_done = True

What is happening here? I'm totally confused. Is this a bug in ZTE or SQLAlchemy? How can I possibly run into an uncatchable exception in a view, and if I do, what am I supposed to do?

[Simon King]

It's difficult to tell without a sample that we can run, but I think the exception is being raised after your view function returns. The zope transaction package (in combination with the pyramid_tm and pyramid_sqlalchemy packages) wraps each request to ensure that a transaction is either committed or rolled back at the end of the request. Your IntegrityError in the middle of the view is causing the transaction to be left in a broken state, but you aren't signalling that to the transaction manager. It has no way of knowing that an error has occurred in the view, so it tries to commit the transaction. Since this happens outside your view altogether, you can't catch it.

The reason you get different behaviour when you order the statements differently is likely to be autoflush. By default, pending changes are flushed to the database whenever you issue a query (so in your original example, the call to DBSession.query(Map).first() will flush the user.tutorial_done change to the database).

Hope that helps,

Simon

[Zsolt Ero]

I'll try to make a minimal reproducible sample of out if.

For autoflush, I tried putting manual flush lines between the commands
but the results were the same.

About exception, it is entering the exception block (I tried putting
print 'exception' there) but it is not catching it.

Zsolt

> --
> You received this message because you are subscribed to a topic in the
> Google Groups "pylons-devel" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/pylons-devel/NqHlZ52NPxU/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> pylons-devel...@googlegroups.com.
> To post to this group, send email to pylons...@googlegroups.com.
> Visit this group at https://groups.google.com/group/pylons-devel.
> For more options, visit https://groups.google.com/d/optout.

[Simon King]

Your exception block *is* catching an exception, but that's not the exception you are seeing being logged. Imagine the code in pyramid_tm does something like this (where "handler" is the view function, in your case exception_test):

# Actual code is at

# https://github.com/Pylons/pyramid_tm/blob/master/pyramid_tm/__init__.py#L44

# this is just a trivial example

def manage_transaction(request):

    transaction = start_transaction()

    try:

        response = handler(request)

    except Exception, e:

        transaction.rollback()

    else:

        transaction.commit()

    return response

In your exception_test function, you've caught the IntegrityError and returned a dictionary. pyramid_tm has no idea that an exception even occurred, so control falls through to the "else" clause which tries to commit the transaction. This fails because the transaction is in a broken state, so a *new exception* is raised. You can't catch the new exception, because your code isn't running any more. Your view function has already returned.

Simon

    

You received this message because you are subscribed to the Google Groups "pylons-devel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pylons-devel...@googlegroups.com.