I've noticed with 3.18.0 that it's possible to make a database increase in size after running pragma integrity_check (which returns "ok") and then running vacuum.
Alternatively, vacuuming without running pragma integrity_check first keeps the database the same size as before. The page size on the database in question is 1024. The database starts with no pages in the freelist. I can't reproduce this with 3.17.0. SQLite version 3.18.0 2017-03-28 18:48:43 Enter ".help" for usage hints. --1) Before the vacuum. Database page count = 3077, and the freelist page count is 0: sqlite> .dbinfo database page size: 1024 write format: 1 read format: 1 reserved bytes: 0 file change counter: 52 database page count: 3077 freelist page count: 0 schema cookie: 19 schema format: 4 default cache size: 0 autovacuum top root: 0 incremental vacuum: 0 text encoding: 1 (utf8) user version: 0 application id: 0 software version: 3008010 number of tables: 2 number of indexes: 1 number of triggers: 0 number of views: 0 schema size: 309 --2) Running a vacuum (without pragma integrity_check) results in the same size of database: 3077 page count and 0 freelist page count: sqlite> vacuum; sqlite> .dbinfo database page size: 1024 write format: 1 read format: 1 reserved bytes: 0 file change counter: 53 database page count: 3077 freelist page count: 0 schema cookie: 20 schema format: 4 default cache size: 0 autovacuum top root: 0 incremental vacuum: 0 text encoding: 1 (utf8) user version: 0 application id: 0 software version: 3018000 number of tables: 2 number of indexes: 1 number of triggers: 0 number of views: 0 schema size: 309 --3) Now running pragma integrity_check which returns "ok", and then vacuuming. This increases the database page count to 3236: sqlite> pragma integrity_check; ok sqlite> vacuum; sqlite> .dbinfo database page size: 1024 write format: 1 read format: 1 reserved bytes: 0 file change counter: 54 database page count: 3236 freelist page count: 0 schema cookie: 21 schema format: 4 default cache size: 0 autovacuum top root: 0 incremental vacuum: 0 text encoding: 1 (utf8) user version: 0 application id: 0 software version: 3018000 number of tables: 2 number of indexes: 1 number of triggers: 0 number of views: 0 schema size: 309 sqlite> Here is the full schema: CREATE TABLE bids_list (report_id INTEGER, price_id INTEGER, premium_id INTEGER, period_inactive INTEGER DEFAULT (0) CHECK (period_inactive IN (0, 1))); CREATE TABLE bids_dates (report_id integer primary key, date text, current integer check (current in (0, 1))); CREATE INDEX idx_price_id ON bids_list (price_id); /* No STAT tables available */ Is the pragma fixing something in the index perhaps? _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
