Quote from: CIYAM on December 05, 2016, 12:49:13 PM

That certainly is a surprise to me (thanks for the link and I deleted my own post to avoid any further confusion). Thinking about it more I guess it makes sense that in order to do a "rollback" it would need to be able to restore deleted data (as the size of a transaction could be huge it has to write all the recovery information and keep that at least until the commit has been completed and all final tx data has been flushed to disk).

But can you force rollbacks to occur after a successful commit (and especially after a checkpoint)?

Certainly I'm not familiar with any standard SQL commands for doing such a thing (and I wouldn't count nested transactions if supported as really being a solution) so I still don't see how you could use a traditional RDMBS to achieve a blockchain "re-org" (which is the main point that I was perhaps poorly trying to make).

Assuming that most RDBMS engines do work the same way regarding logging then perhaps it would at least be potentially possible to add some method of tagging the DB state at a certain "block height" and therefore later be able to "rewind" the DB back to the state it was at that specified tag (although I don't think that there is any such standard method).

Rollback is a bit of a red herring for an immutable block chain. Just store everything, good or bad, and adjust the query.

SQLite also has the ability to treat multiple, separate database files as a single database at run-time (max 64). One could create separate DB files for working DB and historical but I don't even see the need for that.

BTW. SQLite can insert 1,000,000 records in under 10 secs and wipes the floor with My/MSSQL and Postgres in terms of performance. What it doesn't have is authentication so the others are a better choice for web servers.