Pony expects that concurrent requests are processed in transactions, each parallel transaction decorated with db_session, and have separate connection to the database. Pony handles connection under the hood, each connection associated with current thread (or current process if the application is single-threaded). In async framework all coroutines use the same thread, so the approach that connections are tied to specific threads does not work. It may lead to situation that different coroutines use the same connection, which may break transaction boundaries. Also, async code can lead to enormous amount of concurrent coroutines with parallel transactions, which are very long comparing with transactions in a usual application. Database server does not work well in situation, when there are thousands of concurent long-living transactions. But it seems that many people who choose to use async approach does not understand what does it mean from the database point of view. The just say, "hey, async code should definitely be better, because it async", and don't think about transactions and all that database stuff. For async application it is better to have separate database access layer which communicates with the rest part of application using queues, but such architecture is much more complex than the usual synchronous approach.

Can you provide more details on what are you looking for? @YoungNastyman

something to simulate withdrawals and deposits from a bank account, user management etc.

Is it going to be an app?

Is there a way to "properly" insert a new related object i using raw sql? Consider this code. There's already a single parent created in the db. Uncommenting the # parent.children.load() line leads to pony.orm.core.UnrepeatableReadError: Phantom object Child[19] appeared in collection Parent[1].children is there a way to avoid that without rolling back the session? E.g. this happens without explicit children.load if parent is a newly created and committed instance instead of fetched-by-id one. I'm using raw_sql insert because I want to use ON CONFLICT DO NOTHING to implement get-or-create that doesn't require a rollback. I want to avoid rollback because i'm using long-ish lived db sessions and re-fetching all objects after a rollback seems very very inconvenient if at all possible.

Is there a way to "properly" insert a new related object i using raw sql? Consider this code. There's already a single parent created in the db. Uncommenting the # parent.children.load() line leads to pony.orm.core.UnrepeatableReadError: Phantom object Child[19] appeared in collection Parent[1].children is there a way to avoid that without rolling back the session? E.g. this happens without explicit children.load if parent is a newly created and committed instance instead of fetched-by-id one. I'm using raw_sql insert because I want to use ON CONFLICT DO NOTHING to implement get-or-create that doesn't require a rollback. I want to avoid rollback because i'm using long-ish lived db sessions and re-fetching all objects after a rollback seems very very inconvenient if at all possible.

Yes, after rollback() session cache is invalidated and all previous objects are unaccessible >>> from pony.orm.examples.university1 import * >>> with db_session: ... s = Student[1] ... rollback() ... print(s.group.dept.name) ... Traceback (most recent call last): ... pony.orm.core.DatabaseSessionIsOver: Cannot load attribute Group[101].dept: the database session is over

Re-fetching objects which were created in previous db_session may be an interesting idea, but it is tricky to implement correctly. We need not only to fetch attributes, but also to add this object to current db_session. It is possible that current db_session already has an instance which represents Student[1], and in Python we cannot merge two different instances into a single one. Maybe in that case Student[1] from previous db_session should become something like a transparent proxy for Student[1] from the current db_session. It is possible, but may be a bit tricky to implement

Pony expects that concurrent requests are processed in transactions, each parallel transaction decorated with db_session, and have separate connection to the database. Pony handles connection under the hood, each connection associated with current thread (or current process if the application is single-threaded). In async framework all coroutines use the same thread, so the approach that connections are tied to specific threads does not work. It may lead to situation that different coroutines use the same connection, which may break transaction boundaries. Also, async code can lead to enormous amount of concurrent coroutines with parallel transactions, which are very long comparing with transactions in a usual application. Database server does not work well in situation, when there are thousands of concurent long-living transactions. But it seems that many people who choose to use async approach does not understand what does it mean from the database point of view. The just say, "hey, async code should definitely be better, because it async", and don't think about transactions and all that database stuff. For async application it is better to have separate database access layer which communicates with the rest part of application using queues, but such architecture is much more complex than the usual synchronous approach.

Also, rollback() inside of db_session currently commonly used as a way to forget and garbage collect previously loaded objects, at least ones which does not have direct references from user code, so we don't want to keep full identity map of objects which are loaded before rollback()

I just added support of volatile Set attributes, you can check it on GitHub https://github.com/ponyorm/pony/commit/54b0a97fa5602980ef1dd368336a08ad89da4748

Cache wipe is ok and probably even required but having to manually refetch stuff makes rollback pretty much unusable for a user. At least not usable in "inner" functions.

In hindsight it is really sad that table building, query building and actually running the code aren't more modular. Otherwise I could use Pony to write a pythonic query but let asyncpg execute it.

Actually, we really need to add native support for upserts, this would make most of this problems obsolete

Cache wipe is ok and probably even required but having to manually refetch stuff makes rollback pretty much unusable for a user. At least not usable in "inner" functions.

Rollback is not something you can do in normal situation. Usually after rollback a function raises exception and exits. Rollback lead to transaction termination. After rollback a new transaction is started, and in-between these transactions database tables could be modified by concurrent transactions, so we cannot keep old query results and use them in new transaction. And inner functions is definitely not a place to do rollback, if the inner function does not have corresponding raise for some exception. Some databases (PostgreSQL including) have a concept of nested transactions, so theoretically it would be possible to start sub-transaction in inner function and then rollback this sub-transaction only and continue execution of the main transaction. But it may be hard to support from an ORM, because on encountering rollback of sub-transaction, the ORM needs to rollback its in-memory cache of loaded objects as well to the moment where the sub-transaction started, and it is hard to do that rollback(clear_cache=False) is possible to implement, but I'm not sure all users who will try to use it will understand consequences on isolation: it is possible that objects were changed in database by concurrent transaction. On the other side, Pony support of optimistic checks can make this problem a bit less critical, as object state will be checked during the UPDATE execution But I think that proper support of ON CONFLICT would solve most of these problems

> in-between these transactions database tables could be modified by concurrent transactions I was under impression that can happen anyway because pony uses optimistic concurrency control

I just added support of volatile Set attributes, you can check it on GitHub https://github.com/ponyorm/pony/commit/54b0a97fa5602980ef1dd368336a08ad89da4748