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.
