• We have a pyramid web application.
• We use SQLAlchemy@1.4 with Zope transactions.

In our application, it is possible for an error to occur during flush as described here which causes any subsequent usage of the SQLAlchemy session to throw a PendingRollbackError. The error which occurs during a flush is unintentional (a bug), and is raised to our exception handling view... which tries to use data from the SQLAlchemy session, which then throws a PendingRollbackError.

Is it possible to "recover" from a PendingRollbackError if you have not framed your transaction management correctly? The SQLAclhemy documentation says to avoid this situation you essentially "just need to do things the right way". Unfortunately, this is a large codebase, and developers don't always follow correct transaction management. This issue is also complicated if savepoints/nested transactions are used.

def some_view():
    # constraint violation
    session.add_all([Foo(id=1), Foo(id=1)])
    session.commit()  # Error is raised during flush
    return {'data': 'some data'}


def exception_handling_view():  # Wired in via pyramid framework, error ^ enters here.
    session.query(... does a query to get some data)  # This throws a `PendingRollbackError`

I am wondering if we can do something like the below, but don't understand pyramid + SQLAlchemy + Zope transactions well enough to know the implications (when considering the potential for nested transactions etc).

def exception_handling_view():  # Wired in via pyramid framework, error ^ enters here.
    def _query():
        session.query(... does a query to get some data)

    try:
        _query()
    except PendingRollbackError:
        session.rollback()
        _query()