sqlalchemy: after_transaction_end event not fired when NestedTransaction commits in future mode

Describe the bug

According to the documents at https://docs.sqlalchemy.org/en/14/orm/events.html#sqlalchemy.orm.SessionEvents.after_transaction_end which say:

This event differs from SessionEvents.after_commit() in that it corresponds to all SessionTransaction objects in use, including those for nested transactions and subtransactions

(my emphasis)

we should see after_transaction_end fire when I commit a transaction returned from Connection.begin_nested. However, no such event fires.

To Reproduce

from sqlalchemy import event, create_engine, Column, Integer
from sqlalchemy.orm import declarative_base, sessionmaker
  
Base = declarative_base()
class Foo(Base):
    __tablename__ = "foo"
    id = Column(Integer, primary_key=True)
    foo = Column(Integer)
  
engine = create_engine("sqlite://", future=True)
# I know sqlite doesn't have savepoints but it makes the example easier. Fails on postgres too.
Session = sessionmaker()
Base.metadata.create_all(bind=engine)
 
connection = engine.connect()
txn = connection.begin()
session = Session(future=True, bind=connection)
 
@event.listens_for(session, "after_transaction_end")
def do_something(sess, transaction):
    print("after_transaction_end called")
 
savepoint = connection.begin_nested()
assert savepoint.is_active == True                                                                                                                                                                                             
session.add(Foo(foo=1))
savepoint.commit()
assert savepoint.is_active == False
 
 
# Should print the message above, does not.

Error

No event callback happens.

Versions

OS: Ubuntu 20.04
Python: 3.8
SQLAlchemy: 1.4.22
Database: Postgres 11
DBAPI:

Additional context

No response

About this issue

Original URL
State: closed
Created 3 years ago
Comments: 19 (9 by maintainers)

Most upvoted comments

looks like this is figured out.

zzzeek on Aug 18, 2021

Since I wound up here having a similar problem but using pytest, here’s a working pytest implementation of https://github.com/sqlalchemy/sqlalchemy/issues/6823#issuecomment-889860696 in case any other pytest users land here from google/ddg:

import pytest
from sqlalchemy import event
from sqlalchemy.engine import create_engine
from sqlalchemy.orm.session import sessionmaker

engine = create_engine(<db_string>[, echo=True, echo_pool=True])
Session = sessionmaker()

# Should add: I keep a test db with the schema already defined and reflect all the tables. If that's not
# how you do it, your schema definition code and setting up declarative should go here, and you'll need to
# add the create_all and drop_all calls, perhaps to a separate, class-scoped fixture.

@pytest.fixture
def savepoint():
    connectors = {}
    connectors["connection"] = engine.connect()
    connectors["transaction"] = connectors["connection"].begin()
    connectors["session"] = Session(bind=connectors["connection"])
    connectors["nested"] = connectors["connection"].begin_nested()

    @event.listens_for(connectors["session"], "after_transaction_end")
    def end_savepoint(session, transaction):
        if not connectors["nested"].is_active:
            connectors["nested"] =  connectors["connection"].begin_nested()

    yield connectors["session"]
    connectors["session"].close()
    connectors["transaction"].rollback()
    connectors["connection"].close()

As to how I actually fixed the problem of data being left behind after every test, I guess at some point during a refactor I failed to update a reference to a previously module-scoped variable (the Session) into a class attribute reference. My tests were then using two separate Session objects for different operations, and only one was inside the Connection’s transaction.

Overall a productive process: it revealed not only a simple programmer error but also a poor design choice.

MajorDallas on Aug 6, 2021

Hi again

I have got to the bottom of things. I had a custom sessionmaker in place that was re-binding the session to the engine, which caused a BEGIN to get emitted and overrode the savepoint.

Thank you VERY much for helping me through this, this is an awesome project with awesome people. The support will not be forgotten.

Cheers.

bigjools on Aug 1, 2021