On Fri, Oct 5, 2018 at 5:55 PM Chris Brody <chris.br...@gmail.com> wrote:
> Savepoints (<https://www.sqlite.org/lang_savepoint.html>)? Savepoints are still part of a transaction, so not visible to readers until the COMMIT. Daniel's use case wants each transaction to become visible to readers, so savepoint do not apply here. Daniel wants regular transactions to become savepoints basically. Sounds a bit like Oracle's configurable RETENTION policy on UNDO/REDO logs. Which allow SELECT ... AS OF (timepoint | SCN), i.e. queries in the past. You don't mention it Daniel, but if you can rollback to a given TX, you can also query at the time of that TX. This is an interesting use-case. But the WAL file is changes to the DB not yet in the DB file. After checkpointing, the DB file is updated with those changes, but even if you keep the part of the WAL that was checkpointed, you still cannot go back in time. You need the "reverse" of what's in the WAL file, i.e. the "UNDO" log, the old values from the DB file overwritten during checkpointing. Or you don't have a DB file at all anymore, and the WAL file *is* the DB. But then the older your DB gets (the more TX are added), the slower its gets. So basically: 1) WAL mode only has REDO logs. 2) JOURNAL mode only has UNDO logs. And your requirements call for both REDO and UNDO logs IMHO. I'm not an SQLite or Oracle expert, thus take the above with a grain of salt :). --DD _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users