Strange behavior with wal_checkpoint PRAGMA

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Strange behavior with wal_checkpoint PRAGMA

Joshua Watt
Hello,

I observed the following behavior when using the wal_checkpoint PRAGMA
in TRUNCATE mode:
 
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open test.db
sqlite> PRAGMA journal_mode = WAL;
wal
sqlite> create table test ( key INTEGER PRIMARY KEY, val INTEGER );
sqlite> insert into test values ( 5, 6 );
sqlite> insert into test values ( 6, 6 );
sqlite> PRAGMA wal_checkpoint(TRUNCATE);
0|0|0
 
According to the documentation, the second and third values should
correspond to the number of pages in the write-ahead log and the number
of pages that have been moved back to the database file.  This works as
expected in the other modes, but in TRUNCATE mode the second and third
return values are always zero.
 
I believe this is because walCheckpoint() triggers a call to
walRestartHdr() if the mode is SQLITE_CHEKPOINT_TRUNCATE.  Once
walCheckpoint() returns, sqlite3WalCheckpoint() attempts to extract the
values from the already restarted wal header.

Has anyone else expirenced this issue?

Thanks,
Joshua Watt
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Strange behavior with wal_checkpoint PRAGMA

Joshua Watt
On Wed, 2018-02-14 at 08:13 -0600, Joshua Watt wrote:

> Hello,
>
> I observed the following behavior when using the wal_checkpoint
> PRAGMA
> in TRUNCATE mode:
>  
> SQLite version 3.22.0 2018-01-22 18:45:57
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .open test.db
> sqlite> PRAGMA journal_mode = WAL;
> wal
> sqlite> create table test ( key INTEGER PRIMARY KEY, val INTEGER );
> sqlite> insert into test values ( 5, 6 );
> sqlite> insert into test values ( 6, 6 );
> sqlite> PRAGMA wal_checkpoint(TRUNCATE);
> 0|0|0
>  
> According to the documentation, the second and third values should
> correspond to the number of pages in the write-ahead log and the
> number
> of pages that have been moved back to the database file.  This works
> as
> expected in the other modes, but in TRUNCATE mode the second and
> third
> return values are always zero.
>  
> I believe this is because walCheckpoint() triggers a call to
> walRestartHdr() if the mode is SQLITE_CHEKPOINT_TRUNCATE.  Once
> walCheckpoint() returns, sqlite3WalCheckpoint() attempts to extract
> the
> values from the already restarted wal header.
>
> Has anyone else expirenced this issue?

Ping?

>
> Thanks,
> Joshua Watt
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Strange behavior with wal_checkpoint PRAGMA

Dan Kennedy-4
In reply to this post by Joshua Watt
On 02/14/2018 09:13 PM, Joshua Watt wrote:

> Hello,
>
> I observed the following behavior when using the wal_checkpoint PRAGMA
> in TRUNCATE mode:
>  
> SQLite version 3.22.0 2018-01-22 18:45:57
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .open test.db
> sqlite> PRAGMA journal_mode = WAL;
> wal
> sqlite> create table test ( key INTEGER PRIMARY KEY, val INTEGER );
> sqlite> insert into test values ( 5, 6 );
> sqlite> insert into test values ( 6, 6 );
> sqlite> PRAGMA wal_checkpoint(TRUNCATE);
> 0|0|0
>  
> According to the documentation, the second and third values should
> correspond to the number of pages in the write-ahead log and the number
> of pages that have been moved back to the database file.  This works as
> expected in the other modes, but in TRUNCATE mode the second and third
> return values are always zero.

The reasoning is that the wal file has been truncated. It therefore
contains 0 modified pages, 0 of which have already been checkpointed to
the database file.

Dan.






>  
> I believe this is because walCheckpoint() triggers a call to
> walRestartHdr() if the mode is SQLITE_CHEKPOINT_TRUNCATE.  Once
> walCheckpoint() returns, sqlite3WalCheckpoint() attempts to extract the
> values from the already restarted wal header.
>
> Has anyone else expirenced this issue?
>
> Thanks,
> Joshua Watt
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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