Vacuum results in larger database after running pragma integrity_check

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Vacuum results in larger database after running pragma integrity_check

Ben Newberg
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Vacuum results in larger database after running pragma integrity_check

Richard Hipp-3
Can you email me the database that does this?

On 4/4/17, Ben Newberg <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Vacuum results in larger database after running pragma integrity_check

Richard Hipp-3
In reply to this post by Ben Newberg
On 4/4/17, Ben Newberg <[hidden email]> 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.

I can now repro the behavior and have bisected to this check-in:
https://www.sqlite.org/src/timeline?c=aa02bd

Still do not understand how a (read-only) integrity_check might affect
a subsequent VACUUM operation, however.....
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Vacuum results in larger database after running pragma integrity_check

Richard Hipp-3
On 4/4/17, Richard Hipp <[hidden email]> wrote:

> On 4/4/17, Ben Newberg <[hidden email]> 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.
>
> I can now repro the behavior and have bisected to this check-in:
> https://www.sqlite.org/src/timeline?c=aa02bd
>
> Still do not understand how a (read-only) integrity_check might affect
> a subsequent VACUUM operation, however.....

Dan has now checked in a fix for the problem on trunk.

   https://www.sqlite.org/src/timeline?c=e5bb7d

Because this problem never results in an incorrect answer (as far as
we can determine) only a sub-optimal vacuum, we are not calling it a
bug.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...