4 ms·

Dumb Q: what would happen if you used this to store a SQLite database? Would it just... work? My guess is this would only enable a read-replica and not backups

Dumb Q: what would happen if you used this to store a SQLite database? Would it just... work? My guess is this would only enable a read-replica and not backups as Litestream currently does?

laurencerowe 6mo ago
SQLite’s locking is not NFS safe so this would not work.
- miguel_martin 6mo ago
  thanks
- jlokier 6mo ago
  Technically, SQLite's locking is NFS safe, provided NFS's implementation of fcntl() locking is working correctly. I don't know if S3 Files implements fcntl() locking or does it correctly. But if it does, I believe SQLite should work on it correctly as well. There have been many buggy NFS locking or caching implementations historically, which is why reason SQLite recommends against using it on NFS concurrently on multiple machines: https://sqlite.org/faq.html#:~:text=But%20use,time%2E https://sqlite.org/faq.html#:~:text=But%20use,time%2E This SO reply suggests NFSv4 is better at this: https://unix.stackexchange.com/a/432519 https://unix.stackexchange.com/a/432519. But caveat it with this older reply: https://unix.stackexchange.com/a/1887 https://unix.stackexchange.com/a/1887 To the best of my knowledge (I worked a little on this long ago), on Linux even NFSv2 has done correct fcntl() locking for decades, if all the correct services are running and the options are set appropriately and it's Linux on both the client and server. But if something is not configured as it should be, then locking or caching may not work correctly.
  - laurencerowe 6mo ago
    Thanks for the clarification. It is completely impossible for WAL mode since that uses shared memory. I must have conflated that with non-WAL mode in my mind. From https://sqlite.org/wal.html https://sqlite.org/wal.html > All processes using a database must be on the same host computer; WAL does not work over a network filesystem. This is because WAL requires all processes to share a small amount of memory and processes on separate host machines obviously cannot share memory with each other.
Eikon 6mo ago
SQLite works great with ZeroFS: https://github.com/Barre/ZeroFS https://github.com/Barre/ZeroFS
- otterley 6mo ago
  Via which front end? It can’t be the NFS one.

Dumb Q: what would happen if you used this to store a SQLite database? Would it just... work? My guess is this would only enable a read-replica and not backups as Litestream currently does?

laurencerowe 6mo ago

SQLite’s locking is not NFS safe so this would not work.

miguel_martin 6mo ago
thanks
jlokier 6mo ago
Technically, SQLite's locking is NFS safe, provided NFS's implementation of fcntl() locking is working correctly. I don't know if S3 Files implements fcntl() locking or does it correctly. But if it does, I believe SQLite should work on it correctly as well. There have been many buggy NFS locking or caching implementations historically, which is why reason SQLite recommends against using it on NFS concurrently on multiple machines: https://sqlite.org/faq.html#:~:text=But%20use,time%2E https://sqlite.org/faq.html#:~:text=But%20use,time%2E This SO reply suggests NFSv4 is better at this: https://unix.stackexchange.com/a/432519 https://unix.stackexchange.com/a/432519. But caveat it with this older reply: https://unix.stackexchange.com/a/1887 https://unix.stackexchange.com/a/1887 To the best of my knowledge (I worked a little on this long ago), on Linux even NFSv2 has done correct fcntl() locking for decades, if all the correct services are running and the options are set appropriately and it's Linux on both the client and server. But if something is not configured as it should be, then locking or caching may not work correctly.
- laurencerowe 6mo ago
  Thanks for the clarification. It is completely impossible for WAL mode since that uses shared memory. I must have conflated that with non-WAL mode in my mind. From https://sqlite.org/wal.html https://sqlite.org/wal.html > All processes using a database must be on the same host computer; WAL does not work over a network filesystem. This is because WAL requires all processes to share a small amount of memory and processes on separate host machines obviously cannot share memory with each other.

Eikon 6mo ago

SQLite works great with ZeroFS: https://github.com/Barre/ZeroFS https://github.com/Barre/ZeroFS

otterley 6mo ago
Via which front end? It can’t be the NFS one.