[Sqlalchemy-tickets] [sqlalchemy] #2921: backref relationships with cascade delete-orphan throw Int

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

#2921: backref relationships with cascade delete-orphan throw IntegrityError when
child is replaced
--------------------+-----------------------------------------
 Reporter:  elmer   |           Owner:  zzzeek
     Type:  defect  |          Status:  new
 Priority:  medium  |       Milestone:
Component:  (none)  |        Severity:  no triage selected yet
 Keywords:          |  Progress State:  awaiting triage
--------------------+-----------------------------------------
 In a two-table schema, with a parent table that holds a reference to a
 child that may only have a single parent, assigning a new child to the
 parent seems to break because of autoflushing. While the error is
 avoidable by suppressing autoflushing, given that this is (for the user) a
 single statement, it seems like something that should not happen.

 Script to test this is enclosed (first run with 'initial' as argument,
 then either 'break' or 'succeed'.

 Software versions:
 * SQLAlchemy version: 0.9.1
 * Postgres version: 9.1.11

 Complete log of queries emitted and final traceback from running the
 'break' action of the attached script:
 {{{
 2014-01-27 15:47:32,906 INFO sqlalchemy.engine.base.Engine select
 version()
 2014-01-27 15:47:32,907 INFO sqlalchemy.engine.base.Engine {}
 2014-01-27 15:47:32,909 INFO sqlalchemy.engine.base.Engine select
 current_schema()
 2014-01-27 15:47:32,909 INFO sqlalchemy.engine.base.Engine {}
 2014-01-27 15:47:32,913 INFO sqlalchemy.engine.base.Engine show
 standard_conforming_strings
 2014-01-27 15:47:32,913 INFO sqlalchemy.engine.base.Engine {}
 2014-01-27 15:47:32,914 INFO sqlalchemy.engine.base.Engine select relname
 from pg_class c join pg_namespace n on n.oid=c.relnamespace where
 n.nspname=current_schema() and relname=%(name)s
 2014-01-27 15:47:32,915 INFO sqlalchemy.engine.base.Engine {'name':
 u'a_child'}
 2014-01-27 15:47:32,916 INFO sqlalchemy.engine.base.Engine select relname
 from pg_class c join pg_namespace n on n.oid=c.relnamespace where
 n.nspname=current_schema() and relname=%(name)s
 2014-01-27 15:47:32,916 INFO sqlalchemy.engine.base.Engine {'name':
 u'a_parent'}
 2014-01-27 15:47:32,926 INFO sqlalchemy.engine.base.Engine BEGIN
 (implicit)
 2014-01-27 15:47:32,928 INFO sqlalchemy.engine.base.Engine SELECT
 a_parent.id AS a_parent_id, a_parent.name AS a_parent_name,
 a_parent.child_id AS a_parent_child_id
 FROM a_parent
  LIMIT %(param_1)s
 2014-01-27 15:47:32,928 INFO sqlalchemy.engine.base.Engine {'param_1': 1}
 /home/elmer/sqltest/env/local/lib/python2.7/site-
 packages/sqlalchemy/engine/default.py:537: SAWarning: Unicode type
 received non-unicode bind param value.
   processors[key](compiled_params[key])
 2014-01-27 15:47:32,933 INFO sqlalchemy.engine.base.Engine INSERT INTO
 a_child (location) VALUES (%(location)s) RETURNING a_child.id
 2014-01-27 15:47:32,934 INFO sqlalchemy.engine.base.Engine {'location':
 'Elsewhere'}
 2014-01-27 15:47:32,937 INFO sqlalchemy.engine.base.Engine SELECT
 a_child.id AS a_child_id, a_child.location AS a_child_location
 FROM a_child
 WHERE a_child.id = %(param_1)s
 2014-01-27 15:47:32,937 INFO sqlalchemy.engine.base.Engine {'param_1': 15}
 2014-01-27 15:47:32,940 INFO sqlalchemy.engine.base.Engine SELECT
 a_parent.id AS a_parent_id, a_parent.name AS a_parent_name,
 a_parent.child_id AS a_parent_child_id
 FROM a_parent
 WHERE %(param_1)s = a_parent.child_id
 2014-01-27 15:47:32,940 INFO sqlalchemy.engine.base.Engine {'param_1': 15}
 2014-01-27 15:47:32,945 INFO sqlalchemy.engine.base.Engine UPDATE a_parent
 SET child_id=%(child_id)s WHERE a_parent.id = %(a_parent_id)s
 2014-01-27 15:47:32,945 INFO sqlalchemy.engine.base.Engine {'child_id':
 None, 'a_parent_id': 2}
 2014-01-27 15:47:32,947 INFO sqlalchemy.engine.base.Engine ROLLBACK
 Traceback (most recent call last):
   File "test.py", line 70, in <module>
     parent.child = child # Wrongness happens here
   File "/home/elmer/sqltest/env/local/lib/python2.7/site-
 packages/sqlalchemy/orm/attributes.py", line 220, in __set__
     instance_dict(instance), value, None)
   File "/home/elmer/sqltest/env/local/lib/python2.7/site-
 packages/sqlalchemy/orm/attributes.py", line 780, in set
     value = self.fire_replace_event(state, dict_, value, old, initiator)
   File "/home/elmer/sqltest/env/local/lib/python2.7/site-
 packages/sqlalchemy/orm/attributes.py", line 801, in fire_replace_event
     value = fn(state, value, previous, initiator or self._replace_token)
   File "/home/elmer/sqltest/env/local/lib/python2.7/site-
 packages/sqlalchemy/orm/attributes.py", line 1112, in
 emit_backref_from_scalar_set_event
     passive=PASSIVE_NO_FETCH)
   File "/home/elmer/sqltest/env/local/lib/python2.7/site-
 packages/sqlalchemy/orm/attributes.py", line 604, in append
     self.set(state, dict_, value, initiator, passive=passive)
   File "/home/elmer/sqltest/env/local/lib/python2.7/site-
 packages/sqlalchemy/orm/attributes.py", line 764, in set
     old = self.get(state, dict_, passive=PASSIVE_ONLY_PERSISTENT)
   File "/home/elmer/sqltest/env/local/lib/python2.7/site-
 packages/sqlalchemy/orm/attributes.py", line 579, in get
     value = self.callable_(state, passive)
   File "/home/elmer/sqltest/env/local/lib/python2.7/site-
 packages/sqlalchemy/orm/strategies.py", line 505, in _load_for_state
     return self._emit_lazyload(session, state, ident_key, passive)
   File "<string>", line 1, in <lambda>
   File "/home/elmer/sqltest/env/local/lib/python2.7/site-
 packages/sqlalchemy/orm/strategies.py", line 567, in _emit_lazyload
     result = q.all()
   File "/home/elmer/sqltest/env/local/lib/python2.7/site-
 packages/sqlalchemy/orm/query.py", line 2280, in all
     return list(self)
   File "/home/elmer/sqltest/env/local/lib/python2.7/site-
 packages/sqlalchemy/orm/query.py", line 2391, in __iter__
     self.session._autoflush()
   File "/home/elmer/sqltest/env/local/lib/python2.7/site-
 packages/sqlalchemy/orm/session.py", line 1194, in _autoflush
     util.raise_from_cause(e)
   File "/home/elmer/sqltest/env/local/lib/python2.7/site-
 packages/sqlalchemy/util/compat.py", line 185, in raise_from_cause
     reraise(type(exception), exception, tb=exc_tb)
   File "/home/elmer/sqltest/env/local/lib/python2.7/site-
 packages/sqlalchemy/orm/session.py", line 1184, in _autoflush
     self.flush()
   File "/home/elmer/sqltest/env/local/lib/python2.7/site-
 packages/sqlalchemy/orm/session.py", line 1879, in flush
     self._flush(objects)
   File "/home/elmer/sqltest/env/local/lib/python2.7/site-
 packages/sqlalchemy/orm/session.py", line 1997, in _flush
     transaction.rollback(_capture_exception=True)
   File "/home/elmer/sqltest/env/local/lib/python2.7/site-
 packages/sqlalchemy/util/langhelpers.py", line 57, in __exit__
     compat.reraise(exc_type, exc_value, exc_tb)
   File "/home/elmer/sqltest/env/local/lib/python2.7/site-
 packages/sqlalchemy/orm/session.py", line 1961, in _flush
     flush_context.execute()
   File "/home/elmer/sqltest/env/local/lib/python2.7/site-
 packages/sqlalchemy/orm/unitofwork.py", line 370, in execute
     rec.execute(self)
   File "/home/elmer/sqltest/env/local/lib/python2.7/site-
 packages/sqlalchemy/orm/unitofwork.py", line 523, in execute
     uow
   File "/home/elmer/sqltest/env/local/lib/python2.7/site-
 packages/sqlalchemy/orm/persistence.py", line 59, in save_obj
     mapper, table, update)
   File "/home/elmer/sqltest/env/local/lib/python2.7/site-
 packages/sqlalchemy/orm/persistence.py", line 510, in
 _emit_update_statements
     execute(statement, params)
   File "/home/elmer/sqltest/env/local/lib/python2.7/site-
 packages/sqlalchemy/engine/base.py", line 664, in execute
     return meth(self, multiparams, params)
   File "/home/elmer/sqltest/env/local/lib/python2.7/site-
 packages/sqlalchemy/sql/elements.py", line 282, in _execute_on_connection
     return connection._execute_clauseelement(self, multiparams, params)
   File "/home/elmer/sqltest/env/local/lib/python2.7/site-
 packages/sqlalchemy/engine/base.py", line 761, in _execute_clauseelement
     compiled_sql, distilled_params
   File "/home/elmer/sqltest/env/local/lib/python2.7/site-
 packages/sqlalchemy/engine/base.py", line 874, in _execute_context
     context)
   File "/home/elmer/sqltest/env/local/lib/python2.7/site-
 packages/sqlalchemy/engine/base.py", line 1023, in _handle_dbapi_exception
     exc_info
   File "/home/elmer/sqltest/env/local/lib/python2.7/site-
 packages/sqlalchemy/util/compat.py", line 185, in raise_from_cause
     reraise(type(exception), exception, tb=exc_tb)
   File "/home/elmer/sqltest/env/local/lib/python2.7/site-
 packages/sqlalchemy/engine/base.py", line 867, in _execute_context
     context)
   File "/home/elmer/sqltest/env/local/lib/python2.7/site-
 packages/sqlalchemy/engine/default.py", line 388, in do_execute
     cursor.execute(statement, parameters)
 sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked
 autoflush; consider using a session.no_autoflush block if this flush is
 occuring prematurely) (IntegrityError) null value in column "child_id"
 violates not-null constraint
  'UPDATE a_parent SET child_id=%(child_id)s WHERE a_parent.id =
 %(a_parent_id)s' {'child_id': None, 'a_parent_id': 2}
 }}}

 Removing the delete-orphan AND single_parent=True from the backref
 relationship avoids the error (but then removes the desired behavior of
 removing childs that are no longer necessary).

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2921>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

[Sqlalchemy-tickets] [sqlalchemy] #2921: backref relationships with cascade delete-orphan throw Int

[Sqlalchemy-tickets] [sqlalchemy] #2921: backref relationships with cascade delete-orphan throw IntegrityError when child is replaced