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.
