My application requires a way to build a "pending state" in the database while allowing users to query data from both the "official state" and the "pending state". I am achieving this using sessions and snapshots.
When pending data first comes into the application, I take a snapshot of the current "official" state of the database and keep a read transaction open on one read only connection for the life of the snapshot to prevent it being checkpointed out of the WAL, and then start a session that tracks all changes on my one write connection so that the pending data may later be "rolled back" by applying the inverse changeset. Then I proceed to commit incoming pending data to the write connection. Any user querying the application for the pending state, is reading from a read only connection which will reflect the current latest state of the database, which includes the pending data, if any exists. Any user querying for the official state, is reading from a read only connection that has a read transaction started from the official state snapshot captured above, if any pending data exists. When the next official state update occurs, the snapshot is freed, its associated read transaction that I set aside is closed (and the read connection is returned to my read conn pool), and the inverse changeset is used to roll back the pending state on a write connection before applying the official state update. Any reader currently reading from the snapshot should be able to proceed until they end their read transaction. I assumed that keeping a read transaction open on the snapshot would be enough to prevent the WAL from being checkpointed past the snapshot, making it unavailable for use by other read connections. However, despite keeping one read transaction open, I am still getting SQLITE_ERROR_SNAPSHOT from sqlite3_snapshot_open when the pending data grow large enough to force an auto checkpoint. I am fairly confident that the snapshot getting checkpointed out of the WAL is the cause of this error since it went away when I disabled auto checkpoints. Is my assumption incorrect? If so, is there any way to ensure a snapshot remains in the WAL without going so far as to manually manage checkpoints myself? Finally if I must manually manage checkpoints, is it possible for a PASSIVE checkpoint to leave the WAL in a state that will prevent taking a snapshot in the first place? Specifically, can a PASSIVE checkpoint cause the following requirement for sqlite3_snapshot_get to no longer hold: "One or more transactions must have been written to the current wal file since it was created on disk (by any connection). This means that a snapshot cannot be taken on a wal mode database with no wal file immediately after it is first opened. At least one transaction must be written to it first." Thank you Adam Levy _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
