3

I have changed value of auto_vacuum PRAGMA of my sqlite database to INCREMENTAL. When I run PRAGMA incremental_vacuum; through 'DB Browser for SQlite' application it frees all the pages in the free_list. But when I am running same statement using any SQLite library in C# (e.g. Microsoft.Data.SQLite), it frees only one page from the free_list

I verified this by getting the number in current free_list by running PRAGMA freelist_count before and after running PRAGMA incremental_vacuum statement.

Also I tried passing different parameters to incremental_vacuum pragma e.g. PRAGMA incremental_vacuum(100) but it would still free only one page.

Please let me if what is it that I am doing wrong here.

Thanks in advance!

Shashank Choudhary
  • 31
  • 2
  • Did you run a `VACUUM` after changing the `auto_vacuum` setting? – Shawn Dec 12 '18 at 16:43
  • @Shawn Yes i did. I can see in the PRAGMAs of database that auto_vacuum value is INCREMENTAL. without calling VACUUM it will not allow the value to change. And also the `incremental_vacuum` runs fine through `DB Browser for SQLite` application – Shashank Choudhary Dec 12 '18 at 19:00
  • In my case I get only one page removed in SQLiteStudio – Alex Che Apr 29 '19 at 15:18

1 Answers1

1

This happens if one ignores the result from sqlite3_step(). If it returns SQLITE_ROW, the code cannot act as if it had returned SQLITE_DONE. One has to keep calling sqlite3_step() until the query is done: pragma or not, a query is a query and it isn't done until it's, well, SQLITE_DONE :). It happens to remove one page at a time:

bool do_incremental_vacuum(sqlite3 *db) {
   sqlite3_stmt *stmt = nullptr;
   auto rc = sqlite3_prepare_v2(db, "pragma freelist_count;", -1, &stmt);
   if (rc != SQLITE_OK) return false;
   rc = sqlite3_step(stmt);
   if (rc != SQLITE_DONE && rc != SQLITE_ROW) return false;
   auto freelist_count = sqlite3_column_int64(stmt, 0);
   rc = sqlite3_errcode(db);
   if (rc != SQLITE_OK && rc != SQLITE_DONE && rc != SQLITE_ROW) return false;
   std::cout << "There are " << freelist_count << " pages in the free list." << std::endl;

   rc = sqlite3_prepare_v2(db, "pragma incremental_vacuum;", -1, &stmt, nullptr);
   uint64_t pages = 0;
   if (rc != SQLITE_OK) return false;
   do {
      rc = sqlite3_step(stmt);
      if (rc == SQLITE_ROW) ++ pages;
   } while (rc == SQLITE_ROW);
   if (rc != SQLITE_DONE) return false;

   std::cout << "Freed " << pages << " pages" << std::endl;
   return true;
}
Kuba hasn't forgotten Monica
  • 95,931
  • 16
  • 151
  • 313
  • 1
    Thank you, this was helpful. My C# version based on your suggestion (sorry for bad code formatting): `var command = new SQLiteCommand("pragma incremental_vacuum", _db); using (var reader = command.ExecuteReader()) { while (reader.Read()) { ++result; } Logger.WriteLine($"{result} pages removed by the incremental vacuum command"); }` – Mar Jan 06 '21 at 13:29