On 9/26/19 12:13 PM, Gwendal Roué wrote:
> Hello,
>
> The documentation for sqlite3_snapshot_open() [1] says:
>
>> A call to sqlite3_snapshot_open() will fail to open if the specified
> snapshot has been overwritten by a checkpoint.
>
> And indeed I am able to create a snapshot with sqlite3_snapshot_get() [2],
> then run checkpoints with sqlite3_wal_checkpoint_v2() [3], and finally have
> sqlite3_snapshot_open() fail.
>
> I have a concern, because this turns automatic checkpointing into a mortal
> danger for all snapshots. As soon as a checkpoint is performed, snapshots
> are invalidated, and whoever is relying on them has lost the database state
> he's interested into.
>
> My question is: is it possible to prevent checkpoints from completing
> successfully when a snapshot is alive?
>
> I know this is possible with a "hand-made checkpoint", made of a distinct
> connection which has started a deferred transaction and has performed an
> initial read. Such hand-made checkpoint has SQLITE_CHECKPOINT_RESTART and
> SQLITE_CHECKPOINT_TRUNCATE fail, and are still able to access their
> precious database state after a SQLITE_CHECKPOINT_PASSIVE or a
> SQLITE_CHECKPOINT_FULL.
>
> The behavior of those "hand-made checkpoint" matches well the needs of
> users who want to use WAL without thinking too much about it: they do not
> disable automatic checkpointing, and are guaranteed with a stable access to
> a given database state as long as they need it.
>
> I was wondering if such a behavior is possible with snapshots returned from
> sqlite3_snapshot_get().
>
> If it is not, then I humbly suggest that this feature would be added, and
> am willing to listen to the opinion of SQLite experts on this subject.
>
> Regards,
> Gwendal Roué
>
> [1] https://www.sqlite.org/c3ref/snapshot_open.html
> [2] https://www.sqlite.org/c3ref/snapshot_get.html
> [3] https://www.sqlite.org/c3ref/wal_checkpoint_v2.html

I think you have a fundamental issue here. Snapshots are tied to a
Transaction, and as long as that transaction doesn't invalidate that
snapshot, nothing else can. (This is what you hand-mand checkpoint does
right). The issue is that if you are reusing the connection, you are
sharing the Transaction and thus not protecting your snapshot. Once
someone else using that connection breaks the transaction, then the
snapshot is no longer protected.

The issue trying to increase the protection on a snapshot is that
currently sqlite_snapshot_free() isn't defined as protecting the
snapshot, but is merely a memory management tool, so many applications
likely don't free all their snapshots before they allow for the
possibility of them being invalidated by ending the Transaction, thus
this change would break many existing programs. I suppose it could be
implemented with a pragma or the like that doesn't allow that
transaction to be committed or invalidate the snapshot until the
snapshot is freed.

What that would ultimately do is cause your program to get an error from
SQLite3 farther down the road when you did some action on the connection
that would potentially invalidate your precious snapshot, and you now
have one more source of 'unexplained' errors returns.

-- 
Richard Damon

_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to