Merge fails when Object has had a new child object created in a one-to -many situation

[Mark Paine]

Model example:

class Element(Base):
    __tablename__ = 'Element'
    # Primary Key(s)
    id = Column(Integer, primary_key=True)
    properties = relationship("ElementProperty", backref="Element")

class ElementProperty(Base):
    __tablename__ = 'ElementProperty'
    # Primary Key(s)
    name = Column(String(450), primary_key=True)
    element_Id = Column(Integer, ForeignKey("Element.id"), primary_key=True)

Code example:

    def working(self):
        e = sqldb_ems.Element(id=1)
        ep = sqldb_ems.ElementProperty(name='test')
        e.properties.append(ep)
        session.add(e)
        session.commit()

    def not_working(self):
        e = sqldb_ems.Element(id=1)
        ep = sqldb_ems.ElementProperty(name='test')
        e.properties.append(ep)
        session.merge(e)
        session.commit()

The code which does not work, fails during a select statement looking at the child object table. The select statement looks like this:

SELECT [ElementProperty].name AS [ElementProperty_name], [ElementProperty].[element_Id] AS [ElementProperty_element_Id], [ElementProperty].type AS [ElementProperty_type], [ElementProperty].value AS [ElementProperty_value] FROM [ElementProperty] WHERE [ElementProperty].name = %(param_1)s AND [ElementProperty].[element_Id] = %(param_2)s

sqlalchemy.engine.base.Engine {'param_1': 'test', 'param_2': symbol('NEVER_SET')}

The param_2 is not being set. This should be the foreign key.

[Mike Bayer]

hi there -

there is possibly something of a bug in that the NEVER_SET symbol is leaking into the query, wihch will in fact fail on a backend such as Postgresq, but unfortunately the foreign key value is not available at this point, because the two given objects have not been flushed. The correlation between the fact that the primary key of Element needs to be populated into the foriegn key column of ElementProperty only occurs within flush(). Otherwise, the primary key needs to be set explicitly. I'll commit a warning for this situation.

[Mike Bayer]

OK, so the NEVER_SET will fix in a moment. However the merge w/o the primary key here is problematic in some cases.

In the case where both "e" and "ep" are brand new, the INSERT of both records works fine.

In the case where "e" and "ep" both represent an existing row, the merge fails unless the primary key attributes of "ep" are set explicitly. here's a full example:

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

Base = declarative_base()



class Element(Base):
    __tablename__ = 'Element'
    # Primary Key(s)
    id = Column(Integer, primary_key=True)
    properties = relationship("ElementProperty",)


class ElementProperty(Base):
    __tablename__ = 'ElementProperty'
    # Primary Key(s)
    name = Column(String(450), primary_key=True)
    element_Id = Column(Integer, ForeignKey("Element.id"), primary_key=True)

engine = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)

session = Session(engine)

# case 1, brand new objects, no problem
e = Element(id=1)
ep = ElementProperty(name='test')
e.properties.append(ep)
e2 = session.merge(e)
session.commit()

# case 2, now they exist
e = Element(id=1)
ep = ElementProperty(name='test')
e.properties.append(ep)
e2 = session.merge(e)

# fails: Dependency rule tried to blank-out primary key column
# 'ElementProperty.element_Id'.  this is because ep is detected as *replacing*
# the existing ElementProperty in the database because the primary keys
# don't match; so it attempts to de-associate the "original" ElementProperty
# and causes the error.
try:
    session.flush()
except Exception as e:
    print "flush of 'existing' records failed: %s" % e
session.rollback()

# succeeds: fully set up primary key attributes without reliance on
# fk->pk sync
e = Element(id=1)
ep = ElementProperty(element_Id=1, name='test')
e.properties.append(ep)
session.commit()

[Mike Bayer]

• Fixed bug in :meth:.Session.merge where an object with a composite primary key that has values for some but not all of the PK fields would emit a SELECT statement leaking the internal NEVER_SET symbol into the query, rather than detecting that this object does not have a searchable primary key and no SELECT should be emitted. fixes #3647

→ <<cset 366f97b5617a>>

[Mike Bayer]

• Fixed bug in :meth:.Session.merge where an object with a composite primary key that has values for some but not all of the PK fields would emit a SELECT statement leaking the internal NEVER_SET symbol into the query, rather than detecting that this object does not have a searchable primary key and no SELECT should be emitted. fixes #3647

(cherry picked from commit 366f97b5617af0d15cfaf594ec5ef0408c70e873)

→ <<cset a74b028f9f5b>>

[Mike Bayer]

this resolves the NEVER_SET issue. merge() still might not do what you want if you don't send in the known primary key value, however, as illustrated in the example above. thanks for reporting!