Re:MongoDB queries don’t always return all matching documents
by jeltz on Wednesday June 08, 2016 @09:17AM
(#11862052)
A common example is making sure that every event in a bookkeeping system balances to zero (e.g. three rows: -125 EUR bank, +100 EUR office materials, +25 EUR tax). To enforce this at the database level you either need to run at SERIALIZABLE, take a table lock, or do something horrible with your database structure (like putting all rows of the event in a JSON blob).
When I have solved this case I have done it in the application by making sure we are only using a small set of carefully audited stored procedures to modify the event table.
MongoDB does not even try to solve this kind of problem, and I am the wrong guy to ask if you want someone to try to make a case for using it anywhere.
Re:MongoDB queries don’t always return all matching documents
by tremon on Wednesday June 08, 2016 @09:27AM
(#11862105)
A common example is making sure that every event in a bookkeeping system balances to zero (e.g. three rows: -125 EUR bank, +100 EUR office materials, +25 EUR tax). To enforce this at the database level you either need to run at SERIALIZABLE, take a table lock, or do something horrible with your database structure (like putting all rows of the event in a JSON blob).
Or you deny direct table inserts, and provide a stored procedure for inserting multiple rows in one transaction. But I see how it could be useful in the generic case.
Re:MongoDB queries don’t always return all matching documents
by jeltz on Wednesday June 08, 2016 @10:10AM
(#11862358)
Which is exactly what I did when I had to solve the problem. :) And you are right in mentioning that the fact that you can restrict modification of the database to only certain stored procedures is a great help here.
Re:MongoDB queries don’t always return all matching documents
by snaky on Wednesday June 08, 2016 @02:06PM
(#11864212)
With on commit triggers or deferrable check constraints it would be easier, but in Oracle you could use a materialized view with "on commit refresh" and add triggers to this MV.
Re:MongoDB queries don’t always return all matching documents
by snaky on Wednesday June 08, 2016 @08:55AM
(#11861900)
You don't have to avoid such constraints if they are natural to data model actually. It's much faster (and safer, thanks to transactions) to check new data against some old data when the checking code is as close to the data as possible, instead of make a couple additional requests between DBMS and the business logic layer.
Also: how do you enforce such constraints with MongoDB exactly? If the answer is "do it in the application", then your answer applies to relational databases too.