Back

You know what's fun? Values are encrypted by the ORM before being stored in the database. No way to use jsonb functions to merge documents. I quit. Too much time has been spent for no result.

PROBLEM SOLVED

Wow, after 3 days of debugging, I've finally solved the problem by creating a transaction and issuing a lock table (in exclusive mode), for only "patch" operations. Without rewriting this mess.

Now I can move on.

@khalidabuhakmeh There's MVC code with a model called Organization holding a big JSON blob in a database. Each time you want to update a sub-document in this document, the app fetches the whole document, merges it with the changes, creates a transaction to atomically delete the old and create the new document ("patch" operation). When you do it with hundreds of workers on the same document, the last patch operation wins.

@khalidabuhakmeh I've tried to create a transaction and a select for update (with_for_update() in the Query) but now the select returns no row. I've tried to use populate_exiting() in the executor to refresh values in the objects, without success. Everything is vendored in the app with old versions. On top of that, there's a home made library to manage the database. Nobody maintains this app. 100% hell.

@jriou Thanks for the explanation. Yeah, ORMs are good for quick prototyping, but there are edge cases like the ones you explained where I wouldn't hesitate to optimize to something more sane.

I come from the #dotnet space, and ORMs are "good" but have provided built-in functionality to execute raw SQL. Sometimes it's the best option.

The right thing to do is improve the user experience so your instinct is correct. :)