So how about the "pragma wal_trim" that does a checkpoint, grabs the lock, trims, and drops the lock?
Or some other new keyword? The WAL file never gets trimmed (just reused apparently) so having an explicit way to trim it without having to close and reopen the database would seem quite handy. And...would also address the FAQ of "why doesn't my wal file shrink?" with a simple "use 'pragma wal_trim but note that it will block readers and writers' Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems ________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Richard Hipp [d...@sqlite.org] Sent: Wednesday, November 30, 2011 12:59 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] WAL file size On Wed, Nov 30, 2011 at 1:21 PM, Black, Michael (IS) <michael.bla...@ngc.com > wrote: > Maybe we need a "pragma wal_trim" ?? This would automatically trim it > after a checkpoint? Or make it an explicit action? > checkpoint cannot trim. This is because of the rule that checkpoints will never block either readers or writers, and a trim operation requires getting a write lock, and hence as the potential to block writers. That's why the trim is done by the next writer - since it already has to get a write lock and hence has already dealt with any concurrency issues. > > > > Waiting for another write before truncating it seems too late and not a > logical thing to expect. > > > > > > Michael D. Black > > Senior Scientist > > Advanced Analytics Directorate > > Advanced GEOINT Solutions Operating Unit > > Northrop Grumman Information Systems > > ________________________________ > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] > on behalf of Richard Hipp [d...@sqlite.org] > Sent: Wednesday, November 30, 2011 12:10 PM > To: Teg; General Discussion of SQLite Database > Subject: EXT :Re: [sqlite] WAL file size > > On Wed, Nov 30, 2011 at 1:04 PM, Teg <t...@djii.com> wrote: > > > Hello Richard, > > > > When does the WAL get trimmed down? If my transaction completes, does > > the WAL file get deleted? I've seen the WAL grow to 4 times my actual > > DB size so, I don't really want 160 Gigs of WAL hanging out when the > > process that generated it is only run once a month. > > > > SQLite starts writing the WAL file from the beginning again on the first > write transaction that follows a checkpoint that ran to completion and > where there are no readers using the WAL file. A checkpoint will normally > run to completion if there are no readers still using prior transactions. > If there are readers on prior transactions, then the checkpoint cannot run > to completion since that would delete content out from under the readers. > > > > > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
