Hello Mike, first, thanks for considering my ramblings and thanks for the reply!
On 17.01.2018 23:23, Mike Bayer wrote: > this is overkill. Here's what your code looks like without an ORM: Yes, I think my message was lost in translation or rather in oversimplification. > this is very specific to the very quirky and odd thing I'm doing in > the first place, which is that I'm creating an endlessly long linked > list - I detach the element. The actual code does not create an endlessly long linked list of course, it's more like a directed acyclic graph. Nodes may have many outgoing edges so it's not so simple to know where to cut :-) >> This made me think about our usage of "session.expire": Mostly, we use it to >> tell SQLAlchemy that this is a good time to forget this object in the >> expectation that there are no pending changes. I found 13 calls to >> session.expire in our code base. > The way that loop is structured, I would not want to expire all > attributes that have been flushed. When you make a new Change(), it > has a predecessor for which it needs to locate its primary key in > order to assemble it into the foreign key column of the successor. If > you've expired that Change() object completely, you are forcing the > ORM to un-expire the object using another SELECT to ensure it hasn't > been DELETEd, so that it can use the Change.id value. The more True. And I admit that it did not think about cutting the links, I just wanted to drop objects (and all related links) that are no longer needed. I actually prefetch the primary keys of all objects touched or referred to by a batch of incoming objects. After processing the batch, I drop all objects not referred to by the next incoming batch and load those required for processing the next batch. > you can tell if an object is part of that "modified" collection using > instance_state(obj).modified - if that's false, it's definitely clean. > _modified isn't public. You can also look inside of session.dirty. Thanks for the pointer, I actually looked at the implementation of session.dirty to find the internal _modified. I replaced this with instance-state(obj).modified. > ".new" or ".deleted"). But the most in-depth method, > session.is_modified(), does that state.modified check first before > digging in, so since you can likely tolerate false positives you can > stick with that. False positives are not an issue, if an instance is kept for one extra batch if will most likely be dropped in the next one. > it's "safe" but as before if the object is involved in other objects > still to be flushed, a new SELECT will need to be emitted to unexpire > the object. I droped safe_expire_all, it was only for completeness. About the extra select for unexpiring: I will gladly take that performance hit if I manage to reintroduce that bug. Calling expire() for an instance that had pending updates was (obviously) a bug in my application. I just wanted to go one step further than just fixing the spot where it appeared and make it harder to go there again. > the case you illustrate is surprising and unusual. I would mark it > up to a specific case you have here where you happen to be building up > an endlessly long chain of objects for which the issue of memory is > most cleanly handled by an explicit periodic break of the chain. The initial code was better here because session.commit() would have done that for us because of the implicit expire_all directly after the flush. As you noted above this lead to a number of extra selects to unexpire all objects still needed. BTW: Our application is a rich client where the user interface also keeps mapped objects loaded in a ORM session, since reloading is quite expensive. Updates are done mostly in worker threads and processes and broadcast to all database sessions. For updating the display, all modified objects are expired and refreshed from the database. This is where I actually messed up and refreshed from the DB inside a running transaction - before flushing the changes entered by the user. m( Finding the cause made me think of other misuses of expiration. >> Note that the documentation does not warn about dropping pending changes due >> to calls to expire. > it's mentioned here: > http://docs.sqlalchemy.org/en/latest/orm/session_state_management.html#session-expire > but just not in any kind of boldface: Oops, sorry, I only checked the reference documentation. > I actually probably wouldn't make expire() do what it does today, e.g. > expire un-flushed changes. When expire() was first created it was > likely not possible or very easy to hit only DB-persisted attributes. > The attribute history system was rewritten several times since then > and it probably would be pretty simple today to add a flag expire(obj, > persistent_only=True) something like that. But it's one of those > things where when someone needs that, I want to see why, because I > think there's usually another way to handle the situation. True. And I want to say very clear that the problem here originated in application code, not inside SQLAlchemy. I still would argue that changing expire to drop only persisted data (which would take a number of releases to become the default) would make this an instance of a pit of success <https://blog.codinghorror.com/falling-into-the-pit-of-success/>. Of course this would need quite some effort. Thanks to your help, I think, we are good now: I added a safe_expire helper to our code base using only public SQLAlchemy APIs, thanks to your hints. Many thanks and greetings, Torsten -- $---+----1----+----2----+----3----+----4----+----5----+----6----+ SCALE GmbH Niederlassung Dresden Torsten Landschoff Pohlandstraße 19 01309 Dresden Tel: +49-351-312002-10 Fax: +49-351-312002-29 SCALE GmbH Registergericht und Sitz: Ingolstadt, HRB 6384 Geschäftsführer: Dr.-Ing. Heiner Müllerschön, Dipl.-Math. Ulrich Franz -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
