Re: [sqlite] journal_size_limit is ignored when using SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE

[Kniep Stefan (CM/ESN3)]

Hi Simon,

thanks for the hint. I won't do that.
--
Stefan


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] journal_size_limit is ignored when using SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE

[Simon Slavin]

On 20 Nov 2017, at 3:41pm, Kniep Stefan (CM/ESN3)  
wrote:

> P.S.: As a test, I have modified sqlite3WalClose() (and the place where it is 
> called) to delete/truncate the WAL when journal_size_limit is reached. I had 
> to change only two lines, but of course I am not sure that my change doesn't 
> break anything.

Do not delete the WAL file unless all connections to the database have been 
closed properly — without crashes, and without an error code being returned to 
SQLite.  Having an empty WAL file does not do exactly the same thing as having 
no WAL file.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] journal_size_limit is ignored when using SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE

[Kniep Stefan (CM/ESN3)]

I'm working on a database application that is shut down quite often. At each 
shutdown, only a small amount of data is written to the DB, which is in WAL 
mode. Due to HW restrictions, I had to reduce the total amount of data written 
to disk, which is why I have activated SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE.

This works fine as long as TRUNCATE checkpoints are issued from within my code. 
However, since the amount of data that is written into the DB at each shutdown 
varies, it is suboptimal to just trigger the checkpoint at each n-th shutdown. 
To get a better trade-off between the amount of written data and WAL size, it 
would be nice to trigger the checkpoints based on the size of the WAL. It seems 
that PRAGMA journal_size_limit doesn't help here: the WAL file grows infinitely 
when no checkpoints are issued from within my code. Is this the expected 
behavior (I guess so) or a configuration problem?

Note that PRAGMA wal_autocheckpoint also doesn't help, but this is pretty 
obvious because it only triggers a PASSIVE checkpoint. I would rather not 
access the WAL file directly (bypassing SQLite) for checking it's size. Does 
anybody have a proper solution for this problem?

P.S.: As a test, I have modified sqlite3WalClose() (and the place where it is 
called) to delete/truncate the WAL when journal_size_limit is reached. I had to 
change only two lines, but of course I am not sure that my change doesn't break 
anything.
--
Stefan Kniep

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users