An update. This problem does occur with sqlite it's just that sqlite doesn't enforce the foreign key so it doesn't throw an exception.
# output that deletes in the proper order 2010-12-15 14:33:52,197 INFO sqlalchemy.engine.base.Engine.0x...d050 BEGIN (implicit) 2010-12-15 14:33:52,197 INFO sqlalchemy.engine.base.Engine.0x...d050 INSERT INTO parents DEFAULT VALUES 2010-12-15 14:33:52,197 INFO sqlalchemy.engine.base.Engine.0x...d050 () 2010-12-15 14:33:52,198 INFO sqlalchemy.engine.base.Engine.0x...d050 INSERT INTO children (parent_id) VALUES (?) 2010-12-15 14:33:52,198 INFO sqlalchemy.engine.base.Engine.0x...d050 (1,) 2010-12-15 14:33:52,198 INFO sqlalchemy.engine.base.Engine.0x...d050 INSERT INTO children (parent_id) VALUES (?) 2010-12-15 14:33:52,198 INFO sqlalchemy.engine.base.Engine.0x...d050 (1,) 2010-12-15 14:33:52,198 INFO sqlalchemy.engine.base.Engine.0x...d050 COMMIT 2010-12-15 14:33:52,199 INFO sqlalchemy.engine.base.Engine.0x...d050 BEGIN (implicit) 2010-12-15 14:33:52,200 INFO sqlalchemy.engine.base.Engine.0x...d050 SELECT children.id AS children_id, children.parent_id AS children_parent_id FROM children WHERE children.id = ? 2010-12-15 14:33:52,200 INFO sqlalchemy.engine.base.Engine.0x...d050 (1,) 2010-12-15 14:33:52,200 INFO sqlalchemy.engine.base.Engine.0x...d050 SELECT children.id AS children_id, children.parent_id AS children_parent_id FROM children WHERE children.id = ? 2010-12-15 14:33:52,201 INFO sqlalchemy.engine.base.Engine.0x...d050 (2,) 2010-12-15 14:33:52,201 INFO sqlalchemy.engine.base.Engine.0x...d050 DELETE FROM children WHERE children.id = ? 2010-12-15 14:33:52,201 INFO sqlalchemy.engine.base.Engine.0x...d050 ((1,), (2,)) 2010-12-15 14:33:52,202 INFO sqlalchemy.engine.base.Engine.0x...d050 SELECT parents.id AS parents_id FROM parents WHERE parents.id = ? 2010-12-15 14:33:52,202 INFO sqlalchemy.engine.base.Engine.0x...d050 (1,) 2010-12-15 14:33:52,203 INFO sqlalchemy.engine.base.Engine.0x...d050 DELETE FROM parents WHERE parents.id = ? 2010-12-15 14:33:52,203 INFO sqlalchemy.engine.base.Engine.0x...d050 (1,) 2010-12-15 14:33:52,203 INFO sqlalchemy.engine.base.Engine.0x...d050 COMMIT # output that deletes in the wrong order 2010-12-15 14:33:56,691 INFO sqlalchemy.engine.base.Engine.0x...6050 BEGIN (implicit) 2010-12-15 14:33:56,692 INFO sqlalchemy.engine.base.Engine.0x...6050 INSERT INTO parents DEFAULT VALUES 2010-12-15 14:33:56,692 INFO sqlalchemy.engine.base.Engine.0x...6050 () 2010-12-15 14:33:56,693 INFO sqlalchemy.engine.base.Engine.0x...6050 INSERT INTO children (parent_id) VALUES (?) 2010-12-15 14:33:56,693 INFO sqlalchemy.engine.base.Engine.0x...6050 (1,) 2010-12-15 14:33:56,693 INFO sqlalchemy.engine.base.Engine.0x...6050 INSERT INTO children (parent_id) VALUES (?) 2010-12-15 14:33:56,693 INFO sqlalchemy.engine.base.Engine.0x...6050 (1,) 2010-12-15 14:33:56,693 INFO sqlalchemy.engine.base.Engine.0x...6050 COMMIT 2010-12-15 14:33:56,694 INFO sqlalchemy.engine.base.Engine.0x...6050 BEGIN (implicit) 2010-12-15 14:33:56,695 INFO sqlalchemy.engine.base.Engine.0x...6050 SELECT parents.id AS parents_id FROM parents WHERE parents.id = ? 2010-12-15 14:33:56,695 INFO sqlalchemy.engine.base.Engine.0x...6050 (1,) 2010-12-15 14:33:56,695 INFO sqlalchemy.engine.base.Engine.0x...6050 DELETE FROM parents WHERE parents.id = ? 2010-12-15 14:33:56,695 INFO sqlalchemy.engine.base.Engine.0x...6050 (1,) 2010-12-15 14:33:56,696 INFO sqlalchemy.engine.base.Engine.0x...6050 SELECT children.id AS children_id, children.parent_id AS children_parent_id FROM children WHERE children.id = ? 2010-12-15 14:33:56,696 INFO sqlalchemy.engine.base.Engine.0x...6050 (1,) 2010-12-15 14:33:56,697 INFO sqlalchemy.engine.base.Engine.0x...6050 SELECT children.id AS children_id, children.parent_id AS children_parent_id FROM children WHERE children.id = ? 2010-12-15 14:33:56,697 INFO sqlalchemy.engine.base.Engine.0x...6050 (2,) 2010-12-15 14:33:56,697 INFO sqlalchemy.engine.base.Engine.0x...6050 DELETE FROM children WHERE children.id = ? 2010-12-15 14:33:56,697 INFO sqlalchemy.engine.base.Engine.0x...6050 ((1,), (2,)) 2010-12-15 14:33:56,697 INFO sqlalchemy.engine.base.Engine.0x...6050 COMMIT On Dec 15, 2:17 pm, Will <willman...@gmail.com> wrote: > """ > Hello, > > I've been recently having a problem with sqlalchemy not flushing > deletes in the proper order. I've created a simple example for the > problem that has been occuring. I tried to run this using sqlite and > it doesn't have any problems, it is only with Postgresql. > > One thing of note is that if there is only one Child it doesn't seem > to > have a problem, only when there are multiple children. Not sure if > that makes a difference in the SQLAlchemy code. > """ > > from sqlalchemy import create_engine > from sqlalchemy.ext.declarative import declarative_base > from sqlalchemy.orm import scoped_session, sessionmaker, relationship > from sqlalchemy.schema import Column, ForeignKey > from sqlalchemy.types import Integer > > #engine = create_engine('sqlite:///') > engine = create_engine('postgresql://test_runner@/testing_db') > > Model = declarative_base() > > class Parent(Model): > __tablename__ = 'parents' > > id = Column(Integer, primary_key=True) > > class Child(Model): > __tablename__ = 'children' > > id = Column(Integer, primary_key=True) > parent_id = Column(Integer, ForeignKey('parents.id'), > nullable=False) > parent = relationship('Parent') > > def begin(): > """Begin transaction""" > #global transaction > #transaction = session.begin() > > session.begin() > > def commit(): > """Commit transaction""" > #global transaction > #transaction.commit() > > session.commit() > > Model.metadata.create_all(engine) > > parent = Parent() > children = [Child(parent=parent), Child(parent=parent)] > > Session = sessionmaker(bind=engine, autocommit=True) > session = Session() > > try: > session.bind.echo = True > > begin() > session.add_all(children) > session.add(parent) > commit() > > begin() > for child in children: > session.delete(child) > session.delete(parent) > commit() > > session.bind.echo = False > finally: > Model.metadata.drop_all(engine) > > """ > From running the script I have two different outputs because it seems > to run the deletes in a random order so subsequent runs will behave > differently. > """ > > # Example Failed Run > """ > 2010-12-15 13:45:05,050 INFO sqlalchemy.engine.base.Engine.0x...f5d0 > BEGIN (implicit) > 2010-12-15 13:45:05,051 INFO sqlalchemy.engine.base.Engine.0x...f5d0 > INSERT INTO parents DEFAULT VALUES RETURNING parents.id > 2010-12-15 13:45:05,051 INFO sqlalchemy.engine.base.Engine.0x...f5d0 > {} > 2010-12-15 13:45:05,052 INFO sqlalchemy.engine.base.Engine.0x...f5d0 > INSERT INTO children (parent_id) VALUES (%(parent_id)s) RETURNING > children.id > 2010-12-15 13:45:05,052 INFO sqlalchemy.engine.base.Engine.0x...f5d0 > {'parent_id': 1} > 2010-12-15 13:45:05,053 INFO sqlalchemy.engine.base.Engine.0x...f5d0 > INSERT INTO children (parent_id) VALUES (%(parent_id)s) RETURNING > children.id > 2010-12-15 13:45:05,054 INFO sqlalchemy.engine.base.Engine.0x...f5d0 > {'parent_id': 1} > 2010-12-15 13:45:05,054 INFO sqlalchemy.engine.base.Engine.0x...f5d0 > COMMIT > 2010-12-15 13:45:05,055 INFO sqlalchemy.engine.base.Engine.0x...f5d0 > BEGIN (implicit) > 2010-12-15 13:45:05,056 INFO sqlalchemy.engine.base.Engine.0x...f5d0 > SELECT parents.id AS parents_id > FROM parents > WHERE parents.id = %(param_1)s > 2010-12-15 13:45:05,056 INFO sqlalchemy.engine.base.Engine.0x...f5d0 > {'param_1': 1} > 2010-12-15 13:45:05,057 INFO sqlalchemy.engine.base.Engine.0x...f5d0 > DELETE FROM parents WHERE parents.id = %(id)s > 2010-12-15 13:45:05,057 INFO sqlalchemy.engine.base.Engine.0x...f5d0 > {'id': 1} > 2010-12-15 13:45:05,058 INFO sqlalchemy.engine.base.Engine.0x...f5d0 > ROLLBACK > 2010-12-15 13:45:05,061 INFO sqlalchemy.engine.base.Engine.0x...f5d0 > select relname from pg_class c join pg_namespace n on > n.oid=c.relnamespace where n.nspname=current_schema() and > lower(relname)=%(name)s > 2010-12-15 13:45:05,061 INFO sqlalchemy.engine.base.Engine.0x...f5d0 > {'name': u'children'} > 2010-12-15 13:45:05,063 INFO sqlalchemy.engine.base.Engine.0x...f5d0 > select relname from pg_class c join pg_namespace n on > n.oid=c.relnamespace where n.nspname=current_schema() and > lower(relname)=%(name)s > 2010-12-15 13:45:05,063 INFO sqlalchemy.engine.base.Engine.0x...f5d0 > {'name': u'parents'} > 2010-12-15 13:45:05,064 INFO sqlalchemy.engine.base.Engine.0x...f5d0 > DROP TABLE children > 2010-12-15 13:45:05,064 INFO sqlalchemy.engine.base.Engine.0x...f5d0 > {} > 2010-12-15 13:45:05,066 INFO sqlalchemy.engine.base.Engine.0x...f5d0 > COMMIT > 2010-12-15 13:45:05,067 INFO sqlalchemy.engine.base.Engine.0x...f5d0 > DROP TABLE parents > 2010-12-15 13:45:05,067 INFO sqlalchemy.engine.base.Engine.0x...f5d0 > {} > 2010-12-15 13:45:05,068 INFO sqlalchemy.engine.base.Engine.0x...f5d0 > COMMIT > Traceback (most recent call last): > File "sharded_session_issue.py", line 64, in <module> > commit(session) > File "sharded_session_issue.py", line 47, in commit > session.commit() > File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/session.py", line > 623, in commit > self.transaction.commit() > File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/session.py", line > 385, in commit > self._prepare_impl() > File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/session.py", line > 369, in _prepare_impl > self.session.flush() > File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/session.py", line > 1397, in flush > self._flush(objects) > File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/session.py", line > 1478, in _flush > flush_context.execute() > File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/unitofwork.py", > line 304, in execute > rec.execute(self) > File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/unitofwork.py", > line 471, in execute > uow > File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/mapper.py", line > 2076, in _delete_obj > c = connection.execute(statement, del_objects) > File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/base.py", line > 1191, in execute > params) > File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/base.py", line > 1271, in _execute_clauseelement > return self.__execute_context(context) > File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/base.py", line > 1302, in __execute_context > context.parameters[0], context=context) > File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/base.py", line > 1401, in _cursor_execute > context) > File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/base.py", line > 1394, in _cursor_execute > context) > File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/default.py", > line 299, in do_execute > cursor.execute(statement, parameters) > sqlalchemy.exc.IntegrityError: (IntegrityError) update or delete on > table "parents" violates foreign key constraint > "children_parent_id_fkey" on table "children" > DETAIL: Key (id)=(1) is still referenced from table "children". > 'DELETE FROM parents WHERE parents.id = %(id)s' {'id': 1} > """ > > # Example Successful Run > """ > 2010-12-15 13:45:40,471 INFO sqlalchemy.engine.base.Engine.0x...95d0 > BEGIN (implicit) > 2010-12-15 13:45:40,472 INFO sqlalchemy.engine.base.Engine.0x...95d0 > INSERT INTO parents DEFAULT VALUES RETURNING parents.id > 2010-12-15 13:45:40,472 INFO sqlalchemy.engine.base.Engine.0x...95d0 > {} > 2010-12-15 13:45:40,473 INFO sqlalchemy.engine.base.Engine.0x...95d0 > INSERT INTO children (parent_id) VALUES (%(parent_id)s) RETURNING > children.id > 2010-12-15 13:45:40,473 INFO sqlalchemy.engine.base.Engine.0x...95d0 > {'parent_id': 1} > 2010-12-15 13:45:40,474 INFO sqlalchemy.engine.base.Engine.0x...95d0 > INSERT INTO children (parent_id) VALUES (%(parent_id)s) RETURNING > children.id > 2010-12-15 13:45:40,474 INFO sqlalchemy.engine.base.Engine.0x...95d0 > {'parent_id': 1} > 2010-12-15 13:45:40,475 INFO sqlalchemy.engine.base.Engine.0x...95d0 > COMMIT > 2010-12-15 13:45:40,476 INFO sqlalchemy.engine.base.Engine.0x...95d0 > BEGIN (implicit) > 2010-12-15 13:45:40,477 INFO sqlalchemy.engine.base.Engine.0x...95d0 > SELECT children.id AS children_id, children.parent_id AS > children_parent_id > FROM children > WHERE children.id = %(param_1)s > 2010-12-15 13:45:40,477 INFO sqlalchemy.engine.base.Engine.0x...95d0 > {'param_1': 1} > 2010-12-15 13:45:40,478 INFO sqlalchemy.engine.base.Engine.0x...95d0 > SELECT children.id AS children_id, children.parent_id AS > children_parent_id > FROM children > WHERE children.id = %(param_1)s > 2010-12-15 13:45:40,478 INFO sqlalchemy.engine.base.Engine.0x...95d0 > {'param_1': 2} > 2010-12-15 13:45:40,479 INFO sqlalchemy.engine.base.Engine.0x...95d0 > DELETE FROM children WHERE children.id = %(id)s > 2010-12-15 13:45:40,479 INFO sqlalchemy.engine.base.Engine.0x...95d0 > ({'id': 1}, {'id': 2}) > 2010-12-15 13:45:40,480 INFO sqlalchemy.engine.base.Engine.0x...95d0 > SELECT parents.id AS parents_id > FROM parents > WHERE parents.id = %(param_1)s > 2010-12-15 13:45:40,480 INFO sqlalchemy.engine.base.Engine.0x...95d0 > {'param_1': 1} > 2010-12-15 13:45:40,481 INFO sqlalchemy.engine.base.Engine.0x...95d0 > DELETE FROM parents WHERE parents.id = %(id)s > 2010-12-15 13:45:40,481 INFO sqlalchemy.engine.base.Engine.0x...95d0 > {'id': 1} > 2010-12-15 13:45:40,482 INFO sqlalchemy.engine.base.Engine.0x...95d0 > COMMIT > """ -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.