post_update does not apply state to updated objects in a multi-execute correctly #12748
Description
Describe the bug
very similar to #10800
Deleting a parent object with a cascade=delete relationship bumps the version_id of it's children if the child object has a relationship with post_update=True. This causes the subsequent cascade delete of the children to fail, as the version_id no longer matches
Oddly, this works on python 3.12 but fails on python 3.13.5
Optional link from https://docs.sqlalchemy.org which documents the behavior that is expected
No response
SQLAlchemy Version in Use
2.0.41
DBAPI (i.e. the database driver)
pysqlite
Database Vendor and Major Version
SQLite
Python Version
3.13.5
Operating system
Windows
To Reproduce
from datetime import datetime from typing import List from sqlalchemy import Column from sqlalchemy import create_engine from sqlalchemy import ForeignKey from sqlalchemy import Integer, DateTime from sqlalchemy.orm import Mapped, declarative_base from sqlalchemy.orm import relationship from sqlalchemy.orm import Session Base = declarative_base() engine = create_engine("sqlite://", echo=True) class User(Base): __tablename__ = "user" id = Column(Integer, primary_key=True) class Child(Base): __tablename__ = "child" id = Column(Integer, primary_key=True) created_by_id = Column( Integer, ForeignKey("user.id"), ) created_by: Mapped[User] = relationship( post_update=True, ) updated_at = Column( DateTime(), nullable=False, default=lambda: datetime.now(), ) parent_id = Column( Integer, ForeignKey("parent.id"), ) parent: Mapped["Parent"] = relationship(back_populates="children") created_at = Column(DateTime, default=datetime.now) __mapper_args__ = { "version_id_col": updated_at, "version_id_generator": lambda version: datetime.now(), } class Parent(Base): __tablename__ = "parent" id = Column(Integer, primary_key=True) children: Mapped[List[Child]] = relationship( cascade="all, delete", back_populates="parent" ) Base.metadata.create_all(engine) with Session(engine) as session: u1 = User(id=1) session.add(u1) p1 = Parent(id=1) session.add(p1) session.commit() c1 = Child(id=1, parent=p1, created_by=u1) session.add(c1) c2 = Child(id=2, parent=p1, created_by=u1) session.add(c2) session.commit() session.delete(p1) session.commit()Error
2025-07-23 10:24:36,841 INFO sqlalchemy.engine.Engine BEGIN (implicit) 2025-07-23 10:24:36,841 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("user") 2025-07-23 10:24:36,841 INFO sqlalchemy.engine.Engine [raw sql] () 2025-07-23 10:24:36,842 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("user") 2025-07-23 10:24:36,842 INFO sqlalchemy.engine.Engine [raw sql] () 2025-07-23 10:24:36,842 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("child") 2025-07-23 10:24:36,842 INFO sqlalchemy.engine.Engine [raw sql] () 2025-07-23 10:24:36,842 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("child") 2025-07-23 10:24:36,842 INFO sqlalchemy.engine.Engine [raw sql] () 2025-07-23 10:24:36,842 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("parent") 2025-07-23 10:24:36,842 INFO sqlalchemy.engine.Engine [raw sql] () 2025-07-23 10:24:36,842 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("parent") 2025-07-23 10:24:36,842 INFO sqlalchemy.engine.Engine [raw sql] () 2025-07-23 10:24:36,842 INFO sqlalchemy.engine.Engine CREATE TABLE user ( id INTEGER NOT NULL, PRIMARY KEY (id) ) 2025-07-23 10:24:36,842 INFO sqlalchemy.engine.Engine [no key 0.00006s] () 2025-07-23 10:24:36,843 INFO sqlalchemy.engine.Engine CREATE TABLE parent ( id INTEGER NOT NULL, PRIMARY KEY (id) ) 2025-07-23 10:24:36,843 INFO sqlalchemy.engine.Engine [no key 0.00006s] () 2025-07-23 10:24:36,843 INFO sqlalchemy.engine.Engine CREATE TABLE child ( id INTEGER NOT NULL, created_by_id INTEGER, updated_at DATETIME NOT NULL, parent_id INTEGER, created_at DATETIME, PRIMARY KEY (id), FOREIGN KEY(created_by_id) REFERENCES user (id), FOREIGN KEY(parent_id) REFERENCES parent (id) ) 2025-07-23 10:24:36,843 INFO sqlalchemy.engine.Engine [no key 0.00016s] () 2025-07-23 10:24:36,843 INFO sqlalchemy.engine.Engine COMMIT 2025-07-23 10:24:36,845 INFO sqlalchemy.engine.Engine BEGIN (implicit) 2025-07-23 10:24:36,846 INFO sqlalchemy.engine.Engine INSERT INTO parent (id) VALUES (?) 2025-07-23 10:24:36,846 INFO sqlalchemy.engine.Engine [generated in 0.00010s] (1,) 2025-07-23 10:24:36,847 INFO sqlalchemy.engine.Engine INSERT INTO user (id) VALUES (?) 2025-07-23 10:24:36,847 INFO sqlalchemy.engine.Engine [generated in 0.00010s] (1,) 2025-07-23 10:24:36,847 INFO sqlalchemy.engine.Engine COMMIT 2025-07-23 10:24:36,847 INFO sqlalchemy.engine.Engine BEGIN (implicit) 2025-07-23 10:24:36,848 INFO sqlalchemy.engine.Engine SELECT parent.id AS parent_id FROM parent WHERE parent.id = ? 2025-07-23 10:24:36,849 INFO sqlalchemy.engine.Engine [generated in 0.00012s] (1,) 2025-07-23 10:24:36,849 INFO sqlalchemy.engine.Engine INSERT INTO child (id, created_by_id, updated_at, parent_id, created_at) VALUES (?, ?, ?, ?, ?) 2025-07-23 10:24:36,849 INFO sqlalchemy.engine.Engine [generated in 0.00014s] [(1, None, '2025-07-23 10:24:36.849389', 1, '2025-07-23 10:24:36.849569'), (2, None, '2025-07-23 10:24:36.849395', 1, '2025-07-23 10:24:36.849570')] 2025-07-23 10:24:36,850 INFO sqlalchemy.engine.Engine SELECT user.id AS user_id FROM user WHERE user.id = ? 2025-07-23 10:24:36,850 INFO sqlalchemy.engine.Engine [generated in 0.00014s] (1,) 2025-07-23 10:24:36,851 INFO sqlalchemy.engine.Engine UPDATE child SET created_by_id=? WHERE child.id = ? AND child.updated_at = ? 2025-07-23 10:24:36,851 INFO sqlalchemy.engine.Engine [generated in 0.00011s] [(1, 1, '2025-07-23 10:24:36.849389'), (1, 2, '2025-07-23 10:24:36.849395')] 2025-07-23 10:24:36,851 INFO sqlalchemy.engine.Engine COMMIT 2025-07-23 10:24:36,852 INFO sqlalchemy.engine.Engine BEGIN (implicit) 2025-07-23 10:24:36,852 INFO sqlalchemy.engine.Engine SELECT parent.id AS parent_id FROM parent WHERE parent.id = ? 2025-07-23 10:24:36,852 INFO sqlalchemy.engine.Engine [generated in 0.00012s] (1,) 2025-07-23 10:24:36,853 INFO sqlalchemy.engine.Engine SELECT child.id AS child_id, child.created_by_id AS child_created_by_id, child.updated_at AS child_updated_at, child.parent_id AS child_parent_id, child.created_at AS child_created_at FROM child WHERE ? = child.parent_id 2025-07-23 10:24:36,853 INFO sqlalchemy.engine.Engine [generated in 0.00013s] (1,) 2025-07-23 10:24:36,853 INFO sqlalchemy.engine.Engine SELECT user.id AS user_id FROM user WHERE user.id = ? 2025-07-23 10:24:36,854 INFO sqlalchemy.engine.Engine [cached since 0.003617s ago] (1,) 2025-07-23 10:24:36,854 INFO sqlalchemy.engine.Engine UPDATE child SET created_by_id=?, updated_at=? WHERE child.id = ? AND child.updated_at = ? 2025-07-23 10:24:36,854 INFO sqlalchemy.engine.Engine [generated in 0.00012s] [(None, '2025-07-23 10:24:36.854267', 1, '2025-07-23 10:24:36.849389'), (None, '2025-07-23 10:24:36.854279', 2, '2025-07-23 10:24:36.849395')] 2025-07-23 10:24:36,855 INFO sqlalchemy.engine.Engine DELETE FROM child WHERE child.id = ? AND child.updated_at = ? 2025-07-23 10:24:36,855 INFO sqlalchemy.engine.Engine [generated in 0.00015s] [(1, '2025-07-23 10:24:36.854267'), (2, '2025-07-23 10:24:36.854267')] 2025-07-23 10:24:36,855 INFO sqlalchemy.engine.Engine ROLLBACK Traceback (most recent call last): File "C:\Users\JackSormaz\dev\sqlalchemy_testing\issue_10800.py", line 77, in <module> session.commit() ~~~~~~~~~~~~~~^^ File "C:\Users\JackSormaz\dev\sqlalchemy_testing\.venv3.13\Lib\site-packages\sqlalchemy\orm\session.py", line 2028, in commit trans.commit(_to_root=True) ~~~~~~~~~~~~^^^^^^^^^^^^^^^ File "<string>", line 2, in commit File "C:\Users\JackSormaz\dev\sqlalchemy_testing\.venv3.13\Lib\site-packages\sqlalchemy\orm\state_changes.py", line 139, in _go ret_value = fn(self, *arg, **kw) File "C:\Users\JackSormaz\dev\sqlalchemy_testing\.venv3.13\Lib\site-packages\sqlalchemy\orm\session.py", line 1313, in commit self._prepare_impl() ~~~~~~~~~~~~~~~~~~^^ File "<string>", line 2, in _prepare_impl File "C:\Users\JackSormaz\dev\sqlalchemy_testing\.venv3.13\Lib\site-packages\sqlalchemy\orm\state_changes.py", line 139, in _go ret_value = fn(self, *arg, **kw) File "C:\Users\JackSormaz\dev\sqlalchemy_testing\.venv3.13\Lib\site-packages\sqlalchemy\orm\session.py", line 1288, in _prepare_impl self.session.flush() ~~~~~~~~~~~~~~~~~~^^ File "C:\Users\JackSormaz\dev\sqlalchemy_testing\.venv3.13\Lib\site-packages\sqlalchemy\orm\session.py", line 4352, in flush self._flush(objects) ~~~~~~~~~~~^^^^^^^^^ File "C:\Users\JackSormaz\dev\sqlalchemy_testing\.venv3.13\Lib\site-packages\sqlalchemy\orm\session.py", line 4487, in _flush with util.safe_reraise(): ~~~~~~~~~~~~~~~~~^^ File "C:\Users\JackSormaz\dev\sqlalchemy_testing\.venv3.13\Lib\site-packages\sqlalchemy\util\langhelpers.py", line 146, in __exit__ raise exc_value.with_traceback(exc_tb) File "C:\Users\JackSormaz\dev\sqlalchemy_testing\.venv3.13\Lib\site-packages\sqlalchemy\orm\session.py", line 4448, in _flush flush_context.execute() ~~~~~~~~~~~~~~~~~~~~~^^ File "C:\Users\JackSormaz\dev\sqlalchemy_testing\.venv3.13\Lib\site-packages\sqlalchemy\orm\unitofwork.py", line 466, in execute rec.execute(self) ~~~~~~~~~~~^^^^^^ File "C:\Users\JackSormaz\dev\sqlalchemy_testing\.venv3.13\Lib\site-packages\sqlalchemy\orm\unitofwork.py", line 679, in execute util.preloaded.orm_persistence.delete_obj( ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^ self.mapper, ^^^^^^^^^^^^ uow.states_for_mapper_hierarchy(self.mapper, True, False), ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ uow, ^^^^ ) ^ File "C:\Users\JackSormaz\dev\sqlalchemy_testing\.venv3.13\Lib\site-packages\sqlalchemy\orm\persistence.py", line 193, in delete_obj _emit_delete_statements( ~~~~~~~~~~~~~~~~~~~~~~~^ base_mapper, ^^^^^^^^^^^^ ...<3 lines>... delete, ^^^^^^^ ) ^ File "C:\Users\JackSormaz\dev\sqlalchemy_testing\.venv3.13\Lib\site-packages\sqlalchemy\orm\persistence.py", line 1494, in _emit_delete_statements raise orm_exc.StaleDataError( ...<5 lines>... ) sqlalchemy.orm.exc.StaleDataError: DELETE statement on table 'child' expected to delete 2 row(s); 1 were matched. Please set confirm_deleted_rows=False within the mapper configuration to prevent this warning. Additional context
No response