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.
