Documentation Source Text

Check-in [9b3d9280b7]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Updates to the temporary disk file document.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 9b3d9280b7271e3c03cc7b161e411e903c19ea84
User & Date: drh 2013-02-12 13:45:20.869
Context
2013-02-12
13:49
Nokia back on the Sponsors section of the homepage. (check-in: 8197ce8dbb user: drh tags: trunk)
13:45
Updates to the temporary disk file document. (check-in: 9b3d9280b7 user: drh tags: trunk)
2013-01-25
15:06
Correction to the description of the WAL file checksum computation. (check-in: 0a8b8c3b49 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/tempfiles.in.
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49


50
51
52
53
54
55
56
updated.  But there is no guarantee that future versions of SQLite
will use temporary files in the same way.  New kinds of temporary
files might be employed  and some of
the current temporary file uses might be discontinued
in future releases of SQLite.
</p>

<tcl>hd_fragment seventypes</tcl>
<h2>2.0 Seven Kinds Of Temporary Files</h2>

<p>
SQLite currently uses seven distinct types of temporary files:
</p>

<ol>
<li>Rollback journals</li>
<li>Master journals</li>


<li>Statement journals</li>
<li>TEMP databases</li>
<li>Materializations of views and subqueries</li>
<li>Transient indices</li>
<li>Transient databases used by VACUUM</li>
</ol>








|
|


|





>
>







33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
updated.  But there is no guarantee that future versions of SQLite
will use temporary files in the same way.  New kinds of temporary
files might be employed  and some of
the current temporary file uses might be discontinued
in future releases of SQLite.
</p>

<tcl>hd_fragment types</tcl>
<h2>2.0 Nine Kinds Of Temporary Files</h2>

<p>
SQLite currently uses nine distinct types of temporary files:
</p>

<ol>
<li>Rollback journals</li>
<li>Master journals</li>
<li>Write-ahead Log (WAL) files</li>
<li>Shared-memory files</li>
<li>Statement journals</li>
<li>TEMP databases</li>
<li>Materializations of views and subqueries</li>
<li>Transient indices</li>
<li>Transient databases used by VACUUM</li>
</ol>

126
127
128
129
130
131
132
133
134
135
136
137
138
139





140
141





















































142
143
144
145
146
147
148
149
150
as described above.  The PERSIST journal mode foregoes the deletion of
the journal file and instead overwrites the rollback journal header
with zeros, which prevents other processes from rolling back the
journal and thus has the same effect as deleting the journal file, though
without the expense of actually removing the file from disk.  In other
words, journal mode PERSIST exhibits the same behavior as is seen
in EXCLUSIVE locking mode. The
OFF journal mode causes SQLite to forego creating a rollback journal
in the first place.  The OFF journal mode disables the atomic
commit and rollback capabilities of SQLite.  The ROLLBACK command
is not available when OFF journal mode is set.  And if a crash or power
loss occurs in the middle of a transaction that uses the OFF journal
mode, no recovery is possible and the database file will likely
go corrupt.





</p>






















































<tcl>hd_fragment masterjrnl</tcl>
<h3>2.2 Master Journal Files</h3>

<p>
The master journal file is used as part of the atomic commit
process when a single transaction makes changes to multiple
databases that have been added to a single [database connection]
using the [ATTACH] statement.  The master journal file is always
located in the same directory as the main database file







|
|





>
>
>
>
>


>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

|







128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
as described above.  The PERSIST journal mode foregoes the deletion of
the journal file and instead overwrites the rollback journal header
with zeros, which prevents other processes from rolling back the
journal and thus has the same effect as deleting the journal file, though
without the expense of actually removing the file from disk.  In other
words, journal mode PERSIST exhibits the same behavior as is seen
in EXCLUSIVE locking mode. The
OFF journal mode causes SQLite to the rollback journal.
The OFF journal mode disables the atomic
commit and rollback capabilities of SQLite.  The ROLLBACK command
is not available when OFF journal mode is set.  And if a crash or power
loss occurs in the middle of a transaction that uses the OFF journal
mode, no recovery is possible and the database file will likely
go corrupt.
The MEMORY journal mode causes the rollback journal to be stored in
memory rather than on disk.  The ROLLBACK command still works when
the journal mode is MEMORY, but because no file exists on disks for
recovery, a crash or power loss in the middle of a transaction that uses
the MEMORY journal mode will likely result in a corrupt database.
</p>

<tcl>hd_fragment walfile</tcl>
<h3>2.2 Write-Ahead Log (WAL) Files</h3>

<p>
A write-ahead log or WAL file is used in place of a rollback journal
when SQLite is operating in [WAL mode].  As with the rollback journal,
the purpose of the WAL file is to implement atomic commit and rollback.
The WAL file is always located in the same directory
as the database file and has the same name as the database
file except with the 4 characters "<b>-wal</b>" appended.
The WAL file is created when the first connection to the
database is opened and is normally removed when the last
connection to the database closes.  However, if the last connection
does not shutdown cleanly, the WAL file will remain in the filesystem
and will be automatically cleaned up the next time the database is
opened.
</p>

<tcl>hd_fragment shmfile</tcl>
<h3>2.3 Shared-Memory Files</h3>

<p>
When operating in [WAL mode], all SQLite database connections associated
with the same database file need to share some memory that is used as an
index for the WAL file.  In most implementations, this shared memory is
implemented by calling mmap() on a file created for this sole purpose:
the shared-memory file.  The shared-memory file, if it exists, is located
in the same directory as the database file and has the same name as the
database file except with the 4 characters "<b>-shm</b>" appended.
Shared memory files only exist while running in WAL mode.
</p>

<p>
The shared-memory file contains no persistent content.  The only purpose
of the shared-memory file is to provide a block of shared memory for use
by multiple processes all accessing the same database in WAL mode.
If the [VFS] is able to providean  alternative method for accessing shared
memory, then that alternative method might be used rather than the
shared-memory file.  For example, if [PRAGMA locking_mode] is set to
EXCLUSIVE (meaning that only one process is able to access the database
file) then the shared memory will be allocated from heap rather than out
of the shared-memory file, and the shared-memory file will never be
created.
</p>

<p>
The shared-memory file has the same lifetime as its associated WAL file.
The shared-memory file is created when the WAL file is created and is
deleted when the WAL file is deleted.  During WAL file recovery, the
shared memory file is recreated from scratch based on the contents of
the WAL file being recovered.
</p>

<tcl>hd_fragment masterjrnl</tcl>
<h3>2.4 Master Journal Files</h3>

<p>
The master journal file is used as part of the atomic commit
process when a single transaction makes changes to multiple
databases that have been added to a single [database connection]
using the [ATTACH] statement.  The master journal file is always
located in the same directory as the main database file
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
commit were interrupted in the middle by a crash or power loss, then
the changes to one of the databases might complete while the changes
to another database might roll back.  The master journal causes all
changes in all databases to either rollback or commit together.
</p>

<tcl>hd_fragment stmtjrnl</tcl>
<h3>2.3 Statement Journal Files</h3>

<p>
A statement journal file is used to rollback partial results of
a single statement within a larger transaction.  For example, suppose
an UPDATE statement will attempt to modify 100 rows in the database.
But after modifying the first 50 rows, the UPDATE hits
a constraint violation which should block the entire statement.







|







232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
commit were interrupted in the middle by a crash or power loss, then
the changes to one of the databases might complete while the changes
to another database might roll back.  The master journal causes all
changes in all databases to either rollback or commit together.
</p>

<tcl>hd_fragment stmtjrnl</tcl>
<h3>2.5 Statement Journal Files</h3>

<p>
A statement journal file is used to rollback partial results of
a single statement within a larger transaction.  For example, suppose
an UPDATE statement will attempt to modify 100 rows in the database.
But after modifying the first 50 rows, the UPDATE hits
a constraint violation which should block the entire statement.
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
deleted at the conclusion of the transaction.  The size of the
statement journal is proportional to the size of the change implemented
by the UPDATE or INSERT statement that caused the statement journal
to be created.
</p>

<tcl>hd_fragment tempdb</tcl>
<h3>2.4 TEMP Databases</h3>

<p>Tables created using the "CREATE TEMP TABLE" syntax are only
visible to the [database connection] in which the "CREATE TEMP TABLE"
statement is originally evaluated.  These TEMP tables, together
with any associated indices, triggers, and views, are collectively
stored in a separate temporary database file that is created as
soon as the first "CREATE TEMP TABLE" statement is seen.







|







279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
deleted at the conclusion of the transaction.  The size of the
statement journal is proportional to the size of the change implemented
by the UPDATE or INSERT statement that caused the statement journal
to be created.
</p>

<tcl>hd_fragment tempdb</tcl>
<h3>2.6 TEMP Databases</h3>

<p>Tables created using the "CREATE TEMP TABLE" syntax are only
visible to the [database connection] in which the "CREATE TEMP TABLE"
statement is originally evaluated.  These TEMP tables, together
with any associated indices, triggers, and views, are collectively
stored in a separate temporary database file that is created as
soon as the first "CREATE TEMP TABLE" statement is seen.
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
<p>
The temporary files associated with the TEMP database and its
rollback journal are only created if the application makes use
of the "CREATE TEMP TABLE" statement.
</p>

<tcl>hd_fragment views</tcl>
<h3>2.5 Materializations Of Views And Subqueries</h3>

<p>Queries that contain subqueries must sometime evaluate
the subqueries separately and store the results in a temporary
table, then use the content of the temporary table to evaluate
the outer query.
We call this "materializing" the subquery.
The query optimizer in SQLite attempts to avoid materializing,







|







314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
<p>
The temporary files associated with the TEMP database and its
rollback journal are only created if the application makes use
of the "CREATE TEMP TABLE" statement.
</p>

<tcl>hd_fragment views</tcl>
<h3>2.7 Materializations Of Views And Subqueries</h3>

<p>Queries that contain subqueries must sometime evaluate
the subqueries separately and store the results in a temporary
table, then use the content of the temporary table to evaluate
the outer query.
We call this "materializing" the subquery.
The query optimizer in SQLite attempts to avoid materializing,
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
or not the subquery or outer query contain aggregate functions,
ORDER BY or GROUP BY clauses, LIMIT clauses, and so forth.
The rules for when a query and cannot be flattened are
very complex and are beyond the scope of this document.
</p>

<tcl>hd_fragment transidx</tcl>
<h3>2.6 Transient Indices</h3>

<p>
SQLite may make use of transient indices to
implement SQL language features such as:
</p>

<ul>







|







420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
or not the subquery or outer query contain aggregate functions,
ORDER BY or GROUP BY clauses, LIMIT clauses, and so forth.
The rules for when a query and cannot be flattened are
very complex and are beyond the scope of this document.
</p>

<tcl>hd_fragment transidx</tcl>
<h3>2.8 Transient Indices</h3>

<p>
SQLite may make use of transient indices to
implement SQL language features such as:
</p>

<ul>
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480

481
482
483
484
485
486
487
488
489
490
<p>
Note that the UNION ALL operator for compound queries does not
use transient indices by itself (though of course the right
and left subqueries of the UNION ALL might use transient indices
depending on how they are composed.)

<tcl>hd_fragment vacuumdb</tcl>
<h3>2.7 Transient Database Used By [VACUUM]</h3>

<p>
The [VACUUM] command works by creating a temporary file
and then rebuilding the entire database into that temporary
file.  Then the content of the temporary file is copied back
into the original database file and the temporary file is
deleted.
</p>

<p>
The temporary file created by the [VACUUM] command exists only
for the duration of the command itself.  The size of the temporary
file will be no larger than the original database.
</p>

<tcl>hd_fragment tempstore *tempstore</tcl>
<h2>3.0 The SQLITE_TEMP_STORE Compile-Time Parameter and Pragma</h2>

<p>

The rollback journal, master journal,
and statement journal files are always written
to disk.
But the other kinds of temporary files might be stored in memory
only and never written to disk.
Whether or not temporary files other than the rollback,
master, and statement journals are written to disk or stored only in memory
depends on the [SQLITE_TEMP_STORE] compile-time parameter, the
[temp_store pragma],
and on the size of the temporary file.







|



















>
|
<
|







514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542

543
544
545
546
547
548
549
550
<p>
Note that the UNION ALL operator for compound queries does not
use transient indices by itself (though of course the right
and left subqueries of the UNION ALL might use transient indices
depending on how they are composed.)

<tcl>hd_fragment vacuumdb</tcl>
<h3>2.9 Transient Database Used By [VACUUM]</h3>

<p>
The [VACUUM] command works by creating a temporary file
and then rebuilding the entire database into that temporary
file.  Then the content of the temporary file is copied back
into the original database file and the temporary file is
deleted.
</p>

<p>
The temporary file created by the [VACUUM] command exists only
for the duration of the command itself.  The size of the temporary
file will be no larger than the original database.
</p>

<tcl>hd_fragment tempstore *tempstore</tcl>
<h2>3.0 The SQLITE_TEMP_STORE Compile-Time Parameter and Pragma</h2>

<p>
The temporary files associated with transaction control, namely
the rollback journal, master journal, write-ahead log (WAL) files,

and shared-memory files, are always written to disk.
But the other kinds of temporary files might be stored in memory
only and never written to disk.
Whether or not temporary files other than the rollback,
master, and statement journals are written to disk or stored only in memory
depends on the [SQLITE_TEMP_STORE] compile-time parameter, the
[temp_store pragma],
and on the size of the temporary file.