[Sqlalchemy-tickets] Issue #4176: Accessing deferred column property after flush loads all deferred column properties (zzzeek/sqlalchemy)

[Adrian <iss...@bi...>]

New issue 4176: Accessing deferred column property after flush loads all deferred column properties
https://bitbucket.org/zzzeek/sqlalchemy/issues/4176/accessing-deferred-column-property-after

Adrian:

Example to reproduce:

```
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import *
from sqlalchemy.dialects.postgresql import JSON

Base = declarative_base()


class ProtectionMode(object):
    public = 0
    inheriting = 1
    protected = 2


class Event(Base):
    __tablename__ = 'events'
    id = Column(Integer, primary_key=True)
    category_id = Column(Integer, ForeignKey('categories.id'), nullable=True, index=True)
    category = relationship('Category', backref='events')

    def __repr__(self):
        return '<Event {}>'.format(self.id)


class Category(Base):
    __tablename__ = 'categories'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('categories.id'), index=True, nullable=True)
    protection_mode = Column(SmallInteger, nullable=False, default=ProtectionMode.inheriting)
    icon_metadata = Column(JSON, nullable=False, default=lambda: None)
    children = relationship('Category', primaryjoin=(id == remote(parent_id)),
                            backref=backref('parent', primaryjoin=(remote(id) == parent_id)))

    @classmethod
    def get_icon_data_cte(cls):
        cat_alias = aliased(cls)
        cte_query = (select([cat_alias.id, cat_alias.id.label('source_id'), cat_alias.icon_metadata])
                     .where(cat_alias.parent_id.is_(None))
                     .cte(recursive=True))
        rec_query = (select([cat_alias.id,
                             case({'null': cte_query.c.source_id}, else_=cat_alias.id,
                                  value=func.json_typeof(cat_alias.icon_metadata)),
                             case({'null': cte_query.c.icon_metadata}, else_=cat_alias.icon_metadata,
                                  value=func.json_typeof(cat_alias.icon_metadata))])
                     .where(cat_alias.parent_id == cte_query.c.id))
        return cte_query.union_all(rec_query)

    @classmethod
    def get_protection_cte(cls):
        cat_alias = aliased(cls)
        cte_query = (select([cat_alias.id, cat_alias.protection_mode])
                     .where(cat_alias.parent_id.is_(None))
                     .cte(recursive=True))
        rec_query = (select([cat_alias.id,
                             case({ProtectionMode.inheriting: cte_query.c.protection_mode},
                                  else_=cat_alias.protection_mode, value=cat_alias.protection_mode)])
                     .where(cat_alias.parent_id == cte_query.c.id))
        return cte_query.union_all(rec_query)

    def __repr__(self):
        return '<Category {}>'.format(self.id)


cte = Category.get_protection_cte()
query = select([cte.c.protection_mode]).where(cte.c.id == Category.id).correlate_except(cte)
Category.effective_protection_mode = column_property(query, deferred=True)

cte = Category.get_icon_data_cte()
query = (select([func.json_build_object('source_id', cte.c.source_id,
                                        'metadata', cte.c.icon_metadata)])
         .where(cte.c.id == Category.id)
         .correlate_except(cte))
Category.effective_icon_data = column_property(query, deferred=True)


e = create_engine('postgresql:///test', echo=True)
Base.metadata.create_all(e)
s = Session(e)


if not s.query(Category).count():
    root = Category(protection_mode=ProtectionMode.public)
    cat = Category(parent=root)
    s.add(root)
    e = Event(category=root)
    s.add(e)
    s.commit()
    print 'created data'
else:
    root = s.query(Category).filter_by(parent_id=None).one()
    cat = root.children[0]
    e = root.events[0]
    print '\n\n\nFIRST READ\n'
    print cat.effective_protection_mode
    e.category = cat
    s.flush()
    print '\n\n\nSECOND READ\n'
    print cat.effective_protection_mode
```

---

Relevant output (on the second run):

```
FIRST READ

2018-01-31 16:05:18,233 INFO sqlalchemy.engine.base.Engine WITH RECURSIVE anon_2(id, protection_mode) AS
(SELECT categories_1.id AS id, categories_1.protection_mode AS protection_mode
FROM categories AS categories_1
WHERE categories_1.parent_id IS NULL UNION ALL SELECT categories_1.id AS id, CASE categories_1.protection_mode WHEN %(param_1)s THEN anon_2.protection_mode ELSE categories_1.protection_mode END AS anon_3
FROM categories AS categories_1, anon_2
WHERE categories_1.parent_id = anon_2.id)
 SELECT (SELECT anon_2.protection_mode
FROM anon_2
WHERE anon_2.id = categories.id) AS anon_1
FROM categories
WHERE categories.id = %(param_2)s
2018-01-31 16:05:18,233 INFO sqlalchemy.engine.base.Engine {'param_1': 1, 'param_2': 2}
0
2018-01-31 16:05:18,234 INFO sqlalchemy.engine.base.Engine UPDATE events SET category_id=%(category_id)s WHERE events.id = %(events_id)s
2018-01-31 16:05:18,234 INFO sqlalchemy.engine.base.Engine {'category_id': 2, 'events_id': 1}



SECOND READ

2018-01-31 16:05:18,236 INFO sqlalchemy.engine.base.Engine WITH RECURSIVE anon_2(id, protection_mode) AS
(SELECT categories_1.id AS id, categories_1.protection_mode AS protection_mode
FROM categories AS categories_1
WHERE categories_1.parent_id IS NULL UNION ALL SELECT categories_1.id AS id, CASE categories_1.protection_mode WHEN %(param_1)s THEN anon_2.protection_mode ELSE categories_1.protection_mode END AS anon_3
FROM categories AS categories_1, anon_2
WHERE categories_1.parent_id = anon_2.id),
anon_5(id, source_id, icon_metadata) AS
(SELECT categories_2.id AS id, categories_2.id AS source_id, categories_2.icon_metadata AS icon_metadata
FROM categories AS categories_2
WHERE categories_2.parent_id IS NULL UNION ALL SELECT categories_2.id AS id, CASE json_typeof(categories_2.icon_metadata) WHEN %(param_2)s THEN anon_5.source_id ELSE categories_2.id END AS anon_6, CASE json_typeof(categories_2.icon_metadata) WHEN %(param_3)s THEN anon_5.icon_metadata ELSE categories_2.icon_metadata END AS anon_7
FROM categories AS categories_2, anon_5
WHERE categories_2.parent_id = anon_5.id)
 SELECT (SELECT anon_2.protection_mode
FROM anon_2
WHERE anon_2.id = categories.id) AS anon_1, (SELECT json_build_object(%(json_build_object_2)s, anon_5.source_id, %(json_build_object_3)s, anon_5.icon_metadata) AS json_build_object_1
FROM anon_5
WHERE anon_5.id = categories.id) AS anon_4
FROM categories
WHERE categories.id = %(param_4)s
2018-01-31 16:05:18,236 INFO sqlalchemy.engine.base.Engine {'param_4': 2, 'param_1': 1, 'param_3': 'null', 'param_2': 'null', 'json_build_object_3': 'metadata', 'json_build_object_2': 'source_id'}
0
```

As you can see, the second time the property is accessed, the other deferred column property is loaded as well - this results in a massive performance drop in the real (non-simplified) code since some of the deferred relationships are quite expensive.

This issue happens in 1.1.15 but not in 1.2.2, but I couldn't find any related changelog entry.