Re: [sqlite] Vacuum results in larger database after running pragma integrity_check

2017-04-04 Thread Richard Hipp 
On 4/4/17, Richard Hipp  wrote:
> On 4/4/17, Ben Newberg  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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vacuum results in larger database after running pragma integrity_check

2017-04-04 Thread Richard Hipp 
On 4/4/17, Ben Newberg  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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vacuum results in larger database after running pragma integrity_check

2017-04-04 Thread Richard Hipp 
Can you email me the database that does this?

On 4/4/17, Ben Newberg  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

[sqlite] Vacuum results in larger database after running pragma integrity_check

2017-04-04 Thread 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users