Dear Michael, I have discovered that under very particular circumstances, SQLAlchemy forgets that an object was newly created in a transaction, and if that transaction rolls back, the object's state is not reset from persistent to transient.
It seems to rely on the following: * Open an outer transaction * Open an inner transaction, add object to session, close transaction to commit. The object is now persistent. * Clear all references to the object, allowing it to be GC'ed and removed from the session._new weakref map * Load the object from the database again. * Raise an exception to rollback the outer transaction. * SQLAlchemy does not realise that the object is new, and fails to reset its state. * Object is still "persistent", but no longer in the database. Here is an example that reproduces the problem: from sqlalchemy import Column, Integer, Text, create_engine, inspect from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker Base = declarative_base() class Dog(Base): __tablename__ = 'dog' id = Column(Integer, primary_key=True) name = Column(Text) def create(): global session with session.begin(nested=True): fido = Dog(name="fido") session.add(fido) assert not inspect(fido).persistent, "should not yet be persisted to database" assert inspect(fido).persistent, "should now be persisted to database, but transaction not yet committed" def fetch(): global session global fido fido = session.query(Dog).filter_by(name="fido").one() assert inspect(fido).persistent, "should have been retrieved from the database, therefore persistent" def main(): engine = create_engine('sqlite://') engine.echo = True Base.metadata.create_all(engine) DBSession = sessionmaker(bind=engine) global session session = DBSession(autocommit=True) try: with session.begin(subtransactions=True) as trans: create() print(dict(trans._new)) fetch() print(dict(trans._new)) raise Exception("force transaction to roll back") except Exception: pass global fido assert not inspect(fido).persistent, "after rollback, should no longer be in the database" if __name__ == '__main__': main() Which fails the last assertion: AssertionError: after rollback, should no longer be in the database I know that this is a minor issue, but I found it quite confusing while debugging. I suspect that we might need to keep a strongly referenced list of "newly added" PKs in the outer session, instead of using _new. Thanks, Chris. This email is confidential. If you are not the intended recipient, please advise us immediately and delete this message. The registered name of Cantab- part of GAM Systematic is Cantab Capital Partners LLP. See - http://www.gam.com/en/Legal/Email+disclosures+EU for further information on confidentiality, the risks of non-secure electronic communication, and certain disclosures which we are required to make in accordance with applicable legislation and regulations. If you cannot access this link, please notify us by reply message and we will send the contents to you. GAM Holding AG and its subsidiaries (Cantab – GAM Systematic) will collect and use information about you in the course of your interactions with us. Full details about the data types we collect and what we use this for and your related rights is set out in our online privacy policy at https://www.gam.com/en/legal/privacy-policy. Please familiarise yourself with this policy and check it from time to time for updates as it supplements this notice. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.