Thanks for the answer. My use case is the following: I have an object (map_obj), which has screenshots in two sizes. I'm using JSONB columns to store the screenshot filenames.
Now, the two screenshot sizes are generated in parallel. The code is like the following: map_obj = query(...by id...) filename = generate_screenshot(size) # long running screenshot generation try: dbsession.refresh(map_obj, ['screenshots']) map_obj.screenshots = dict(map_obj.screenshots, **{size: filename}) except Exception as e: logger.warning(...) It worked well for 99.9% of the cases. The problem is that in the rare case when both screenshots got rendered within a few milliseconds, one of the screenshots got lost. The simple solution was to add lockmode='update' to the refresh, so this way the refreshes are blocking until the other finishes the update. But since this means locking a full row, I was thinking a simple JSONB insertion would probably be better, since I can avoid locking the row. Zsolt On 15 May 2017 at 15:58, mike bayer <mike...@zzzcomputing.com> wrote: > > > On 05/15/2017 09:32 AM, Zsolt Ero wrote: >> >> In PostgreSQL 9.5+ it is finally possible to modify a single key inside a >> JSONB column. Usage is something like this: >> >> update maps set screenshots=jsonb_set(screenshots, '{key}', '"value"') >> where id = 10688 >> >> Is it possible to write this query using the ORM somehow? If not, please >> take it as a feature request. > > > > You can use that function directly: > > my_object = session.query(Maps).get(5) > > my_object.screenshots = func.jsonb_set(my_object.screenshots, '{key}', > '"value"') > > session.flush() > > > as far as "transparent" ORM use of that, like this: > > my_object.screenshots[key] = "value" > > right now that is a mutation of the value, and assuming you were using > MutableDict to detect this as an ORM change event, the ORM considers > "screenshots" to be a single value that would be the target of an UPDATE, > meaning the whole JSON dictionary is passed into the UPDATE. There is no > infrastructure for the ORM to automatically turn certain column updates into > finely-detailed SQL function calls. I can imagine that there might be some > event-based way to make this happen transparently within the flush, however, > but I'd need to spend some time poking around to work out how that might > work. > > > I'm not familiar with what the advantage to jsonb_set() would be and I can > only guess it's some kind of performance advantage. I'd be curious to see > under what scenarios being able to set one element of the JSON vs. UPDATEing > the whole thing is a performance advantage significant compared to the usual > overhead of the ORM flush process; that is, Postgresql is really fast, and > for this optimization to be significant, you probably need to be calling the > Core function directly anyway rather than going through the whole ORM flush > process. But this is all based on my assumption as to what your goal of > using this function is. > > > >> >> >> >> -- >> 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 >> <mailto:sqlalchemy+unsubscr...@googlegroups.com>. >> To post to this group, send email to sqlalchemy@googlegroups.com >> <mailto: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/hjjIyEC8KHQ/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.