VACUUM INTO 'other.db' and fsync
(1) By willemv on 2022-10-18 07:56:57 [source]
The VACUUM INTO documentation contains the following snippet:
The VACUUM INTO command is transactional in the sense .... Also, SQLite does not invoke fsync() or FlushFileBuffers() on the generated database to ensure that it has reached non-volatile storage before completing.
That begs the question on how then we should call fsync or FlushFileBuffers
Are we supposed to call POSIX' open[at] to get a new file-descriptor and call fsync with that descriptor (or CreateFileW and FlushFileBuffers respectively in the Windows case)?
Is that guaranteed to work on all supported platforms?
I couldn't find definitive information on that in the man pages for fsync nor the MSDN documentation on FlushFileBuffers
(2) By anonymous on 2022-10-18 18:20:16 in reply to 1 [link] [source]
VACUUM INTO is more recent than plain VACUUM (it was added in version 3.27). Turning off synchronisation for the temporary database used by VACUUM is reasonable, but it seems nobody checked that those reasons are valid for the output of VACUUM INTO.
Proposed fix: for VACUUM INTO, turn synchronisation on just before committing.
Index: src/vacuum.c
==================================================================
--- src/vacuum.c
+++ src/vacuum.c
@@ -353,12 +353,15 @@
if( NEVER(rc!=SQLITE_OK) ) goto end_of_vacuum;
}
if( pOut==0 ){
rc = sqlite3BtreeCopyFile(pMain, pTemp);
+ if( rc!=SQLITE_OK ) goto end_of_vacuum;
+ } else {
+ sqlite3BtreeSetPagerFlags(
+ pTemp, PAGER_SYNCHRONOUS_NORMAL | PAGER_CACHESPILL);
}
- if( rc!=SQLITE_OK ) goto end_of_vacuum;
rc = sqlite3BtreeCommit(pTemp);
if( rc!=SQLITE_OK ) goto end_of_vacuum;
#ifndef SQLITE_OMIT_AUTOVACUUM
if( pOut==0 ){
sqlite3BtreeSetAutoVacuum(pMain, sqlite3BtreeGetAutoVacuum(pTemp));
(3) By willemv on 2022-10-19 17:06:51 in reply to 2 [link] [source]
That would work for us, indeed
(4) By willemv on 2022-10-24 08:00:49 in reply to 2 [link] [source]
Is there something expected from me for this enhancement to be implemented? Or will one of the SQLite developers pick up this thread ?
(5) By Dan Kennedy (dan) on 2022-10-24 15:53:57 in reply to 4 [link] [source]
(6) By willemv on 2022-10-25 14:01:30 in reply to 5 [link] [source]
Thank you very much for the fix!
Willem
(7) By Pepijn Van Eeckhoudt (pepijnve) on 2022-10-26 08:38:57 in reply to 5 [link] [source]
Should the VACUUM INTO documentation be updated as well?
The last sentence currently states:
Also, SQLite does not invoke fsync() or FlushFileBuffers() on the generated database to ensure that it has reached non-volatile storage before completing.
which I don't think is entirely correct after this change.