News for hackers. Stuff that matters.

A comment on MongoDB queries don’t always return all matching documents. Parent comment

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.

[ Parent | on HN ]
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.

[ Parent | on HN ]
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.
[ Parent | on HN ]
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.
[ Parent | on HN ]