uselist=False collection not backref deferred, leads to unwanted load/autoflush

[Mike Bayer]

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class A(Base):
    __tablename__ = 'a'

    id = Column(Integer, primary_key=True)
    b = relationship("B", uselist=False, backref="a")

class B(Base):
    __tablename__ = 'b'

    id = Column(Integer, primary_key=True)
    a_id = Column(Integer, ForeignKey('a.id'), nullable=False)

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

sess = Session(e)

a1 = A()
b1 = B(a=a1)
sess.add(a1)

a2 = A()
sess.add(a2)

sess.commit()

b1 = sess.query(B).first()

# this is needed to trigger it
b1.a


b1.a = a2

[Mike Bayer]

the difference between uselist=True and uselist=False is that the old value must be removed. Plenty of tests in test_backref_mutations test this kind of thing. Suppose we disable the load on backref:

diff --git a/lib/sqlalchemy/orm/attributes.py b/lib/sqlalchemy/orm/attributes.py
index bfba695..4ff6261 100644
--- a/lib/sqlalchemy/orm/attributes.py
+++ b/lib/sqlalchemy/orm/attributes.py
@@ -784,7 +784,7 @@ class ScalarObjectAttributeImpl(ScalarAttributeImpl):
         if initiator and initiator.parent_token is self.parent_token:
             return

-        if self.dispatch._active_history:
+        if passive is PASSIVE_OFF and self.dispatch._active_history:
             old = self.get(state, dict_, passive=PASSIVE_ONLY_PERSISTENT)
         else:
             old = self.get(state, dict_, passive=PASSIVE_NO_FETCH)

then run the test like this:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class A(Base):
    __tablename__ = 'a'

    id = Column(Integer, primary_key=True)
    b = relationship("B", uselist=False, backref="a")

class B(Base):
    __tablename__ = 'b'

    id = Column(Integer, primary_key=True)
    a_id = Column(Integer, ForeignKey('a.id'), nullable=False)

e = create_engine("sqlite://", echo='debug')
Base.metadata.create_all(e)

sess = Session(e)

a1 = A()
b1 = B(a=a1)
a2 = A()
b2 = B(a=a2)

sess.add_all([b1, a2, b2](a1,))
sess.commit()

b1.a = a2

sess.commit()

print a2.b

a2.b now has two b's attached and b2 has not been replaced by b1, we get:

SELECT b.id AS b_id, b.a_id AS b_a_id 
FROM b 
WHERE ? = b.a_id
2013-06-04 12:28:23,543 INFO sqlalchemy.engine.base.Engine (2,)
2013-06-04 12:28:23,543 DEBUG sqlalchemy.engine.base.Engine Col ('b_id', 'b_a_id')
2013-06-04 12:28:23,543 DEBUG sqlalchemy.engine.base.Engine Row (1, 2)
2013-06-04 12:28:23,543 DEBUG sqlalchemy.engine.base.Engine Row (2, 2)
/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/strategies.py:524: SAWarning: Multiple rows returned with uselist=False for lazily-loaded attribute 'A.b' 
  return self._emit_lazyload(session, state, ident_key, passive)
<__main__.B object at 0x10161f7d0>

in this case, if we stick to the plan and do no_autoflush:

with sess.no_autoflush:
    b1.a = a2

sess.commit()

print a2.b

we still get an integrity error, because the replacement of b2 throws a not null exception, but this is what we want in this case, since uselist=False means the old value must be replaced.

[Mike Bayer]

we can also get the "multiple rows" condition by just turning off active_history:

A.b.impl.active_history = False

b1.a = a2

sess.commit()

print a2.b

it might be an interesting feature to allow users to override active_history, right now you can turn it on if not already, but not turn it off at the configuration level.

[Mike Bayer]

Removing milestone: 0.8.xx (automated comment)