Thanks, it is all clear now. Just out of interest, what is the point of synchronize_session='fetch'?
For me all it does is a simple SELECT maps.id AS maps_id FROM maps WHERE maps.id = %(id_1)s All I get as a return value is 0: not successful (probably id didn't exist), while 1: successful. It is the same behaviour both with 'fetch' and False. Zsolt On 15 May 2017 at 16:33, mike bayer <mike...@zzzcomputing.com> wrote: > > > On 05/15/2017 10:31 AM, Zsolt Ero wrote: >> >> I'm trying to run your example, but it doesn't work: >> >> from sqlalchemy import func >> >> m = request.dbsession.query(models.Map).get(3) >> m.screenshots = func.jsonb_set(m.screenshots, '{key}', '"value"') >> request.dbsession.flush() >> >> It ends up in a (psycopg2.ProgrammingError) can't adapt type 'dict'. > > > jsonb_set(models.Map.screenshots, ...) > > because this works against the column, not the value > > > > > >> >> Also, from the generated SQL it seems to me that it's also doing the >> full JSONB update from client side, not just inserting a key into the >> database server side. >> >> UPDATE maps SET screenshots=jsonb_set(%(jsonb_set_1)s, >> %(jsonb_set_2)s, %(jsonb_set_3)s) WHERE maps.id = %(maps_id)s >> {'maps_id': 3, 'jsonb_set_3': '"value"', 'jsonb_set_2': '{key}', >> 'jsonb_set_1': {u'small': u'2ad139ee69cdcd9e.jpg', u'full': >> u'68b3f51491ff1501.jpg'}} >> >> On 15 May 2017 at 16:18, Zsolt Ero <zsolt....@gmail.com> wrote: >>> >>> 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 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.