Adding upstream version 3.40.1.upstream/3.40.1 upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
author: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-05-05 17:28:19 +0000
committer: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-05-05 17:28:19 +0000
commit: 18657a960e125336f704ea058e25c27bd3900dcb (patch)
tree: 17b438b680ed45a996d7b59951e6aa34023783f2 /www/tempfiles.html
parent: Initial commit. (diff)
download: sqlite3-18657a960e125336f704ea058e25c27bd3900dcb.tar.xz
sqlite3-18657a960e125336f704ea058e25c27bd3900dcb.zip
1 files changed, 880 insertions, 0 deletions
diff --git a/www/tempfiles.html b/www/tempfiles.html
new file mode 100644
index 0000000..684746f
--- /dev/null
+++ b/www/tempfiles.html
@@ -0,0 +1,880 @@
+<!DOCTYPE html>
+<html><head>
+<meta name="viewport" content="width=device-width, initial-scale=1.0">
+<meta http-equiv="content-type" content="text/html; charset=UTF-8">
+<link href="sqlite.css" rel="stylesheet">
+<title>Temporary Files Used By SQLite</title>
+<!-- path= -->
+</head>
+<body>
+<div class=nosearch>
+<a href="index.html">
+<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
+</a>
+<div><!-- IE hack to prevent disappearing logo --></div>
+<div class="tagline desktoponly">
+Small. Fast. Reliable.<br>Choose any three.
+</div>
+<div class="menu mainmenu">
+<ul>
+<li><a href="index.html">Home</a>
+<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
+<li class='wideonly'><a href='about.html'>About</a>
+<li class='desktoponly'><a href="docs.html">Documentation</a>
+<li class='desktoponly'><a href="download.html">Download</a>
+<li class='wideonly'><a href='copyright.html'>License</a>
+<li class='desktoponly'><a href="support.html">Support</a>
+<li class='desktoponly'><a href="prosupport.html">Purchase</a>
+<li class='search' id='search_menubutton'>
+<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
+</ul>
+</div>
+<div class="menu submenu" id="submenu">
+<ul>
+<li><a href='about.html'>About</a>
+<li><a href='docs.html'>Documentation</a>
+<li><a href='download.html'>Download</a>
+<li><a href='support.html'>Support</a>
+<li><a href='prosupport.html'>Purchase</a>
+</ul>
+</div>
+<div class="searchmenu" id="searchmenu">
+<form method="GET" action="search">
+<select name="s" id="searchtype">
+<option value="d">Search Documentation</option>
+<option value="c">Search Changelog</option>
+</select>
+<input type="text" name="q" id="searchbox" value="">
+<input type="submit" value="Go">
+</form>
+</div>
+</div>
+<script>
+function toggle_div(nm) {
+var w = document.getElementById(nm);
+if( w.style.display=="block" ){
+w.style.display = "none";
+}else{
+w.style.display = "block";
+}
+}
+function toggle_search() {
+var w = document.getElementById("searchmenu");
+if( w.style.display=="block" ){
+w.style.display = "none";
+} else {
+w.style.display = "block";
+setTimeout(function(){
+document.getElementById("searchbox").focus()
+}, 30);
+}
+}
+function div_off(nm){document.getElementById(nm).style.display="none";}
+window.onbeforeunload = function(e){div_off("submenu");}
+/* Disable the Search feature if we are not operating from CGI, since */
+/* Search is accomplished using CGI and will not work without it. */
+if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
+document.getElementById("search_menubutton").style.display = "none";
+}
+/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
+function hideorshow(btn,obj){
+var x = document.getElementById(obj);
+var b = document.getElementById(btn);
+if( x.style.display!='none' ){
+x.style.display = 'none';
+b.innerHTML='show';
+}else{
+x.style.display = '';
+b.innerHTML='hide';
+}
+return false;
+}
+var antiRobot = 0;
+function antiRobotGo(){
+if( antiRobot!=3 ) return;
+antiRobot = 7;
+var j = document.getElementById("mtimelink");
+if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
+}
+function antiRobotDefense(){
+document.body.onmousedown=function(){
+antiRobot |= 2;
+antiRobotGo();
+document.body.onmousedown=null;
+}
+document.body.onmousemove=function(){
+antiRobot |= 2;
+antiRobotGo();
+document.body.onmousemove=null;
+}
+setTimeout(function(){
+antiRobot |= 1;
+antiRobotGo();
+}, 100)
+antiRobotGo();
+}
+antiRobotDefense();
+</script>
+<div class=fancy>
+<div class=nosearch>
+<div class="fancy_title">
+Temporary Files Used By SQLite
+</div>
+<div class="fancy_toc">
+<a onclick="toggle_toc()">
+<span class="fancy_toc_mark" id="toc_mk">&#x25ba;</span>
+Table Of Contents
+</a>
+<div id="toc_sub"><div class="fancy-toc1"><a href="#introduction">1. Introduction</a></div>
+<div class="fancy-toc1"><a href="#nine_kinds_of_temporary_files">2. Nine Kinds Of Temporary Files</a></div>
+<div class="fancy-toc2"><a href="#rollback_journals">2.1. Rollback Journals</a></div>
+<div class="fancy-toc2"><a href="#write_ahead_log_wal_files">2.2. Write-Ahead Log (WAL) Files</a></div>
+<div class="fancy-toc2"><a href="#shared_memory_files">2.3. Shared-Memory Files</a></div>
+<div class="fancy-toc2"><a href="#super_journal_files">2.4. Super-Journal Files</a></div>
+<div class="fancy-toc2"><a href="#statement_journal_files">2.5. Statement Journal Files</a></div>
+<div class="fancy-toc2"><a href="#temp_databases">2.6. TEMP Databases</a></div>
+<div class="fancy-toc2"><a href="#materializations_of_views_and_subqueries">2.7. Materializations Of Views And Subqueries</a></div>
+<div class="fancy-toc2"><a href="#transient_indices">2.8. Transient Indices</a></div>
+<div class="fancy-toc2"><a href="#transient_database_used_by_vacuum_">2.9. Transient Database Used By <a href="lang_vacuum.html">VACUUM</a></a></div>
+<div class="fancy-toc1"><a href="#the_sqlite_temp_store_compile_time_parameter_and_pragma">3. The SQLITE_TEMP_STORE Compile-Time Parameter and Pragma</a></div>
+<div class="fancy-toc1"><a href="#other_temporary_file_optimizations">4. Other Temporary File Optimizations</a></div>
+<div class="fancy-toc1"><a href="#temporary_file_storage_locations">5. Temporary File Storage Locations</a></div>
+</div>
+</div>
+<script>
+function toggle_toc(){
+var sub = document.getElementById("toc_sub")
+var mk = document.getElementById("toc_mk")
+if( sub.style.display!="block" ){
+sub.style.display = "block";
+mk.innerHTML = "&#x25bc;";
+} else {
+sub.style.display = "none";
+mk.innerHTML = "&#x25ba;";
+}
+}
+</script>
+</div>
+
+
+
+
+
+<h1 id="introduction"><span>1. </span>Introduction</h1>
+
+<p>
+One of the <a href="different.html">distinctive features</a> of
+SQLite is that a database consists of a single disk file.
+This simplifies the use of SQLite since moving or backing up a
+database is a simple as copying a single file.  It also makes
+SQLite appropriate for use as an
+<a href="whentouse.html#appfileformat">application file format</a>.
+But while a complete database is held in a single disk file,
+SQLite does make use of many temporary files during the
+course of processing a database.
+</p>
+
+<p>
+This article describes the various temporary files that SQLite
+creates and uses.  It describes when the files are created, when
+they are deleted, what they are used for, why they are important,
+and how to avoid them on systems where creating temporary files is
+expensive.
+</p>
+
+<p>
+The manner in which SQLite uses temporary files is not considered
+part of the contract that SQLite makes with applications.  The
+information in this document is a correct description of how
+SQLite operates at the time that this document was written or last
+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>
+
+<a name="types"></a>
+
+<h1 id="nine_kinds_of_temporary_files"><span>2. </span>Nine Kinds Of Temporary Files</h1>
+
+<p>
+SQLite currently uses nine distinct types of temporary files:
+</p>
+
+<ol>
+<li>Rollback journals</li>
+<li>Super-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>
+
+<p>
+Additional information about each of these temporary file types
+is in the sequel.
+</p>
+
+<a name="rollbackjrnl"></a>
+
+<h2 id="rollback_journals"><span>2.1. </span>Rollback Journals</h2>
+
+<p>
+A rollback journal is a temporary file used to implement
+atomic commit and rollback capabilities in SQLite.
+(For a detailed discussion of how this works, see
+the separate document titled
+<a href="atomiccommit.html">Atomic Commit In SQLite</a>.)
+The rollback journal is always located in the same directory
+as the database file and has the same name as the database
+file except with the 8 characters "<b>-journal</b>" appended.
+The rollback journal is usually created when a transaction
+is first started and is usually deleted when a transaction
+commits or rolls back.
+The rollback journal file is essential for implementing the
+atomic commit and rollback capabilities of SQLite.  Without
+a rollback journal, SQLite would be unable to rollback an
+incomplete transaction, and if a crash or power loss occurred
+in the middle of a transaction the entire database would likely
+go corrupt without a rollback journal.
+</p>
+
+<p>
+The rollback journal is <i>usually</i> created and destroyed at the
+start and end of a transaction, respectively.  But there are exceptions
+to this rule.
+</p>
+
+<p>
+If a crash or power loss occurs in the middle of a transaction,
+then the rollback journal file is left on disk.  The next time
+another application attempts to open the database file, it notices
+the presence of the abandoned rollback journal (we call it a "hot
+journal" in this circumstance) and uses the information in the
+journal to restore the database to its state prior to the start
+of the incomplete transaction.  This is how SQLite implements
+atomic commit.
+</p>
+
+<p>
+If an application puts SQLite in 
+<a href="pragma.html#pragma_locking_mode">exclusive locking mode</a> using
+the pragma:
+</p>
+
+<blockquote><pre>
+PRAGMA locking_mode=EXCLUSIVE;
+</pre></blockquote>
+
+<p>
+SQLite creates a new rollback journal at the start of the first
+transaction within an exclusive locking mode session.  But at the
+conclusion of the transaction, it does not delete the rollback
+journal.  The rollback journal might be truncated, or its header
+might be zeroed (depending on what version of SQLite you are using)
+but the rollback journal is not deleted.  The rollback journal is
+not deleted until exclusive access mode is exited.</p>
+
+<p>
+Rollback journal creation and deletion is also changed by the
+<a href="pragma.html#pragma_journal_mode">journal_mode pragma</a>.
+The default journaling mode is DELETE, which is the default behavior
+of deleting the rollback journal file at the end of each transaction,
+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 omit the rollback journal, completely.
+In other words, no rollback journal is ever written if journal mode is
+set to OFF.
+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>
+
+<a name="walfile"></a>
+
+<h2 id="write_ahead_log_wal_files"><span>2.2. </span>Write-Ahead Log (WAL) Files</h2>
+
+<p>
+A write-ahead log or WAL file is used in place of a rollback journal
+when SQLite is operating in <a href="wal.html">WAL mode</a>.  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>
+
+<a name="shmfile"></a>
+
+<h2 id="shared_memory_files"><span>2.3. </span>Shared-Memory Files</h2>
+
+<p>
+When operating in <a href="wal.html">WAL mode</a>, 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 <a href="vfs.html">VFS</a> is able to provide an alternative method for accessing shared
+memory, then that alternative method might be used rather than the
+shared-memory file.  For example, if <a href="pragma.html#pragma_locking_mode">PRAGMA locking_mode</a> 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>
+
+<a name="superjrnl"></a>
+
+<h2 id="super_journal_files"><span>2.4. </span>Super-Journal Files</h2>
+
+<p>
+The super-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 <a href="c3ref/sqlite3.html">database connection</a>
+using the <a href="lang_attach.html">ATTACH</a> statement.  The super-journal file is always
+located in the same directory as the main database file
+(the main database file is the database that is identified
+in the original <a href="c3ref/open.html">sqlite3_open()</a>, <a href="c3ref/open.html">sqlite3_open16()</a>, or
+<a href="c3ref/open.html">sqlite3_open_v2()</a> call that created the <a href="c3ref/sqlite3.html">database connection</a>)
+with a randomized suffix.  The super-journal file contains
+the names of all of the various attached auxiliary databases
+that were changed during the transaction.  The multi-database
+transaction commits when the super-journal file is deleted.
+See the documentation titled
+<a href="atomiccommit.html">Atomic Commit In SQLite</a> for
+additional detail.
+</p>
+
+<p>
+Without the super-journal, the transaction commit on a multi-database
+transaction would be atomic for each database individually, but it
+would not be atomic across all databases.  In other words, if the
+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 super-journal causes all
+changes in all databases to either rollback or commit together.
+</p>
+
+<p>
+The super-journal file is only created for <a href="lang_transaction.html">COMMIT</a> operations that
+involve multiple database files where at least two of the databases 
+meet all of the following requirements:
+
+</p><ol>
+<li>The database is modified by the transaction
+</li><li>The <a href="pragma.html#pragma_synchronous">PRAGMA synchronous</a> setting is not OFF
+</li><li>The <a href="pragma.html#pragma_journal_mode">PRAGMA journal_mode</a> is not OFF, MEMORY, or WAL
+</li></ol>
+
+<p>
+This means that SQLite transactions are not atomic
+across multiple database files on a power-loss when the database 
+files have synchronous turned off or when they are using journal 
+modes of OFF, MEMORY, or WAL.  For synchronous OFF and for
+journal_modes OFF and MEMORY, database will usually corrupt if
+a transaction commit is interrupted by a power loss.  For 
+<a href="wal.html">WAL mode</a>, individual database files are updated atomically
+across a power-loss, but in the case of a multi-file transactions,
+some files might rollback while others roll forward after
+power is restored.
+</p>
+
+
+<a name="stmtjrnl"></a>
+
+<h2 id="statement_journal_files"><span>2.5. </span>Statement Journal Files</h2>
+
+<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.
+The statement journal is used to undo the first 50 row changes
+so that the database is restored to the state it was in at the start
+of the statement.
+</p>
+
+<p>
+A statement journal is only created for an UPDATE or INSERT statement
+that might change multiple 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>
+UPDATE OR FAIL ...
+UPDATE OR IGNORE ...
+UPDATE OR REPLACE ...
+UPDATE OR ROLLBACK ...
+INSERT OR FAIL ...
+INSERT OR IGNORE ...
+INSERT OR REPLACE ...
+INSERT OR ROLLBACK ...
+REPLACE INTO ....
+</pre></blockquote>
+
+<p>
+The statement journal is given a randomized name, not necessarily
+in the same directory as the main database, and is automatically
+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>
+
+<a name="tempdb"></a>
+
+<h2 id="temp_databases"><span>2.6. </span>TEMP Databases</h2>
+
+<p>Tables created using the "CREATE TEMP TABLE" syntax are only
+visible to the <a href="c3ref/sqlite3.html">database connection</a> 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.
+This separate temporary database file also has an associated
+rollback journal.
+The temporary database file used to store TEMP tables is deleted
+automatically when the <a href="c3ref/sqlite3.html">database connection</a> is closed
+using <a href="c3ref/close.html">sqlite3_close()</a>.
+</p>
+
+<p>
+The TEMP database file is very similar to auxiliary database
+files added using the <a href="lang_attach.html">ATTACH</a> statement, though with a few
+special properties.
+The TEMP database is always automatically deleted when the
+<a href="c3ref/sqlite3.html">database connection</a> is closed.
+The TEMP database always uses the
+<a href="pragma.html#pragma_synchronous">synchronous=OFF</a> and <a href="pragma.html#pragma_journal_mode">journal_mode=PERSIST</a>
+PRAGMA settings.
+And, the TEMP database cannot be used with <a href="lang_detach.html">DETACH</a> nor can
+another process <a href="lang_attach.html">ATTACH</a> the TEMP database.
+</p>
+
+<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>
+
+<a name="views"></a>
+
+<h2 id="materializations_of_views_and_subqueries"><span>2.7. </span>Materializations Of Views And Subqueries</h2>
+
+<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,
+but sometimes it is not easily avoidable.
+The temporary tables created by materialization are each stored
+in their own separate temporary file, which is automatically
+deleted at the conclusion of the query.
+The size of these temporary tables depends on the amount of
+data in the materialization of the subquery, of course.
+</p>
+
+<p>
+A subquery on the right-hand side of IN operator must often
+be materialized.  For example:
+</p>
+
+<blockquote><pre>
+SELECT * FROM ex1 WHERE ex1.a IN (SELECT b FROM ex2);
+</pre></blockquote>
+
+<p>
+In the query above, the subquery "SELECT b FROM ex2" is evaluated
+and its results are stored in a temporary table (actually a temporary
+index) that allows one to determine whether or not a value ex2.b
+exists using a simple binary search.  Once this table is constructed,
+the outer query is run and for each prospective result row a check
+is made to see if ex1.a is contained within the temporary table.
+The row is output only if the check is true.
+</p>
+
+<p>
+To avoid creating the temporary table, the query might be rewritten
+as follows:
+</p>
+
+<blockquote><pre>
+SELECT * FROM ex1 WHERE EXISTS(SELECT 1 FROM ex2 WHERE ex2.b=ex1.a);
+</pre></blockquote>
+
+<p>
+Recent versions of SQLite (<a href="releaselog/3_5_4.html">version 3.5.4</a> 2007-12-14) 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;
+</pre></blockquote>
+
+<p>
+Depending on the query, SQLite might need to materialize the 
+"(SELECT b FROM ex2)" subquery into a temporary table, then
+perform the join between ex1 and the temporary table.  The
+query optimizer tries to avoid this by "flattening" the
+query.  In the previous example the query can be flattened,
+and SQLite will automatically transform the query into
+</p>
+
+<blockquote><pre>
+SELECT ex1.*, ex2.b FROM ex1 JOIN ex2 ON ex2.b=ex1.a;
+</pre></blockquote>
+
+<p>
+More complex queries may or may not be able to employ query
+flattening to avoid the temporary table.  Whether or not
+the query can be flattened depends on such factors as whether
+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 can and cannot be flattened are
+very complex and are beyond the scope of this document.
+</p>
+
+<a name="transidx"></a>
+
+<h2 id="transient_indices"><span>2.8. </span>Transient Indices</h2>
+
+<p>
+SQLite may make use of transient indices to
+implement SQL language features such as:
+</p>
+
+<ul>
+<li>An ORDER BY or GROUP BY clause</li>
+<li>The DISTINCT keyword in an aggregate query</li>
+<li>Compound SELECT statements joined by UNION, EXCEPT, or INTERSECT</li>
+</ul>
+
+<p>
+Each transient index is stored in its own temporary file.
+The temporary file for a transient index is automatically deleted
+at the end of the statement that uses it.
+</p>
+
+<p>
+SQLite strives to implement ORDER BY clauses using a preexisting
+index.  If an appropriate index already exists, SQLite will walk
+the index, rather than the underlying table, to extract the 
+requested information, and thus cause the rows to come out in
+the desired order.  But if SQLite cannot find an appropriate index
+it will evaluate the query and store each row in a transient index
+whose data is the row data and whose key is the ORDER BY terms.
+After the query is evaluated, SQLite goes back and walks the
+transient index from beginning to end in order to output the
+rows in the desired order.
+</p>
+
+<p>
+SQLite implements GROUP BY by ordering the output rows in the
+order suggested by the GROUP BY terms.  Each output row is
+compared to the previous to see if it starts a new "group".
+The ordering by GROUP BY terms is done in exactly the same way
+as the ordering by ORDER BY terms.  A preexisting index is used
+if possible, but if no suitable index is available, a transient
+index is created.
+</p>
+
+<p>
+The DISTINCT keyword on an aggregate query is implemented by
+creating a transient index in a temporary file and storing
+each result row in that index.  As new result rows are computed
+a check is made to see if they already exist in the transient
+index and if they do the new result row is discarded.
+</p>
+
+<p>
+The UNION operator for compound queries is implemented by creating
+a transient index in a temporary file and storing the results
+of the left and right subquery in the transient index, discarding
+duplicates.  After both subqueries have been evaluated, the
+transient index is walked from beginning to end to generate the final output.
+</p>
+
+<p>
+The EXCEPT operator for compound queries is implemented by creating
+a transient index in a temporary file, storing the results of the
+left subquery in this transient index, then removing the result 
+from right subquery from the transient index, and finally walking
+the index from beginning to end to obtain the final output.
+</p>
+
+<p>
+The INTERSECT operator for compound queries is implemented by
+creating two separate transient indices, each in a separate
+temporary file.  The left and right subqueries are evaluated
+each into a separate transient index.  Then the two indices
+are walked together and entries that appear in both indices
+are output.
+</p>
+
+<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.)
+
+<a name="vacuumdb"></a>
+
+</p><h2 id="transient_database_used_by_vacuum_"><span>2.9. </span>Transient Database Used By <a href="lang_vacuum.html">VACUUM</a></h2>
+
+<p>
+The <a href="lang_vacuum.html">VACUUM</a> 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 <a href="lang_vacuum.html">VACUUM</a> 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>
+
+<a name="tempstore"></a>
+
+<h1 id="the_sqlite_temp_store_compile_time_parameter_and_pragma"><span>3. </span>The SQLITE_TEMP_STORE Compile-Time Parameter and Pragma</h1>
+
+<p>
+The temporary files associated with transaction control, namely
+the rollback journal, super-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,
+super, and statement journals are written to disk or stored only in memory
+depends on the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time parameter, the
+<a href="pragma.html#pragma_temp_store">temp_store pragma</a>,
+and on the size of the temporary file.
+</p>
+
+<p>
+The <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time parameter is a #define whose value is
+an integer between 0 and 3, inclusive.  The meaning of the
+<a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time parameter is as follows:
+</p>
+
+<ol type="1">
+<li value="0">
+Temporary files are always stored on disk regardless of the setting
+of the <a href="pragma.html#pragma_temp_store">temp_store pragma</a>.
+</li>
+<li value="1">
+Temporary files are stored on disk by default but this can be
+overridden by the <a href="pragma.html#pragma_temp_store">temp_store pragma</a>.
+</li>
+<li value="2">
+Temporary files are stored in memory by default but this can be
+overridden by the <a href="pragma.html#pragma_temp_store">temp_store pragma</a>.
+</li>
+<li value="3">
+Temporary files are always stored in memory regardless of the setting
+of the <a href="pragma.html#pragma_temp_store">temp_store pragma</a>.
+</li>
+</ol>
+
+<p>
+The default value of the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time parameter is 1,
+which means to store temporary files on disk but provide the option
+of overriding the behavior using the <a href="pragma.html#pragma_temp_store">temp_store pragma</a>.
+</p>
+
+<p>
+The <a href="pragma.html#pragma_temp_store">temp_store pragma</a> has
+an integer value which also influences the decision of where to store
+temporary files.  The values of the temp_store pragma have the
+following meanings:
+</p>
+
+<ol type="1">
+<li value="0">
+Use either disk or memory storage for temporary files as determined
+by the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time parameter.
+</li>
+<li value="1">
+If the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time parameter specifies memory storage for
+temporary files, then override that decision and use disk storage instead.
+Otherwise follow the recommendation of the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time
+parameter.
+</li>
+<li value="2">
+If the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time parameter specifies disk storage for
+temporary files, then override that decision and use memory storage instead.
+Otherwise follow the recommendation of the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time
+parameter.
+</li>
+</ol>
+
+<p>
+The default setting for the <a href="pragma.html#pragma_temp_store">temp_store pragma</a> is 0,
+which means to following the recommendation of <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time
+parameter.
+</p>
+
+<p>
+To reiterate, the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time parameter and the 
+<a href="pragma.html#pragma_temp_store">temp_store pragma</a> only
+influence the temporary files other than the rollback journal
+and the super-journal.  The rollback journal and the
+super-journal are always written to disk regardless of the settings of
+the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time parameter and the
+<a href="pragma.html#pragma_temp_store">temp_store pragma</a>.
+</p>
+
+<a name="otheropt"></a>
+
+<h1 id="other_temporary_file_optimizations"><span>4. </span>Other Temporary File Optimizations</h1>
+
+<p>
+SQLite uses a page cache of recently read and written database
+pages.  This page cache is used not just for the main database
+file but also for transient indices and tables stored in temporary
+files.  If SQLite needs to use a temporary index or table and
+the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> compile-time parameter and the
+<a href="pragma.html#pragma_temp_store">temp_store pragma</a> are
+set to store temporary tables and index on disk, the information
+is still initially stored in memory in the page cache.  The 
+temporary file is not opened and the information is not truly
+written to disk until the page cache is full.
+</p>
+
+<p>
+This means that for many common cases where the temporary tables
+and indices are small (small enough to fit into the page cache)
+no temporary files are created and no disk I/O occurs.  Only
+when the temporary data becomes too large to fit in RAM does
+the information spill to disk.
+</p>
+
+<p>
+Each temporary table and index is given its own page cache
+which can store a maximum number of database pages determined
+by the SQLITE_DEFAULT_TEMP_CACHE_SIZE compile-time parameter.
+(The default value is 500 pages.)
+The maximum number of database pages in the page cache is the
+same for every temporary table and index.  The value cannot
+be changed at run-time or on a per-table or per-index basis.
+Each temporary file gets its own private page cache with its
+own SQLITE_DEFAULT_TEMP_CACHE_SIZE page limit.
+</p>
+
+<a name="tempdir"></a>
+
+<h1 id="temporary_file_storage_locations"><span>5. </span>Temporary File Storage Locations</h1>
+
+<p>
+The directory or folder in which temporary files are created is
+determined by the OS-specific <a href="vfs.html">VFS</a>.
+
+</p><p>
+On unix-like systems, directories are searched in the following order:
+</p><ol>
+<li>The directory set by <a href="pragma.html#pragma_temp_store_directory">PRAGMA temp_store_directory</a> or by the
+    <a href="c3ref/temp_directory.html">sqlite3_temp_directory</a> global variable
+</li><li>The SQLITE_TMPDIR environment variable
+</li><li>The TMPDIR environment variable
+</li><li>/var/tmp
+</li><li>/usr/tmp
+</li><li>/tmp
+</li><li>The current working directory (".")
+</li></ol>
+The first of the above that is found to exist and have the write and
+execute bits set is used.  The final "." fallback is important for some
+applications that use SQLite inside of chroot jails that do not have
+the standard temporary file locations available.
+
+<p>
+On Windows systems, folders are searched in the following order:
+</p><ol>
+<li>The folder set by <a href="pragma.html#pragma_temp_store_directory">PRAGMA temp_store_directory</a> or by the
+    <a href="c3ref/temp_directory.html">sqlite3_temp_directory</a> global variable
+</li><li>The folder returned by the GetTempPath() system interface.
+</li></ol>
+SQLite itself does not pay any attention to environment variables
+in this case, though presumably the GetTempPath() system call does.
+The search algorithm is different for CYGWIN builds.  Check the 
+source code for details.
+<p align="center"><small><i>This page last modified on  <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink"  data-href="https://sqlite.org/docsrc/finfo/pages/tempfiles.in?m=cd7bffb03130e7094">2020-08-16 11:16:26</a> UTC </small></i></p>
+
author	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-05-05 17:28:19 +0000
committer	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-05-05 17:28:19 +0000
commit	18657a960e125336f704ea058e25c27bd3900dcb (patch)
tree	17b438b680ed45a996d7b59951e6aa34023783f2 /www/tempfiles.html
parent	Initial commit. (diff)
download	sqlite3-18657a960e125336f704ea058e25c27bd3900dcb.tar.xz sqlite3-18657a960e125336f704ea058e25c27bd3900dcb.zip