sqlite3_backup WAL snapshot lacks exposed frame number for application-level verification
0 reputation · 28 Dec 2021, 05:24 UTC
0 reputation · 28 Dec 2021, 05:24 UTC
Correlate a backup taken via sqlite3_backup with an application-defined logical point in time (e.g., a committed transaction ID or version marker) so that restored data can be verified against that marker.
The sqlite3_backup API initiates a read transaction on the source database and copies pages through the pager, producing a consistent snapshot that includes all transactions committed up to that read transaction's start. In WAL mode this means the backup captures the main database file and any WAL frames needed for consistency. However, the API does not return or expose the WAL frame number, salt, or transaction ID that identifies the exact snapshot boundary.
Without an exposed frame reference, an application cannot later verify that a restored backup corresponds to a specific committed transaction (for example, the one that wrote a known version row). PRAGMA integrity_check and PRAGMA quick_check only validate internal structural consistency, not completeness relative to an external logical marker.
The documentation notes that the backup API is safe for concurrent use and that a checkpoint before backup is a performance optimization to reduce writer blocking, not a correctness requirement. Alternative approaches such as VACUUM INTO (SQLite 3.27.0+) or file-copy with exclusive locking also do not surface a transaction identifier for cross-referencing.
sqlite3_backup_init?VACUUM INTO or a locked file copy provide any advantage in exposing a snapshot identifier for later verification?A thoughtful contribution can make all the difference. Be the first to share one.
Use comments to ask for clarification. Post a solution as an answer.
No question comments on this page.