On Wednesday, May 16, 2012 1:28:17 e.h. Simon Slavin wrote: > On Tue, May 15, 2012 at 1:55 PM, Charles Samuels <char...@cariden.com>wrote: > > At some point, we get a checkpoint; at this instant, what is in otherdb > > and what is in sqlite is what we want committed to sqlite, if either of > > them fails, we can rollback both of them and both databases return to a > > consistent > > state of a previous checkpoint. The problem is that in the time between > > checkpoint 1 and checkpoint 1 being committed to disk, more data is > > arriving. > > You will find that in almost all situations where either of them failed, > whatever method you're using to protect the integrity of your data will > fail too, because it will depend on the same thing. Not only that, but > that your method of assuring synchrony is likely to add lots of > complication which will make the whole system fail more than a simple > one-SQL-engine implementation would. For data integrity of the type you > describe, there's nothing much you can do short of using duplicate servers > talking to RAIDs with redundancy.
Sorry, I misspoke here a little bit and I think it's confusing you to the actual problem I'm having. To clarify: After I do a commit to "otherdb" - I'm *still* able to atomically roll it back, simply because I keep history of its previous versions in the sqlite db. I have it such that if the sqlite database itself rolls back, then the data in otherdb automatically does as well (it involves lots of COW pages and such). This is a solved problem. The real problem is that while I'm waiting for "otherdb" to synchronize, more data is still coming in to this entire solution. Otherdb is able to take that data and put it in a "future version", but sqlite can't. After otherdb synchronizes (i.e., with fsync), I can commit the sqlite db and even if otherdb has more stuff of a "future version" added to it, the sqlite database causes that "future data" to be safely ignored. However, while I can safely add to otherdb's "future data", there's no place to add it into SQLite, as I still haven't committed sqlite's "present version" - I can't do that until otherdb reports that it's committed its entire present version. > [...] > > To reduce the complication you have already noted, I recommend you try to > divorce the versioning system from the code of your project. Try to write > yourself a database library which would be useful for many projects, > rather than have any one routine which includes both knowledge about your > project and knowledge about how versioning works. I have already done that; I have SQL-friendly data stored in SQLite, and non- SQL data stored in otherdb, but either I can't accept new data while waiting for otherdb to do its full fsync (because I have nowhere to put the sqlite- side of things), or I need a way to have sqlite not commit the stuff from between the start of otherdb's checkpoint and the point that it finishes its fsync. > > Another way to do it is simply to use ROLLBACK points as defined in the SQL > standard and implemented by SQLite. We already know that they work > correctly and there is a lot of documentation on their use and drawbacks. If by rollback points, you mean savepoints; then they're simply not useful if they discard (as opposed to giving me back the data) the data that is being rolled-back. To keep that data, I would need a shadow-database of some sort, which would of course require me to have many many routines that include "knowledge of my project and knowledge of how versioning works". Charles _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
