Can you email me the database that does this? On 4/4/17, Ben Newberg <ben.newb...@gmail.com> wrote: > 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 >
-- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
