Ah. I think that I have been thinking of a session as a cache instead of a transaction, which is incorrect. (It's similar because you have to worry about persistence in the face of failures, but not-similar, because you're not worried about communication with other processes. That is, a commit is not "expose this data to other cores/machines". A commit is a checkpoint to rollback to.) I was thinking that adding something to the session was taking something from a local and putting it into a cache, and then committing it was taking it from a cache and putting it into the database. Totally wrong abstraction, sorry.
-Lyla On Mon, Apr 29, 2019 at 1:04 PM Mike Bayer <mike...@zzzcomputing.com> wrote: > On Mon, Apr 29, 2019 at 12:04 PM Lyla Fischer <lylafi...@gmail.com> wrote: > > > > Sorry about the language. I'll try to stick to "intuitive" and > "surprising" in the future. > > > > I guess that I am familiar enough with the concept that something needs > to persist in the database before it can be referenced in the database that > I was surprised when an object was already in the database even when I > didn't tell it to persist. > > > > This is especially true in the case of a one-to-many relationship, where > the head of a tassel can exist without any tassel threads. It just happened > to be the case that I added a tassel thread and... now it is in the > session?? Quoting from a previous email, for easy reference: > > """ > > my_head = Head(id="foobar") > > my_head = db_session.merge(my_head) > > db_session.commit() > > > > my_tassel_thread = TasselThread(head=my_head) > > > > "my_tassel_thread" is now in the Session due to the backref cascade, > > which I think I really might consider defaulting to False at some > > point. > > """ > > > > What would happen if I were editing an existing TasselThread, instead of > adding a new one? Would those changes be persisted as well, even though I > didn't tell them to explicitly? > > so the idea of "an existing TasselThread" in terms of the ORM means > you would have accessed it from the database, which means it would > have a primary key value and already be associated with that Session. > So you would expect that changes on this object are persisted. If > you expunged the object so that it became detached, then yes again the > cascade_backrefs thing gets in the way (or not, depending on how you > look at it) if you re-associate it with a persistent object. > > > > > I get that you are doing stuff in the backend that involves database > persistence in order to make the collection references work, but if it were > implemented using only foreign keys, then the head would have no way of > accessing it's tassel threads short of doing a table scan for it's own > primary key in the foreign key spot. I get that's massively inefficient, > and therefore not the way that you are doing things, > > if you don't use the backref feature at all, then it actually does > work that way. you can wait for flushes to happen, then expire > attributes on things once they are persistent. this way of working > will expose the persistence mechanics a lot more explicitly and > inconveniently though. > > > but I was thinking of SQL Alchemy relationship as being something that > very well could have been implemented without creating new things to > persist in the database beyond what is in the bare minimum of common > explanations of SQL-level one-to-many relationships, often used in intro > classes. > > what are the "new things to persist" you are referring towards ? > Are you referring to the fact that in order to set > TasselThread.some_foreign_key to a value, you need a Tassel object? > That is also optional. You can set foreign key values directly as > well, and there is no issue with that, except that it does not > automatically refresh the related relationships; you'd need to expire > them directly or wait until everything is expired on commit. The FAQ > entry > https://docs.sqlalchemy.org/en/13/faq/sessions.html#i-set-the-foo-id-attribute-on-my-instance-to-7-but-the-foo-attribute-is-still-none-shouldn-t-it-have-loaded-foo-with-id-7 > discusses this in detail. > > There is also a related recipe > https://github.com/sqlalchemy/sqlalchemy/wiki/ExpireRelationshipOnFKChange > (the FAQ has an apparently broken link to it) which expands upon the > "set foreign key attributes" way of working so that the related > objects *are* automatically refreshed immediately. There is at > least one major production-level application that uses this recipe, > which is why it's so complicated because I helped them find every > possible edge case, however I don't recommend using this recipe > because it is complicated and works in opposition to the design of the > ORM. However, a lot of folks find it appealing because they are in > fact coming from the "SQL-level" view of things to which you refer and > prefer to stay at that level, yet they still want their relationships > to load automatically. > > > > Then the relationship just means that I don't have to do a table scan - > the object just knows which TasselThreads are its own. That doesn't fit the > common university model of the way foreign key only one-to-many > relationships work, but I didn't think about it too hard. I was just > thinking that "yes, of course you need to persist the head before > persisting a tassel thread, because the tassel thread needs the id for its > foreign key", while the reverse didn't occur to me. > > > > -Lyla > > > > On Mon, Apr 29, 2019 at 11:14 AM Mike Bayer <mike...@zzzcomputing.com> > wrote: > >> > >> On Mon, Apr 29, 2019 at 7:22 AM Lyla Fischer <lylafi...@gmail.com> > wrote: > >> > > >> > If I can comment on what seems instinctive to someone who just > recently read the documentation, and made assumptions based off of it: My > understanding was that the relationship abstractions were supposed to be > about making foreign keys easier to keep track of, and being able to go > both ways on a relationship in a object-oriented way. The fact that there > were any implications at all for persistence was surprising to me, and it > took me a significant amount of time to understand that SQL Alchemy was > trying to do some magic behind the scenes when it came to persistence. I > support the idea of making cascade_backrefs=False by default, because it > keeps the separate objects separate, it makes me less nervous about > eventual performance (which is a constant concern when there is magic I > didn't intend happening in a library), and it's basically just working the > way that I expected things to work initially. I'm fine with dealing with > errors that might come about from the implications of updating of objects > independently. It seems like part of the responsibilities of dealing with > persistence, ever. > >> > >> I'm trying to gather the rationale that you are referring towards in > >> your comment, however I'm not able to work with the reason "magic I > >> didn't intend", because that reason doesn't actually say anything. > >> Of course every issue is about telling a library what your intent is, > >> and the library fulfullling that intent or not. But to refer to the > >> intents that you didn't expect as "magic" and the intents that you > >> *did* expect as "not magic", I guess the latter is implicit, only > >> illustrates how much the library is doing completely correctly such > >> that you aren't noticing it. > >> > >> The thing that relationship() does is coordinating Python objects that > >> are linked together in terms of a foreign key relationship in a > >> database. Then there is the backref concept, which expands on the > >> relationship concept, to more fully emulate the behavior of relational > >> database foreign keys with Python objects. When working completely > >> normally, SQLAlchemy produces Python object behavior as follows: > >> > >> object_a = ObjectA() > >> object_b = ObjectB() > >> object_a.some_collection.append(object_b) > >> assert object_b.parent is object_a > >> > >> Someone working only with Python objects would probably call most of > >> the above interaction as "magic", that ObjectA has a collection > >> automatically instantiated on that (this incidentally is also going to > >> be scaled back in SQLAlchemy 2.0) and that when I append an object to > >> this collection, the object now has a ".parent" that refers back to > >> that original object. The reason it has to do that in SQLAlchemy is > >> because we are emulating a relational database foreign key > >> relationship, where containment on one side implies association in the > >> other direction. > >> > >> Where SQLAlchemy works hard to acknowledge that the above behaviors > >> are not normal for Python (that is, are reasonably perceived as > >> "magic"), is that the user explicitly configures attributes named > >> "some_collection" and "parent" on their classes, an they even have to > >> point them together at each other, and that the foreign key and > >> primary key columns that are involved in the persistence for the above > >> are also explicitly configured. This is to reduce the amount of > >> assumptions and implicit decisions SQLAlchemy has to make thereby > >> reducing the possibility of surprise. > >> > >> Now to the issue of casade_backrefs. This flag is in a tough spot, > >> because either way, it leads to a situation that can be non-intutive > >> (that is, surprising). Let's assume above we've turned it off, and > >> we do this: > >> > >> object_a = ObjectA() > >> object_b = ObjectB() > >> session.add(object_b) > >> object_a.some_collection.append(object_b) > >> assert object_b.parent is object_a > >> > >> What happens above when we persist object_b by calling > >> session.commit()? In fact the operation will fail, either silently > >> if the foreign key column is nullable, or explicitly if the column is > >> not nullable, because object_a will *not* be persisted and the foreign > >> key constraint on object_b cannot be satisfied. > >> > >> The reason we get into this issue in the first place is that > >> SQLAlchemy also has an explicit concept of a Session, e.g. a database > >> transaction, where objects must be explicitly associated with that > >> transaction, and that this Session uses a unit of work pattern, which > >> is exactly the thing here that reduces the exposure to "persistence > >> implications" that you refer towards. If we were working like other > >> ORMs, we'd just say "object_b.save()", where it likely would raise a > >> constraint error because we didn't call "object_a.save()" first, which > >> in SQLAlchemy's view is a significant persistence detail that is > >> needlessly exposed. SQLAlchemy's very first version in fact > >> didn't have a Session, and all of object_a/ object_b would be > >> automatically associated with an implicit database connection, very > >> much the way active record .save() style ORMs work. That was also > >> too magical. > >> > >> So the "cascade_backrefs" flag is kind of a "seam" in an otherwise > >> smooth surface. I would not characterize it as any more "magical" > >> than anything else, and instead the discussion should be about which > >> use case is less surprising. > >> > >> > >> > >> > >> > >> > > >> > </my two cents> > >> > > >> > -Lyla > >> > > >> > On Sun, Apr 28, 2019 at 10:54 PM James Fennell < > jamespfenn...@gmail.com> wrote: > >> >> > >> >> Thanks for the explanation Mike! Seeing it now, I actually think > there’s a decent reason to want the current backerefs: > >> >> > >> >> My understanding is that with session.merge in SQL Alchemy it’s > possible to draw a very clean line between entities that are persisted (or > about to be persisted on the next flush) and entities which will never be > persisted. This is owing to the design choice whereby SQL Alchemy doesn’t > persist the entity you pass into the merge; instead, that is kept alone and > a new entity is created. > >> >> > >> >> With this in mind, there are two ways to see Lyla’s example. > >> >> > >> >> One way: as soon as the tassel_thread was related to the persisted > my_head (persisted because of the line my_head=session.merge(my_head)) then > tassel_thread should be seen as in the session already. In this view, the > merge is necessary and possibly error-prone, as here. > >> >> > >> >> Another way: instead of assigning my_head=session.merge(my_head), > keep the unpersisted head around with say persisted_head = > session.merge(my_head). Then relating the new tassel_thread to my_head > won’t add it to the session. To get a record into the DB, then do a > session.merge on it - everything works correctly this way. > >> >> > >> >> > >> >> In both cases, there is the idea of a persisted object graph and a > distinct unpersisted object graph. Once you relate a new entity to > something in the persisted object graph, it becomes persistent. > >> >> > >> >> -- > >> >> 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 a topic in > the Google Groups "sqlalchemy" group. > >> >> To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy/oVVdbCzsNQg/unsubscribe. > >> >> To unsubscribe from this group and all its topics, 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. > >> > > >> > -- > >> > 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. > >> > >> -- > >> 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 a topic in the > Google Groups "sqlalchemy" group. > >> To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy/oVVdbCzsNQg/unsubscribe. > >> To unsubscribe from this group and all its topics, 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. > > > > -- > > 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. > > -- > 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 a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy/oVVdbCzsNQg/unsubscribe. > To unsubscribe from this group and all its topics, 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. > -- 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.