Session.merge does work with un-pickled cached instances

I have an application which uses dogpile to cache some SQLALchemy ORM instances. We recently noticed when loading cached objects any instance access still triggers a bunch of SQL queries. This turns out to be caused due to the use of the standard memory backend from dogpile and how that interacts with SQLALchemy's Session.merge: if I try to merge() an instance from another session into the current session SELECTs are triggered, but if do pickle and unpickle the object no SELECTs are run.

import pickle
import sqlalchemy
from sqlalchemy import orm
from sqlalchemy import schema
from sqlalchemy import types
from sqlalchemy.ext.declarative import declarative_base


metadata = schema.MetaData()
BaseObject = declarative_base(metadata=metadata)


class Parent(BaseObject):
    __tablename__ = 'parent'
    id = schema.Column(types.Integer(), primary_key=True)
    name = schema.Column(types.Unicode())


engine = sqlalchemy.create_engine('sqlite:///:memory:', echo=True)
metadata.create_all(engine)
sm = orm.sessionmaker(bind=engine)

Session = orm.scoped_session(sm)

session = Session()
parent = Parent(name='parent')
session.add(parent)
session.flush()
session.commit()

session = Session()
parent = session.query(Parent).first()
pickle_cache = pickle.dumps(parent)
session.commit()

session = Session()
new = session.merge(parent, load=False)
# This triggers a SELECT
print new.name
session.rollback()


session = Session()
new = session.merge(pickle.loads(pickle_cache), load=False)
# This does NOT trigger a SELECT
print new.name
session.rollback()

Comments (6)