In a recent controversy we learn that macOS has a "no really, I mean it" flag on fsync(2) that might be relevant to SQLite. Since I don't see that flag used anywhere on trunk, I thought I'd raise the issue for consideration.

Thanks for posting this.

I think there's a typo in the linked post. The flag name should be "F_FULLFSYNC" instead of "F_FULLSYNC". We use it here:

http://www.sqlite.org/src/artifact/f5ad51cfd0241?ln=3641

if "PRAGMA fullfsync" or "PRAGMA checkpoint_fullfsync" is set.

Dan.

And it is zero by default. Huge gotcha. I think SQLite databases might become currupted on power loss/reset on macOS if this is not set to one.

I checked this, though it was at least five years ago, so it involves old hardware and old versions of SQLite and macOS.

In our experiments involving unexpected power-offs and unexpected Flash Drive ejection, not setting F_FULLFSYNC sometimes led to databases which were less-up-to-date. They sometimes led to the most recent transaction being lost.

I did not find any examples of corrupt databases. Each database could be opened by sqlite3_open(), passed the integrity_check(), and had in it the data expected, perhaps lacking the most recent transaction.

This was a specific test performed for a specific setup. I'm not saying that the results apply to all Macs running all versions of macOS, and using all file systems. And if I recall correctly, we were not using WAL mode.

It's worth noting that with all the drives we tested F_FULLFSYNC slowed things down a lot. In fact, we decided during testing that if you've found a setup where F_FULLFSYNC does not slow things down, you've found a storage system that doesn't support F_FULLFSYNC and is ignoring it.

Whoa. I never read that before. Apple explicitly says that if you have a lot of small file-like things to maintain …

https://developer.apple.com/documentation/xcode/reducing-disk-writes#Minimize-Explicit-Storage-Synchronization

When possible, use Core Data or SQLite for storing frequently edited documents. If that isn’t possible, use different serialized files for data that changes frequently and data that’s mostly static. This can reduce the amount of disk writes and improve latency.

So Apple endorses the idea that SQLite is sometimes better than a file system for keeping small document-like things. It goes on to talk about things which are SQLite-only like WAL mode and the VACUUM command.