Thanks Michael, the 'solution' is seems was to refactor my code so that operations are clearly performed in the correct order, and in some cases to explicitly load and/or update the tree parameters when they might have changed. Now I'm not only doing before_flush (which I've restricted to session/ORM queries only), but also before/after insert, update, and delete (where I only do SQL expression queries).
On Feb 4, 8:52 am, Michael Bayer <mike...@zzzcomputing.com> wrote: > On Feb 4, 2012, at 11:23 AM, Mark Friedenbach wrote: > > Hi, I'm running into a problem with my nested sets implementation. > > Inserting, moving, or removing a node can potentially affect one or > > more of many other nodes' tree properties (tree id, left, right, > > depth, or parent relationship). For efficiency's sake this change > > occurs as a single, rather complex SQL expression query that handles > > the magic of updating all the other node values. > > > Just as a precaution I've added a session.expire_all() after the > > session.execute(<query>), so that the tree values will be reloaded as > > I move on to process other node operations in the same transaction. > > However what I've discovered is that expire_all() causes *all* as-of- > > yet unpersisted changes to be lost. As an example of what I mean, > > here's an actual shell log: > > >>>> obj = session.query(...) > >>>> obj.name > > u'root1' > >>>> obj.name = 'root66' > >>>> session.add(obj) > >>>> session.expire_all() > >>>> session.commit() > >>>> obj.name > > u'root1' > > > It may be possible that I can restructure the order in which I do > > things so that stale data isn't an issue. But out of curiosity, is > > there a way to expire only *unchanged* stale data? This is how I > > naïvely expected expire_all() to work. > > "all" means everything, that method is called typically after rollback() or > commit() in conjunction with the transaction. > > While there is a way to detect history on all attributes and expire just > those with no net change, this is a time consuming operation and should not > be necessary. > > In this case, you know that the only values that are being updated outside of > the normal flush process are the "left" and "right" columns (and whatever > denormalized data you're storing such as "depth"), so you should just be > expiring those, and it should be either within the after_flush event: > > http://docs.sqlalchemy.org/en/latest/orm/events.html?highlight=after%... > > These attributes will refresh themselves when next accessed. > > Or if you have a means available of populating some of these attributes with > their correct value instead of just expiring, you can use > attributes.set_committed_value(): > > http://docs.sqlalchemy.org/en/latest/orm/session.html?highlight=set_c... > > Reading your paragraph again, if you're actually doing the math for > left/right/depth in Python and need the value of those attributes to be > correct as the flush() proceeds, I'd consider doing the math in SQL, as you > can't assume all the nodes are going to be loaded into memory. > > > Alternatively, a good API for this case would have been an > > expire_all(mapped_class, ['attribute', 'names']), a sort of compromise > > between expire() and expire_all(). > > You can roll this yourself: > > for obj in session.identity_map.values(): > if isinstance(obj, myclass): > session.expire(obj, ['a', 'b']) > > > > > > > > > > > -- > > You received this message because you are subscribed to the Google Groups > > "sqlalchemy" group. > > To post to this group, send email to sqlalchemy@googlegroups.com. > > To unsubscribe from this group, send email to > > sqlalchemy+unsubscr...@googlegroups.com. > > For more options, visit this group > > athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
