I'm not very familiar with the JSON datatype in postgres. Is it possible to update subvalues without writing the entire JSON bag?
For example: {"a": 1, "b": 2} (in psuedocode)
User 1: select JSON
User 2 select JSON
User 1: update JSON set "a" = 11
User 2: update JSON set "b" = 22
What would the value of the JSON column be after that. In other systems I've worked in (where it's JSON in a database column), the answer is {"a": 1, "b", 22} or a failure on the second update letting the client know that their copy is out of date.
I'd love to know if postgres can deal with this in the normal relational way so that the result is actually {"a": 11, "b": 22}
Depending on at which point these transactions are started and committed and the isolation level of the transaction this would either result in {"a": 1, "b", 22}, failure at step 3, failure at step 4 or failure when one of the transactions is committed. However, since locks are on row level rather than on value (cell) level, this would be the same even if these values were stored separately in ordinary column types.
You could still achieve what you want by using read committed transaction isolation and keeping your values in separate rows, e.g. a row with a/1 and a second row with b/2. But basically you would need a separate record for every property in your object graph that you want this behaviour to apply to, which could get pretty messy. Does that make sense to you?
For example: {"a": 1, "b": 2} (in psuedocode)
User 1: select JSON
User 2 select JSON
User 1: update JSON set "a" = 11
User 2: update JSON set "b" = 22
What would the value of the JSON column be after that. In other systems I've worked in (where it's JSON in a database column), the answer is {"a": 1, "b", 22} or a failure on the second update letting the client know that their copy is out of date.
I'd love to know if postgres can deal with this in the normal relational way so that the result is actually {"a": 11, "b": 22}