sqlite增量真空仅删除一个空闲页面 [英] sqlite incremental vacuum removing only one free page

问题描述

我已将我的 sqlite 数据库的 auto_vacuum PRAGMA 的值更改为 INCREMENTAL.当我通过DB Browser for SQlite"应用程序运行 PRAGMAincremental_vacuum; 时，它会释放 free_list 中的所有页面.但是当我使用 C# 中的任何 SQLite 库(例如 Microsoft.Data.SQLite)运行相同的语句时，它只从 free_list

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

我通过在运行 PRAGMAincremental_vacuum 语句之前和之后运行 PRAGMA freelist_count 获取当前 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.

我也尝试将不同的参数传递给 incremental_vacuum 编译指示，例如PRAGMAincremental_vacuum(100) 但它仍然只能释放一页.

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.

提前致谢！

推荐答案

如果忽略 sqlite3_step() 的结果，就会发生这种情况.如果它返回SQLITE_ROW，则代码不能像返回SQLITE_DONE 那样工作.必须不断调用 sqlite3_step() 直到查询完成:pragma 与否，查询是一个查询，直到它完成，SQLITE_DONE 才完成:).恰好一次删除一页:

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;
}

这篇关于sqlite增量真空仅删除一个空闲页面的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！