Serious question: what prompts you to consider these things? Is sqlite being too slow for you?
On Thu, Apr 5, 2018 at 10:00 AM Pavel Cernohorsky < pavel.cernohor...@appeartv.com> wrote: > Hello Dan, thank you very much for clearing this up, because that was my > important misunderstanding. > > Risking corruption when the OS crashes is not really an option for me. I > will probably go for what David Raymond suggested in one of other posts, > or I may also solve my whole problem on the application level and have 2 > databases. One in memory only, which holds "all the changes since the > last checkpoint", the second one on disk with all the data up to the > last checkpoint. The character of the data I need to store allows this - > I can first query the in-memory database for the most recent results, if > I do not find them, I can query the on-disk database. My manual > checkpoint will then be simply "writing everything from my in-memory > database to my on-disk database in a single transaction". But my first > choice will be the VFS David suggested. > > Thanks, > > Pavel > > > On 04/05/2018 09:28 AM, Dan Kennedy wrote: > > On 04/05/2018 02:08 PM, Pavel Cernohorsky wrote: > >> Hello Dan, so you are saying that if WAL is somehow in memory only > >> (not properly persisted) and app or OS crashes in the middle of the > >> checkpoint operation, my main database file will get corrupted? And > >> by corrupted you mean as in "you will loose changes which were in the > >> WAL file", or "you will end up with unusable main database file, or > >> file where rows which were affected by the checkpoint will have wrong > >> contents (halfway through written, ...)". In other words, I may end > >> up with the main database file in some other state than just "like > >> before checkpointing" or "like after checkpointing"? I understood > >> checkpointing as a kind of atomic operation which "merges data in the > >> main database file and in the WAL". Is that understanding wrong? > > > > That's correct. If you crash mid-checkpoint and lose the wal file, > > some future queries may return inconsistent results or SQLITE_CORRUPT > > errors. > > > > The suggestion made in another post to put the wal file on a tmpfs or > > similar file-system is a good one. Then you will only risk corruption > > if the OS crashes. There is no easy way to do that at the moment > > though, you will have to do some hacking to get it to work. > > > > Dan. > > > > > > > > > > > >> > >> Thanks, Pavel > >> > >> > >> On 04/04/2018 06:33 PM, Dan Kennedy wrote: > >>> On 04/04/2018 09:01 PM, Pavel Cernohorsky wrote: > >>>> Hello, does anybody know if there is some possibility to not have > >>>> WAL file as a normal file on the disk, but only in memory? I > >>>> understand that all the modifications to the database would get > >>>> lost in case of the application / OS crash, but for my application, > >>>> I only need the level of durability based on checkpointing. I just > >>>> need to guarantee that all the data are properly written to the > >>>> main database and synchronized to disk when manual (or even > >>>> automatic) WAL checkpoint is called, but I do not care if I loose > >>>> data in between the checkpoints. Of course database should never > >>>> get corrupted. > >>> > >>> If your app or the OS crashes halfway through a checkpoint and this > >>> means that the WAL file is lost, the database is likely to be > >>> corrupted. Is that a problem? > >>> > >>> Dan. > >>> > >>> > >>>> > >>>> My goal is to limit the number of IOps being performed to the disk. > >>>> Currently I use "PRAGMA synchronous = 1" and there is only one > >>>> process manipulating the database (multiple reader threads, only > >>>> one writer thread at one moment in time). Or if it is not possible > >>>> to have WAL in memory only, is there something like “PRAGMA > >>>> wal_synchronous = none_and_delete_wal_if_corrupted”? > >>>> > >>>> Thanks for suggestions, kind regards, > >>>> Pavel > >>>> > >>>> > >>>> _______________________________________________ > >>>> sqlite-users mailing list > >>>> sqlite-users@mailinglists.sqlite.org > >>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >>> > >>> > >>> _______________________________________________ > >>> sqlite-users mailing list > >>> sqlite-users@mailinglists.sqlite.org > >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >> > >> _______________________________________________ > >> sqlite-users mailing list > >> sqlite-users@mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
