4 ms·

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

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.

russellthehippo 6mo ago
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.
- infogulch 6mo ago
  Really? That's the opposite of what I understand the docs say. Care to share your code? This may become a bug report.
  - russellthehippo 6mo ago
    Will do
  - russellthehippo 6mo ago
    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.
    - russellthehippo 6mo ago
      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 https://github.com/russellromney/honker/issues/5

russellthehippo 6mo ago

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.

infogulch 6mo ago
Really? That's the opposite of what I understand the docs say. Care to share your code? This may become a bug report.
- russellthehippo 6mo ago
  Will do
- russellthehippo 6mo ago
  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.
  - russellthehippo 6mo ago
    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 https://github.com/russellromney/honker/issues/5