Hi! On 2018-10-05 18:30, Dominique Devienne wrote: > 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.
Yes, savepoints sound very close to what I need -- except that I need the current state to be visible. (And all changes to be persistent; I'm not sure if a savepoint in an uncommitted transaction is really persistent?) Note, though, that I only have a single-user setting. In other words, I do not need "other" reads to see my changes. It would be enough if I can create savepoints and the same process / connection afterwards sees the updated state even before the transaction is committed. Is that the case? (I could imagine it is.) > 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. My particular usecase does not need queries in the past, only rollbacks to that state. But yes of course, if you have that ability, you can also query at a past snapshot. (In fact, that's what my understanding of WAL mode already enables? Just not the rollbacks.) > 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. Ok, but from my understanding, the "current view" that SQLite exposes of the DB is actually the real DB file + any changes from the WAL. So that gives me the current state, which is all I need. And from the docs it seems that with WAL mode, it also allows me to keep readers open at older revisions -- but again only for reading, and not for restoring the old state. (Although that would in theory be "just" truncating the WAL file, right?) > 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. Yes exactly. In principle that may be fine for me, but as I don't need very old revisions anymore, it would be nice if I could partially checkpoint those. There's a project called litetree [1], which AFAIK supports all I need except that it keeps old revisions forever. (And of course I'd rather go with the officially maintained SQLite if possible than with some fork.) [1] https://github.com/aergoio/litetree > 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 Actually, I would only need UNDO logs (as written by Keith in another reply). Does JOURNAL mode actually support a "persistent" journal that allows me to roll back later? Or is that only temporarily and limited while I create an individual transaction to the database? Yours, Daniel -- https://www.domob.eu/ OpenPGP: 1142 850E 6DFF 65BA 63D6 88A8 B249 2AC4 A733 0737 Namecoin: id/domob -> https://nameid.org/?name=domob -- 3.6.0: Bar-Pri-Ran-Rog-Sam-Val-Wiz To go: Arc-Cav-Hea-Kni-Mon-Tou
signature.asc
Description: OpenPGP digital signature
_______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
