Session.merge() failure when primary key is assigned via a relationship

[Erik Swanson]

I'm not sure whether this a bug or not:

When a mapped object has a foreign key reference to another object as
part of its own primary key, setting that column's value via a
relationship property causes session.merge() to not SELECT the object
first and thus encounter an IntegrityError when it tries to INSERT the
object.

I have written a reduction of this problem. When run as a script, the
second and later invocations will die with an IntegrityError.

"""
#!/usr/bin/env python
from sqlalchemy import Column, Unicode, Integer, ForeignKey,
create_engine
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.orm.exc import NoResultFound
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Alpha(Base):
__tablename__ = 'alpha'
id = Column(Integer, primary_key=True, nullable=False)
val = Column(Unicode, nullable=False)

def __init__(self, val):
self.val = val

class Foo(Base):
__tablename__ = 'foo'
alpha_id = Column(Integer, ForeignKey('alpha.id'),
primary_key=True, nullable=False)
alpha = relationship(Alpha)
foo_key = Column(Unicode, primary_key=True, nullable=False)
foo_val = Column(Integer, nullable=False)

def __init__(self, alpha, foo_key, foo_val):
self.alpha = alpha
self.foo_key = foo_key
self.foo_val = foo_val

engine = create_engine('sqlite:///regr-test.db', echo=True)
metadata = Base.metadata
metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

try:
a = session.query(Alpha).one()
except NoResultFound:
a = Alpha(u'blah')
session.add(a)
session.commit()

f = Foo(a, u'mykey', 99)
merged_f = session.merge(f)
session.add(merged_f)
session.commit()
"""

What makes me think this might be a bug is that the script suddenly
starts working as intended if Foo.__init__ is modified to set
"self.alpha_id = alpha.id" instead of "self.alpha = alpha".

Is this a bug, or is there some aspect of using a relationship to
implicitly set self.alpha_id that I'm not understanding?

--
Erik Swanson

[Michael Bayer]

It's been brought up before, and basically it's way above and beyond whats possible right now with merge() for it to be able to run in-flush dependency rules on incoming objects in order to determine if it already has an effective primary key, and what that primary key should be. I tend to lean towards "not a bug" since the documented behavior of merge() is that the first thing it does is "examines the primary key of the instance". The "Alpha" object here is not the primary key of "Foo". Considering a hypothetical feature of such, the challenge would be to not duplicate code across flush() and merge(), and just as importantly to somehow perform the check in such a way that doesn't add latency to all invocations of merge(). So my current thinking would be "feature add, not feasible for some time to come".

Post by Erik Swanson
When a mapped object has a foreign key reference to another object as
part of its own primary key, setting that column's value via a
relationship property causes session.merge() to not SELECT the object
first and thus encounter an IntegrityError when it tries to INSERT the
object.
I have written a reduction of this problem. When run as a script, the
second and later invocations will die with an IntegrityError.
"""
#!/usr/bin/env python
from sqlalchemy import Column, Unicode, Integer, ForeignKey,
create_engine
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.orm.exc import NoResultFound
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
__tablename__ = 'alpha'
id = Column(Integer, primary_key=True, nullable=False)
val = Column(Unicode, nullable=False)
self.val = val
__tablename__ = 'foo'
alpha_id = Column(Integer, ForeignKey('alpha.id'),
primary_key=True, nullable=False)
alpha = relationship(Alpha)
foo_key = Column(Unicode, primary_key=True, nullable=False)
foo_val = Column(Integer, nullable=False)
self.alpha = alpha
self.foo_key = foo_key
self.foo_val = foo_val
engine = create_engine('sqlite:///regr-test.db', echo=True)
metadata = Base.metadata
metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
a = session.query(Alpha).one()
a = Alpha(u'blah')
session.add(a)
session.commit()
f = Foo(a, u'mykey', 99)
merged_f = session.merge(f)
session.add(merged_f)
session.commit()
"""
What makes me think this might be a bug is that the script suddenly
starts working as intended if Foo.__init__ is modified to set
"self.alpha_id = alpha.id" instead of "self.alpha = alpha".
Is this a bug, or is there some aspect of using a relationship to
implicitly set self.alpha_id that I'm not understanding?
--
Erik Swanson
--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.