Yeah the C API seems like a perfect fit for this use-case:

> [SQLITE_FCNTL_DATA_VERSION] is the only mechanism to detect changes that happen either internally or externally and that are associated with a particular attached database.

Another user itt says the stat(2) approach takes less than 1 μs per call on their hardware.

I wonder how these approaches compare across compatibility & performance metrics.

I just tested this out. PRAGMA data_version uses a shared counter that any connection can use while the C API appears to use a per-connection counter that does not see other connections' commits.

Really? That's the opposite of what I understand the docs say.

Care to share your code? This may become a bug report.

Reporting back. This appears to be a bug in my original test the code of which sadly I did not commit anywhere. I went back to regenerate these tests and proved the opposite - the C API is better than PRAGMA and works across connections. I am going to make that update as I've proved across dozens of versions of SQLite that this is not in fact the case.

Reporting back again. It seems I was actually right the first time - the C API's SQLITE_FCNTL_DATA_VERSION doesn't work cross connection. It is cached on each read - but if there aren't any reads (i.e. just polling SQLITE_FCNTL_DATA_VERSION) then it doesn't work.

PRAGMA data_version is pretty fast (1500ns with prepared statement) and doesn't have that issue.

Checking the wal-index is sub-nanosecond when mmapped but has slightly different behavior on Windows.

Here's the link to the thread on this, my scripts are all there.

https://github.com/russellromney/honker/issues/5

Will do

It's not.

You are correct. I apologize. I seemed to have read the next pragma’s depreciation notice!

Aside from this - SQLite has tons of cool features, like the session extension.

Yep, definitely still in use. Do yall above have an opinion if the pragma is better than the syscall? What are the trade offs there? Another comment thread mentioned this as well and pointed to io uring. I was thinking that dism spam is worse than syscall spam.

Depends on what to mean by better.

I may be wrong, but I think you wrote somewhere that you're looking at the WAL size increasing to know if something was committed. Well, the WAL can be truncated, what then? Or even, however unlikely, it could be truncated, then a transaction comes and appends just enough to it to make it the same size.

If SQLite has an API it guarantees can notify you of changes, that seems better, in the sense that you're passing responsibility along to the experts. It should also work with rollback mode, another advantage. And I don't think wakes you up if a large transaction rolls back (a transaction can hit the WAL and never commit).

That said, I'm not sure what's lighter on average. For a WAL mode database, I will say that something that has knowledge of the WAL index could potentially be cheaper? That file is mmapped. The syscalls involved are file locks, if any.

Interesting, thank you for the response and explanation. Honker workers/listerners are holding an open connection anyway. I do trust SQLite guarantees more than cross-platform sys behavior. I will explore the C API angle.