Columns not deferred when an object is merged into a session with load=False

[Tom Flannaghan]

We are using sqlalchemy and postgres extensively, and have come across a problem with deferred columns when an object is merged into a session with load=False. Under these conditions, we find that the deferred columns are not deferred - i.e. they loaded when any attribute is accessed, rather than being deferred until explicitly accessed. We are using sqlalchemy 0.9.7 and postgres 9.4.1.

Here's a simple example that illustrates the problem using the following table:

class Port(Base):

    ''' Table port in schema comm. '''

    __tablename__ = "port"

    name          = Column(String, primary_key=True)

    port          = deferred(Column(Integer, unique=True))

First we try querying Port on name:

my_port = session.query(Port).filter_by(name='test_thing').one()

my_port.name

This code produces the following query, showing that the port column is correctly deferred:

2015-07-16 15:44:08.539 : sqlalchemy.engine.base.Engine : base.py:912(_execute_context) : INFO : SELECT comm.port.name AS comm_port_name

FROM comm.port

WHERE comm.port.name = %(name_1)s

Now I expire and expunge the my_port object, and then merge it back into the session with load=False, and access name like this:

session.expire(my_port)

session.expunge(my_port)

new_port = session.merge(my_port, load=False)

new_port.name

Which produces the following query, showing that the port column has not been deferred, and has instead been included in the query that fetches name:

2015-07-16 15:44:08.539 : sqlalchemy.engine.base.Engine : base.py:912(_execute_context) : INFO : SELECT comm.port.port AS comm_port_port, comm.port.name AS comm_port_name

FROM comm.port

WHERE comm.port.name = %(param_1)s

If I do not set load=False, the column is correctly deferred. I've also tried creating a deferred object directly rather than expunging one from the session and the problem still occurs, so it seems to be caused by the load=False option.

Thanks,

Tom

[Mike Bayer]

On 7/16/15 11:47 AM, Tom Flannaghan wrote:

We are using sqlalchemy and postgres extensively, and have come across a problem with deferred columns when an object is merged into a session with load=False. Under these conditions, we find that the deferred columns are not deferred - i.e. they loaded when any attribute is accessed, rather than being deferred until explicitly accessed. We are using sqlalchemy 0.9.7 and postgres 9.4.1.

thanks for reporting this issue.  https://bitbucket.org/zzzeek/sqlalchemy/issues/3488/columnpopertymerge-w-load-false-calls has been added to address a possible fix targeted at 1.1.

For now, I'd recommend either not using expire() or specifying specific attribute names to expire().   

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Tom Flannaghan]

Thanks for your reply. Our exact problem is that we are creating empty detached objects from the primary key alone, and then merging them in to a session, so we can't do this:

On Thursday, 16 July 2015 18:44:26 UTC+1, Michael Bayer wrote:

For now, I'd recommend either not using expire() or specifying specific attribute names to expire().   

I just included the expire() in the example as it was a more succinct way to reproduce the same bug.

Our code looks more like this:

detached_port = Port(name='test')

make_transient_to_detached(detached_port)

new_port = session.merge(detached_port, load=False)

...

In my example, Port only has two columns so this won't demonstrate the bug as the only non-deferred column is filled in already, but more complicated objects that are merged in this way will not defer columns. Do you think there a work around in this case?

Thanks,

Tom 

[Mike Bayer]

try this recipe which should reset the expired state of the target attributes individually:


from sqlalchemy.orm import attributes


def merge_load_false(session, obj):
    obj = session.merge(obj, load=False)

    obj_state = attributes.instance_state(obj)
    obj_dict = obj_state.dict

    deferred_keys = [
        attr.key for attr in obj_state.mapper.column_attrs if attr.deferred]
    for k in deferred_keys:
        if k not in obj_dict:
            obj_state._reset(obj_dict, k)
    return obj

a1 = merge_load_false(s, a1)

[Tom Flannaghan]

Thanks a lot! This works well.

Tom

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/gRV7mSHFJiE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.