If you had a power failure between when the transaction was queued and the sqlite transaction was comitted, no amount of fsync will save you.
If that is the threat you want to defend against this is not the right setting. Maybe it would reduce the window for it a little bit, but power failures are basically a non existent threat anyways, does a solution that mildly reduces but not eliminate the risk really matter when the risk is negligible?
> but power failures are basically a non existent threat anyways
Not in the contexts sqlite3 is often used. Remember, this is an embedded database, not a fat MySQL server sitting in a comfy datacenter with redundant power backups, RAID 6 and AC regulated to the millidegree. More like embedded systems with unreliable or no power backup. Like Curl, you can find it in unexpected places.
I think in that context, durability is even less expected.
A better example is probably
1. I general a keypair and commit it.
2. I send the public key to someone.
I *really* want to be sure that 1 is persisted. Because if they for example send me $1M worth of crypto it will really suck if I don't have the key anymore. There are definitely cases where it is critical to know that data has been persisted.
This is also assuming that what you are syncing to is more than one local disc, ideally you are running the fsync on multiple geographically distant discs. But there are also cryptography related applications where you must never reuse state otherwise very bad things happen. This can apply even for one local disc (like a laptop). In this case if you did something like 1. Encrypt some data. 2. Commit that this nonce, key, OTP, whatever has been used. 3. Send that datasome where. Then You want to be sure that either that data was comitted or the disc was permanently destroyed (or at least somehow wouldn't be used accidentally to be encrypt more data).
Of course it will because same programmers don’t ack their customers until their (distributed, replicated) db says ack.
sane*
I believe in the comment they're referring to the "crypto transaction" not the SQLite transaction.