With respect to SQLite 3.7.6
Situation:
- incremental vacuum mode
- old "journal" mode (have not tested with WAL)
- extra tail-room using SQLITE_FCNTL_CHUNK_SIZE
- some free pages
- execute PRAGMA incremental_vacuum
Problem:
- the journal will be created for all extra tail pages
This is just a performance issue, no database corruption, and the
incremental_vacuum is done well.
However, such a situation is not uncommon. SQLITE_FCNTL_CHUNK_SIZE may be
used to prevent
on disk fragmentation, and incremental_vacuum(...) is used for *quick*
background database
compacting.
Reproduction:
- new database
- PRAGMA auto_vacuum = 2
- SQLITE_FCNTL_CHUNK_SIZE with 1 GiB (huge, just for demonstration)
- CREATE TABLE test (id INTEGER)
- DROP TABLE test
- PRAGMA incremental_vacuum(1)
- a journal of 1 GiB will be created
Workaround we are using:
- PRAGMA page_count
- PRAGMA page_size
- close database
- truncate database file (page_count * page_size)
- reopen database
- PRAGMA incremental_vacuum
Proposed solution:
incremental_vacuum should only consider the pages before the last used page
and issue a truncate to the VFS for the final required size (i.e. the size
up to the last used page),
which may again be rounded up by the VFS to the next multiple of
SQLITE_FCNTL_CHUNK_SIZE.
This will allow incremental_vaccum to be used for quick partial compacting
in the background
and also recover disk space in multiples of SQLITE_FCNTL_CHUNK_SIZE.
Kind regards,
Frans van Dorsselaer
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
