Thread safe JSONB updates

rubyonrails-talk
1

Say I have a model Test using JSONB, with a column data

In the database I have data equal to: { "key_1": 1, "key_2": { "foo": "bar" }

Now say there are two employees working on info that updates one of the two keys:

employee_a submits an update that sets { "key_1": 2 }

At the same time, employee_b submits an update that sets {"key_2": { "bar": "foo" }

Now, ideally, what the data column would hold is: { "key_1": 2, "key_2": { "bar": "foo" }

However, because this is all saved with serializers, what is going to be set in the database will depend entirely on which update was submitted last

How would I start creating a patch to use postgresql’s native functions to update specific keys in a json store? What I was hoping to create is functionality within the update process, as well as within the attributes API, to detect specifically what keys (including nested) have been changed, and generate the proper update query to only modify those keys

i.e.

employee_a causes the following query: UPDATE tests SET data = jsonb_set(data, '{key_1}', '2', true)

and

employee_b causes the following query: UPDATE tests SET data = jsonb_set(data #- '{key_2,foo}', '{key_2,bar}', '"foo"', true)

2

This doesn’t solve your merge problem but just wanted to mention you can use optimistic locking to at least prevent the error. Especially if the lock_version is part of your form as a hidden field.

This will cause a user’s save to only succeed if their data is current. If not they get an error and can go back and try again. While not an elegant experience if the chance of contention is small it might be good enough for a lot less work.

2 Likes
3

while that would most likely solve the issue for the most part, I’d still be interested in writing a gem or patch to allow this kind of functionality; I’m just not familiar enough with where the actual update SQL is generated

4

I would question is merging is generally the desired action to take. I could also see certain applications actually wanting an wholesale replacement of whatever data is there. I.E. the behavior might be app dependent.

For this reason it doesn’t seem it would be ideal to integrate it into the base functionality. It seems if you are wanting it to be merged it would need some sort of special API or flag to indicate this IMHO.

5

Yeah, never claimed it is how everyone wants things processed. Doesn’t mean there’s no one this would be useful to. I’m not looking for a pros and cons list of whether people need this. I want this functionality, and want to build it. Whether or not it would ever be part of rails core.

All I’m looking for is what part of the activerecord code base I should be starting with to create a plugin to allow this. What part of the update process would be best suited to hook into

6

For anyone interested, I’m adding the functionality to this gem until I have time to separate the plugins: https://github.com/SampsonCrowley/associate_jsonb

Lots of testing still to be added