Documentation Source Text

Check-in [a192161bbc]
Login

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

Overview
Comment:Updates to the temporary-files document. Create a link to the document from the main documentation index page. Also patch the journal_mode pragma documentation.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: a192161bbc09b2c1f4d10bfc052aa6bbd18556ba
User & Date: drh 2008-04-25 02:42:29
Context
2008-04-25
12:31
Update the atomiccommit document to reflect the latest PRAGMA journal_mode changes. check-in: ee0e82d0aa user: drh tags: trunk
02:42
Updates to the temporary-files document. Create a link to the document from the main documentation index page. Also patch the journal_mode pragma documentation. check-in: a192161bbc user: drh tags: trunk
2008-04-21
23:33
Typos and cleanup in the tempfiles.html page. check-in: d1f8da061d user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/docs.in.

43
44
45
46
47
48
49








50
51
52
53
54
55
56
  Version 3.3.0 and later supports the ability for two or more
  database connections to share the same page and schema cache.
  This feature is useful for certain specialized applications.
}
doc {Tcl API} {tclsqlite.html} {
  A description of the TCL interface bindings for SQLite.
}









doc {How SQLite Implements Atomic Commit} {atomiccommit.html} {
  A description of the logic within SQLite that implements
  transactions with atomic commit, even in the face of power
  failures.
}
doc {Moving From SQLite 3.4 to 3.5} {34to35.html} {







>
>
>
>
>
>
>
>







43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
  Version 3.3.0 and later supports the ability for two or more
  database connections to share the same page and schema cache.
  This feature is useful for certain specialized applications.
}
doc {Tcl API} {tclsqlite.html} {
  A description of the TCL interface bindings for SQLite.
}

doc {Temporary Files Used By SQLite} {tempfiles.html} {
  SQLite can potentially use many different temporary files when
  processing certain SQL statements.  This document describes the
  many kinds of temporary files that SQLite uses and offers suggestions
  for avoiding them on systems where creating a temporary file is an
  expensive operation.
}

doc {How SQLite Implements Atomic Commit} {atomiccommit.html} {
  A description of the logic within SQLite that implements
  transactions with atomic commit, even in the face of power
  failures.
}
doc {Moving From SQLite 3.4 to 3.5} {34to35.html} {

Changes to pages/pragma.in.

254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
    incremental vacuum may be promoted from a pragma to a separate
    SQL command, or perhaps some variation on the [VACUUM] command.
    Programmers are cautioned to not become enamored with the
    current syntax or functionality as it is likely to change.</p>
</li>

<tcl>Subsection journal_mode</tcl>
<li><p><b>PRAGMA jounal_mode;
       <br>PRAGMA <i>database</i>.journal_mode;
       <br>PRAGMA journal_mode
              = <i>DELETE | PERSIST | OFF</i>
       <br>PRAGMA <i>database</i>.journal_mode
              = <i>DELETE | PERSIST | OFF</i></b></p>

    <p>This pragma queries or sets the journal mode for databases







|







254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
    incremental vacuum may be promoted from a pragma to a separate
    SQL command, or perhaps some variation on the [VACUUM] command.
    Programmers are cautioned to not become enamored with the
    current syntax or functionality as it is likely to change.</p>
</li>

<tcl>Subsection journal_mode</tcl>
<li><p><b>PRAGMA journal_mode;
       <br>PRAGMA <i>database</i>.journal_mode;
       <br>PRAGMA journal_mode
              = <i>DELETE | PERSIST | OFF</i>
       <br>PRAGMA <i>database</i>.journal_mode
              = <i>DELETE | PERSIST | OFF</i></b></p>

    <p>This pragma queries or sets the journal mode for databases

Changes to pages/tempfiles.in.

43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
...
186
187
188
189
190
191
192
193

194
195
196
197
198
199
200
201
...
298
299
300
301
302
303
304























305
306
307
308
309
310
311
...
442
443
444
445
446
447
448

449
450
451
452
453
454
455
456
457
458
459
460
461
</p>

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

<p>
Additional information about each of these temporary file types
is in the sequel.
................................................................................
</p>

<p>
A statement journal is only created for an UPDATE or INSERT statement
that might change muliple rows of a database and which might hit a
constraint or a RAISE exception within a trigger and thus need to
undo partial results.
If the UPDATE or INSERT is the first or only statement within a

transaction, then no statement journal is created since the ordinary
rollback journal can be used instead.
The statement journal is also omitted if an alternative
<a href="lang_conflict.html">conflict resolution algorithm</a> is
used.  For example:
</p>

<blockquote><pre>
................................................................................

<p>
Recent versions of SQLite (version 3.5.4 and later)
will do this rewrite automatically
if an index exists on the column ex2.b.
</p>
























<p>
Subqueries might also need to be materialized when they appear
in the FROM clause of a SELECT statement.  For example:
</p>

<blockquote><pre>
SELECT * FROM ex1 JOIN (SELECT b FROM ex2) AS t ON t.b=ex1.a;
................................................................................
for the duration of the command itself.  The size of the temporary
file will be no larger than the original database.
</p>

<h2>3.0 The TEMP_STORE Compile-Time Parameter and Pragma</h2>

<p>

The rollback journal and master 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
and master journals are written to disk or stored only in memory
depends on the TEMP_STORE compile-time parameter, the
<a href="pragma.html#pragma_temp_store">temp_store</a> [PRAGMA],
and on the size of the temporary file.
</p>

<p>
The TEMP_STORE compile-time parameter is a #define whose value is







|







 







|
>
|







 







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







 







>
|



|
|







43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
...
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
...
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
...
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
</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>

<p>
Additional information about each of these temporary file types
is in the sequel.
................................................................................
</p>

<p>
A statement journal is only created for an UPDATE or INSERT statement
that might change muliple rows of a database and which might hit a
constraint or a RAISE exception within a trigger and thus need to
undo partial results.
If the UPDATE or INSERT is not contained within BEGIN...COMMIT and if
there are no other active statements on the same database connection then
no statement journal is created since the ordinary
rollback journal can be used instead.
The statement journal is also omitted if an alternative
<a href="lang_conflict.html">conflict resolution algorithm</a> is
used.  For example:
</p>

<blockquote><pre>
................................................................................

<p>
Recent versions of SQLite (version 3.5.4 and later)
will do this rewrite automatically
if an index exists on the column ex2.b.
</p>

<p>
If the right-hand side of an IN operator can be list of values
as in the following:
</p>
<blockquote><pre>
SELECT * FROM ex1 WHERE a IN (1,2,3);
</pre></blockquote>
<p>
List values on the right-hand side of IN are treated as a 
subquery that must be materialized.  In other words, the
previous statement acts as if it were:
</p>
<blockquote><pre>
SELECT * FROM ex1 WHERE a IN (SELECT 1 UNION ALL
                              SELECT 2 UNION ALL
                              SELECT 3);
</pre></blockquote>
<p>
A temporary index is always used to hold the values of the
right-hand side of an IN operator when that right-hand side
is a list of values.
</p>

<p>
Subqueries might also need to be materialized when they appear
in the FROM clause of a SELECT statement.  For example:
</p>

<blockquote><pre>
SELECT * FROM ex1 JOIN (SELECT b FROM ex2) AS t ON t.b=ex1.a;
................................................................................
for the duration of the command itself.  The size of the temporary
file will be no larger than the original database.
</p>

<h2>3.0 The 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 TEMP_STORE compile-time parameter, the
<a href="pragma.html#pragma_temp_store">temp_store</a> [PRAGMA],
and on the size of the temporary file.
</p>

<p>
The TEMP_STORE compile-time parameter is a #define whose value is