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.
