[sqlite] read-only database in WAL mode and temporary files
When I open a database in read-only mode (?mode=ro), I observe that the WAL and SHM temporary files are created anyway. Is there any possibility to prevent the creation of these files? Aren't they useless? Even worse (using sqlite3.exe version 3.27.1): .open test.db pragma journal_mode=wal; create table a (b integer); insert into a (1); .quit This creates database with WAL and SHM files; those files are correctly removed when exiting. .open --readonly test.db select * from a; <--- creates WAL and SHM -- why? .quit Now, WAL and SHM files aren't deleted anymore. Proposal: Neither WAL nor SHM should be created at all when opening a WAL-mode database in read-only mode. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] read-only database in WAL mode and temporary files
Even a read only database needs to create the wal journal if it doesn't exist at the moment for a database that uses that mode: https://www.sqlite.org/wal.html#read_only_databases You might look into the immutable option mentioned there and see if it's appropriate for your needs. On Sun, Mar 24, 2019, 2:33 AM Thomas Kurz wrote: > When I open a database in read-only mode (?mode=ro), I observe that the > WAL and SHM temporary files are created anyway. Is there any possibility to > prevent the creation of these files? Aren't they useless? > > Even worse (using sqlite3.exe version 3.27.1): > > .open test.db > pragma journal_mode=wal; > create table a (b integer); > insert into a (1); > .quit > > This creates database with WAL and SHM files; those files are correctly > removed when exiting. > > .open --readonly test.db > select * from a; <--- creates WAL and SHM -- why? > .quit > > Now, WAL and SHM files aren't deleted anymore. > > Proposal: Neither WAL nor SHM should be created at all when opening a > WAL-mode database in read-only mode. > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] read-only database in WAL mode and temporary files
But it's of zero size, so there cannot be anything inside. As far as I have understood, it's just a journal containing the changes since the last checkpoint. If I don't change anything, why should there be the need of this journal? I would expect the journal to be created when the first write operation (INSERT, DELETE, whatsoever) occurs, but not for a read operation. And moreover, why is read-only-mode allowed to create a journal (this is contradictory to the read-only-idea), but not delete it upon closing the connection? Actually, this leads to more problems: If the database itself has the read-only-attribute set (by filesystem or ACL), SQLite would start journaling changes which can be never be checkpointed. In commonsense, a write operation should immediately terminate and return an error in this case. Finally, I have noticed the immutable mode, but I have a bad feeling about it. If I understand correctly, a second connection could still open the same database in write-mode. I would instead expect that a read-only-connection locked the database (using file locking mechanism) and a parallel write-connection to be blocked until the locked is released. (No temporary files should be required until this moment.) - Original Message - From: Shawn Wagner To: SQLite mailing list Sent: Sunday, March 24, 2019, 11:29:58 Subject: [sqlite] read-only database in WAL mode and temporary files Even a read only database needs to create the wal journal if it doesn't exist at the moment for a database that uses that mode: https://www.sqlite.org/wal.html#read_only_databases You might look into the immutable option mentioned there and see if it's appropriate for your needs. On Sun, Mar 24, 2019, 2:33 AM Thomas Kurz wrote: > When I open a database in read-only mode (?mode=ro), I observe that the > WAL and SHM temporary files are created anyway. Is there any possibility to > prevent the creation of these files? Aren't they useless? > Even worse (using sqlite3.exe version 3.27.1): > .open test.db > pragma journal_mode=wal; > create table a (b integer); > insert into a (1); > .quit > This creates database with WAL and SHM files; those files are correctly > removed when exiting. > .open --readonly test.db > select * from a; <--- creates WAL and SHM -- why? > .quit > Now, WAL and SHM files aren't deleted anymore. > Proposal: Neither WAL nor SHM should be created at all when opening a > WAL-mode database in read-only mode. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] read-only database in WAL mode and temporary files
From my understanding, the SHM file is needed to communicate to writers that they are in fact performing a read operation (i.e., locks). Without that communication, a writer might insert some data and then perform a checkpoint, without knowing that there is a database reader, and thus overwrite pages in the main database file out from under the reader. Why is the WAL file needed? I’m not sure. It might be just to make the implementation simpler. Since the reader must create the SHM file regardless, it may as well create the WAL file too. > On Mar 24, 2019, at 8:26 AM, Thomas Kurz wrote: > > But it's of zero size, so there cannot be anything inside. As far as I have > understood, it's just a journal containing the changes since the last > checkpoint. If I don't change anything, why should there be the need of this > journal? I would expect the journal to be created when the first write > operation (INSERT, DELETE, whatsoever) occurs, but not for a read operation. > > And moreover, why is read-only-mode allowed to create a journal (this is > contradictory to the read-only-idea), but not delete it upon closing the > connection? > > Actually, this leads to more problems: If the database itself has the > read-only-attribute set (by filesystem or ACL), SQLite would start journaling > changes which can be never be checkpointed. In commonsense, a write operation > should immediately terminate and return an error in this case. > > Finally, I have noticed the immutable mode, but I have a bad feeling about > it. If I understand correctly, a second connection could still open the same > database in write-mode. I would instead expect that a read-only-connection > locked the database (using file locking mechanism) and a parallel > write-connection to be blocked until the locked is released. (No temporary > files should be required until this moment.) > > > - Original Message - > From: Shawn Wagner > To: SQLite mailing list > Sent: Sunday, March 24, 2019, 11:29:58 > Subject: [sqlite] read-only database in WAL mode and temporary files > > Even a read only database needs to create the wal journal if it doesn't > exist at the moment for a database that uses that mode: > https://www.sqlite.org/wal.html#read_only_databases > > You might look into the immutable option mentioned there and see if it's > appropriate for your needs. > > > On Sun, Mar 24, 2019, 2:33 AM Thomas Kurz wrote: > >> When I open a database in read-only mode (?mode=ro), I observe that the >> WAL and SHM temporary files are created anyway. Is there any possibility to >> prevent the creation of these files? Aren't they useless? > >> Even worse (using sqlite3.exe version 3.27.1): > >> .open test.db >> pragma journal_mode=wal; >> create table a (b integer); >> insert into a (1); >> .quit > >> This creates database with WAL and SHM files; those files are correctly >> removed when exiting. > >> .open --readonly test.db >> select * from a; <--- creates WAL and SHM -- why? >> .quit > >> Now, WAL and SHM files aren't deleted anymore. > >> Proposal: Neither WAL nor SHM should be created at all when opening a >> WAL-mode database in read-only mode. > >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users