WAL journal mode & sqlite3_backup_step()

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

WAL journal mode & sqlite3_backup_step()

Olivier Mascia
Dear all,

https://www.sqlite.org/c3ref/backup_finish.html#sqlite3backupstep makes it clear that connections (other than the one used for the backup feature) which writes in between calls to sqlite3_backup_step() will force the next sqlite3_backup_step() to start again the whole copy processing.

What about databases set for journal_mode=WAL?
Is backup_step() also restarting the whole procedure after any write commit happen?
Or only if some checkpointing occurs?

Said differently, is the whole scheme of sqlite3_backup_* API meant to build a copy of the database, including whatever would be in the WAL journal, or is the goal to get a copy of what is seen by the implicit read transaction of the connection doing the backup?

--
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software


_______________________________________________
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: WAL journal mode & sqlite3_backup_step()

Olivier Mascia
> Le 15 févr. 2017 à 16:04, Olivier Mascia <[hidden email]> a écrit :
>
> Dear all,
>
> https://www.sqlite.org/c3ref/backup_finish.html#sqlite3backupstep makes it clear that connections (other than the one used for the backup feature) which writes in between calls to sqlite3_backup_step() will force the next sqlite3_backup_step() to start again the whole copy processing.
>
> What about databases set for journal_mode=WAL?
> Is backup_step() also restarting the whole procedure after any write commit happen?
> Or only if some checkpointing occurs?
>
> Said differently, is the whole scheme of sqlite3_backup_* API meant to build a copy of the database, including whatever would be in the WAL journal, or is the goal to get a copy of what is seen by the implicit read transaction of the connection doing the backup?

Answering my own post, now that I have prepared and experimented with a test program: backup_step() indeed restarts its work for *any* write occurring during the backup, even though the test db is set for journal_mode=WAL and no checkpointing occurs (verified) at the end of the commit of any of those test writes.

Has anyone ever wanted the sqlite3_backup_* API to be able to proceed differently, when journal_mode=WAL?
Something along the lines of starting a transaction (deferred) on _init, _step() copying the database as seen by this transaction, and _finish() releasing that read-transaction?

It wouldn’t copy changes occurring after the backup/copy has started, of course, but would copy a complete stable snapshot of the database, without impacting (excepting the backup I/O) neither the readers or being cancelled/restarted by the occasional writer? Passive checkpointing as done by default by Sqlite3 wouldn’t even impact the process.

I for sure have a nice use case for this. :)

--
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software



_______________________________________________
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: WAL journal mode & sqlite3_backup_step()

Olivier Mascia
> Le 15 févr. 2017 à 17:41, Olivier Mascia <[hidden email]> a écrit :
>
>> Le 15 févr. 2017 à 16:04, Olivier Mascia <[hidden email]> a écrit :
>>
>> Dear all,
>>
>> https://www.sqlite.org/c3ref/backup_finish.html#sqlite3backupstep makes it clear that connections (other than the one used for the backup feature) which writes in between calls to sqlite3_backup_step() will force the next sqlite3_backup_step() to start again the whole copy processing.
>>
>> What about databases set for journal_mode=WAL?
>> Is backup_step() also restarting the whole procedure after any write commit happen?
>> Or only if some checkpointing occurs?
>>
>> Said differently, is the whole scheme of sqlite3_backup_* API meant to build a copy of the database, including whatever would be in the WAL journal, or is the goal to get a copy of what is seen by the implicit read transaction of the connection doing the backup?
>
> Answering my own post, now that I have prepared and experimented with a test program: backup_step() indeed restarts its work for *any* write occurring during the backup, even though the test db is set for journal_mode=WAL and no checkpointing occurs (verified) at the end of the commit of any of those test writes.
>
> Has anyone ever wanted the sqlite3_backup_* API to be able to proceed differently, when journal_mode=WAL?
> Something along the lines of starting a transaction (deferred) on _init, _step() copying the database as seen by this transaction, and _finish() releasing that read-transaction?
>
> It wouldn’t copy changes occurring after the backup/copy has started, of course, but would copy a complete stable snapshot of the database, without impacting (excepting the backup I/O) neither the readers or being cancelled/restarted by the occasional writer? Passive checkpointing as done by default by Sqlite3 wouldn’t even impact the process.
>
> I for sure have a nice use case for this. :)

A good approach, which seems to run fine according to my additional tests is to drive the backup by a single call to sqlite3_backup_step() with a negative integer parameter.  This process all the database pages in one iteration, as described by the documentation.  Proceeding this way, I solve my primary need (using a journal_mode WAL database): making a backup of the database while it is possibly queried and updated by other connections.  Without any possibility for the backup to run endless due to restarts occurring too often.

I’ll use that scheme for now and it essentially kills the above proposal. The only downside is that I loose the capability to monitor (or inform users if needed) of the backup progress.  Some alternative sqlite3_backup_step() version could get a callback function (and a count of pages) and would call the callback function every such pages processed and one last time when complete. There might even be provision to allow the callback function to request premature termination of the copy, if needed.

int backup_callback(sqlite3_backup*, void* user_data);
Return value SQLITE_OK or SQLITE_ABORT (or any other convention) would allow the callback to request interruption or continuation.
Remaining and total pages could be obtained through the existing sqlite3_backup_remaining and sqlite3_backup_pagecount functions or they could be passed as parameters to the callback function as the callback probably has few interest if not to report or log on the progress of the task.

--
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software




_______________________________________________
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: WAL journal mode & sqlite3_backup_step()

Clemens Ladisch
Olivier Mascia wrote:
> A good approach ... is to drive the backup by a single call to sqlite3_backup_step()

This is indeed what you should do with WAL.

> The only downside is that I loose the capability to monitor (or inform users if needed) of the backup progress.

That was never the primary purpose of having multiple steps; it was intended to allow
concurrent read and write accesses (before WAL existed).

And the progress would look silly when it actually restarts ...


Regards,
Clemens
_______________________________________________
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: WAL journal mode & sqlite3_backup_step()

Olivier Mascia
> Le 15 févr. 2017 à 18:44, Clemens Ladisch <[hidden email]> a écrit :
>
> Olivier Mascia wrote:
>> A good approach ... is to drive the backup by a single call to sqlite3_backup_step()
>
> This is indeed what you should do with WAL.
>
>> The only downside is that I loose the capability to monitor (or inform users if needed) of the backup progress.
>
> That was never the primary purpose of having multiple steps; it was intended to allow
> concurrent read and write accesses (before WAL existed).

Indeed, the reporting was probably not the primary motivation for the stepping, anyway reporting progress was obviously considered by authors: sqlite3_backup_remaining() is useless except between steps.
:)

Thanks,
--
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software


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