Hi, I've been running the latest version of (unreleased) sqlite 3.7.6 from http://www.sqlite.org/draft/download.html and in particular looking at the new improved "pragma wal_checkpoint". However, it doesn't seem to be doing what I hoped it would do. I was expecting that following a pragma wal_checkpoint=reset the -wal file would be truncated back to 0 bytes. This seems not to happen, as in the following example:
SQLite version 3.7.6 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> pragma journal_mode=wal; wal sqlite> create table t(i); sqlite> begin; sqlite> insert into t values (zeroblob(20000000) || 'a'); sqlite> insert into t values (zeroblob(20000000) || 'a'); sqlite> insert into t values (zeroblob(20000000) || 'a'); sqlite> insert into t values (zeroblob(20000000) || 'a'); sqlite> insert into t values (zeroblob(20000000) || 'a'); sqlite> rollback; sqlite> .headers on sqlite> pragma wal_checkpoint=reset; busy|log|checkpointed 0|3|3 sqlite> At this point the -wal file is still 100Mb. This is a trivial example, but it is a problem in the real-world version where the -wal file can grow very large when transactions encapsulate large amounts of data. Since disk space is precious, however, and large transactions like this are not so common, it would be good to be able to have a pragma wal_checkpoint which actually truncated the -wal file back to 0 bytes. The documentation for "wal_checkpoint reset" (sidenote: it is called 'restart' in the wal_checkpoint_v2() documentation), says that on success the next "write to the database file restarts the log file from the beginning". My suggestion would be to have a "wal_checkpoint truncate" which works the same as "wal_checkpoint reset" except that, on success, it should additionally and immediately then truncate the -wal file to 0 byte (or delete it maybe?). I think I remember reading somewhere in the wal documentation that the -wal file is kept at its peak size intentionally for performance reasons, and this is great, but it would be good to be able to override this behaviour where desired by the particular application. It is not possible simply to close the database connection to release the -wal file since multiple reader threads are running on the database at all times. Is such a thing possible? What would be very nice would be a flag inside the database that is set by the checkpoint pragma when it was unable to do a full checkpoint and then for it to automatically re-attempt the checkpoint when it detects the last open transaction closing. I don't think this is the same function that the wal_autocheckpoint pragma provides? Many thanks Andy _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
