It was in fact a one liner, so you can go back to your original code if you use the latest 0.6 tip:
http://hg.sqlalchemy.org/sqlalchemy/archive/rel_0_6.tar.gz thanks for the bug report ! On Dec 15, 2010, at 3:41 PM, Will Weaver wrote: > Wow, this has been a problem for me for the past 3 or 4 days and took > a while to get to that example. Defining the backrefs or the > relationships in the opposite direction did the job. I had > intentionally left out some of the backreffed relationships because I > didn't need them for what I was working on, but it definitely is worth > it to get this working. > > Thanks a lot. > > -Will > > On Wed, Dec 15, 2010 at 2:45 PM, Michael Bayer <mike...@zzzcomputing.com> > wrote: >> This is an interesting edge case and I can probably ensure that the >> dependency between Parent/Child is present in the unit of work even if there >> is no known linkage at the Child.parent level for the objects actually >> present - ticket #2002 is added for this. >> >> In the meantime, the uow needs to be aware of the linkage between >> Parent->Child when flush occurs. Adding a backref "children" to the parent >> relationship will do it, or ensuring that child.parent is accessed before >> emitting the flush will do it. The usual way this kind of delete is >> performed is the "delete" cascade is added to the "children" backref, then >> the Parent is deleted alone, the deletes cascading to the Child objects >> naturally. >> >> But this is a fun bug and I'll probably have a fix very soon, perhaps in 10 >> minutes or maybe not. >> >> >> >> >> On Dec 15, 2010, at 2:17 PM, Will 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. >>> >> >> -- >> 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. >> >> > > -- > 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. > -- 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.
