getting AR to do postgres jsbonb atomic updates

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

getting AR to do postgres jsbonb atomic updates

Jonathan Rochkind
It is now easy to store a hash serialized as jsonb in posgres.  It is even pretty easy with store_accessor to make individual keys in the hash look like separate attributes. 

But when AR decides the jsonb column needs to be updated, it will send the entire hash as an update value.  If you only changed a key or two, it will still send the entire hash -- possibly overwriting changes to other keys made by other processes/clients. 

Recent versions of postgres supports an atomic update on just certain keys of a jsonb hash, using `jsonb_set` in SQL. 

I'd like to explore trying to get AR to do this, at first with a plugin.  With dirty tracking, it's fairly straightforward for AR to figure out what top-level keys have been changed and what haven't. 

But I can't quite figure out where to intervene in AR to change the SQL generated in an 'update' operation. So I can generate different SQL that will do an atomic update, where possible. 

If anyone could give me any advice on where might work to hook into AR to change generated SQL for 'update', I would appreciate it!  And I hope to look into making a plugin to let AR do specified-key-only updates to postgres jsonb columns. Thank you for any advice!

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/rubyonrails-core.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: getting AR to do postgres jsbonb atomic updates

Matt Jones-15

On Mar 3, 2018, at 3:47 PM, Jonathan Rochkind <[hidden email]> wrote:

It is now easy to store a hash serialized as jsonb in posgres.  It is even pretty easy with store_accessor to make individual keys in the hash look like separate attributes. 

But when AR decides the jsonb column needs to be updated, it will send the entire hash as an update value.  If you only changed a key or two, it will still send the entire hash -- possibly overwriting changes to other keys made by other processes/clients. 

Recent versions of postgres supports an atomic update on just certain keys of a jsonb hash, using `jsonb_set` in SQL. 

I'd like to explore trying to get AR to do this, at first with a plugin.  With dirty tracking, it's fairly straightforward for AR to figure out what top-level keys have been changed and what haven't. 

But I can't quite figure out where to intervene in AR to change the SQL generated in an 'update' operation. So I can generate different SQL that will do an atomic update, where possible. 

The most central spot would be in `_update_record`:


or in the related `_substitute_values` method.

This could be too late to see things like dirty tracking, though - you may need to start searching up the call stack from `_update_record`.

—Matt Jones

If anyone could give me any advice on where might work to hook into AR to change generated SQL for 'update', I would appreciate it!  And I hope to look into making a plugin to let AR do specified-key-only updates to postgres jsonb columns. Thank you for any advice!

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/rubyonrails-core.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/rubyonrails-core.
For more options, visit https://groups.google.com/d/optout.