autocommit=False, autoflush on begin, after_flush event, unintended side effect?

[Yap Sok Ann]

This is semi-related to the latest post from Kent. I just noticed that I have been abusing the "autoflush on begin" behavior (by the _take_snapshot() method in orm/session.py) to create additional instances within the after_flush Session Event. Here's some sample code to illustrate that:

########

from sqlalchemy import event

from sqlalchemy.engine import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import relationship, scoped_session, sessionmaker

from sqlalchemy.schema import Column, ForeignKey

from sqlalchemy.types import Integer, Text

Base = declarative_base()

engine = create_engine('postgresql://postgres@localhost/test')

Session = scoped_session(sessionmaker(

    autoflush=False,

    autocommit=False,

))

Session.configure(bind=engine)

class Ticket(Base):

    __tablename__ = 'tickets'

    id = Column(Integer, primary_key=True)

    description = Column(Text, nullable=False)

class Notification(Base):

    __tablename__ = 'notifications'

    id = Column(Integer, primary_key=True)

    ticket_id = Column(Integer, ForeignKey('tickets.id'), nullable=False)

    ticket = relationship('Ticket', backref='notifications')

    content = Column(Text, nullable=False)

def send_notification(session, flush_context):

    for instance in session.new:

        if isinstance(instance, Ticket):

            Notification(

                ticket=instance,

                content='Ticket %d created' % instance.id,

            )

            # No flush or commit!

event.listen(Session, 'after_flush', send_notification)

Base.metadata.create_all(engine)

ticket = Ticket(description='test')

Session.add(ticket)

Session.commit()

query = Session.query(Notification).filter_by(

    content='Ticket %d created' % ticket.id

)

assert query.count()

########

Although the code only does "Session.commit()" once, it actually executes 2 INSERT statements in 2 separate transactions. I am pretty sure this is not an intended use case, right?

[Michael Bayer]

On Sep 12, 2012, at 11:54 PM, Yap Sok Ann wrote:

> This is semi-related to the latest post from Kent. I just noticed that I have been abusing the "autoflush on begin" behavior (by the _take_snapshot() method in orm/session.py) to create additional instances within the after_flush Session Event. Here's some sample code to illustrate that:

that behavior is sort of horrifying, in that the "take snapshot" code, finding totally unexpected dirty state after a flush, realizes it needs to flush again, and since no new transaction has been started yet, it commits a new one.

this sort of points to Kent's issue sure, that flush might have to just loop around and keep calling itself until nothing more needs to be flushed.

ticket 2566 http://www.sqlalchemy.org/trac/ticket/2566 is added, including a patch, for 0.7.9.