Documentation Source Text

Check-in [74740a2d9e]
Login

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

Overview
Comment:Add documentation for the cache_spill pragma.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 74740a2d9e5101e3e789ea007df0b131cd2d7f57
User & Date: drh 2013-08-17 17:56:15
Context
2013-08-19
14:26
Fix a typo in the RTREE documentation. check-in: 9ceaba9f7f user: drh tags: trunk
2013-08-17
17:56
Add documentation for the cache_spill pragma. check-in: 74740a2d9e user: drh tags: trunk
2013-08-08
17:58
Fix typos in the "vfs.html" document. check-in: 37c89b86f8 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to pages/changes.in.

    50     50   chng {2013-08-29 (3.8.0)} {
    51     51   <li>Add support for [partial indexes]</li>
    52     52   <li>Cut-over to the [next generation query planner] for faster and better query plans.
    53     53   <li>The [EXPLAIN QUERY PLAN] output no longer shows an estimate of the number of 
    54     54       rows generated by each loop in a join.
    55     55   <li>Added the [FTS4 notindexed option], allowing non-indexed columns in an FTS4 table.
    56     56   <li>Added the [SQLITE_STMTSTATUS_VM_STEP] option to [sqlite3_stmt_status()].
           57  +<li>Added the [cache_spill pragma].
    57     58   <li>Added the "percentile()" function as a [loadable extension] in the ext/misc
    58     59       subdirectory of the source tree.
    59     60   <li>Added the [SQLITE_ALLOW_URI_AUTHORITY] compile-time option.
    60     61   <li>Add the [sqlite3_cancel_auto_extension(X)] interface.
    61     62   <li>A running SELECT statement that lacks a FROM clause (or any other statement that
    62     63       never reads or writes from any database file) will not prevent a read
    63     64       transaction from closing.

Changes to pages/pragma.in.

    22     22     set PragmaKeys($main_name) $namelist
    23     23     foreach x $namelist {
    24     24       set PragmaRef($x) $main_name
    25     25     }
    26     26   }
    27     27   proc LegacyDisclaimer {} {
    28     28     return {
    29         -    <p><u>Do not use this pragma!</u> This pragma is deprecated and exists
           29  +    <p style='background-color: #ffd0d0;'>
           30  +    <b>This pragma is deprecated</b> and exists
    30     31       for backwards compatibility only.  New applications
    31     32       should avoid using this pragma.  Older applications should discontinue
    32     33       use of this pragma at the earliest opportunity.  This pragma may be omitted
    33     34       from the build when SQLite is compiled using [SQLITE_OMIT_DEPRECATED].
    34     35       </p>
    35     36     }
    36     37   }
    37     38   proc DebugDisclaimer {} {
    38     39     return {
    39         -    <p>This pragma is intended for use when debugging SQLite itself.  It
           40  +    <p style='background-color: #f0e0ff;'>
           41  +    This pragma is intended for use when debugging SQLite itself.  It
    40     42       is only contained in the build when the [SQLITE_DEBUG] compile-time option
    41     43       is used.</p>
    42     44     }
    43     45   }
    44     46   # Legacy pragma - do not use these
    45     47   proc LegacyPragma {namelist content} {
    46     48     Pragma $namelist [string map [list DISCLAIMER [LegacyDisclaimer]] $content]
................................................................................
   206    208            <br>PRAGMA busy_timeout = </b><i>milliseconds</i><b>;</b></p>
   207    209       <p>Query or change the setting of the
   208    210       [sqlite3_busy_timeout | busy timeout].)^
   209    211       This pragma is an alternative to the [sqlite3_busy_timeout()] C-language
   210    212       interface which is made available as a pragma for use with language
   211    213       bindings that do not provide direct access to [sqlite3_busy_timeout()].
   212    214   }
          215  +
          216  +Pragma cache_spill {
          217  +    <p>^(<b>PRAGMA cache_spill;
          218  +         <br>PRAGMA cache_spill=</b><i>boolean</i><b>;</b>)^</p>
          219  +
          220  +    <p>^(The cache_spill pragme enables or disables the ability of the pager
          221  +    to spill dirty cache pages to the database file in the middle of a 
          222  +    transaction.)^  ^(Cache_spill is enabled by default)^ and most applications
          223  +    should leave it that way as cache spilling is unusally advantageous.
          224  +    However, a cache spill has the side-effect of acquiring an
          225  +    [EXCLUSIVE lock] on the database file.  Hence, some applications that
          226  +    have large long-running transactions may want to disable cache spilling
          227  +    in order to prevent the application from acquiring an exclusive lock
          228  +    on the database until the moment that the transaction [COMMIT]s.
          229  +}
   213    230   
   214    231   Pragma cache_size {
   215    232       <p>^(<b>PRAGMA cache_size;
   216    233          <br>PRAGMA cache_size = </b><i>pages</i><b>;
   217    234          <br>PRAGMA cache_size = -</b><i>kibibytes</i><b>;</b></p>
   218    235       <p>Query or change the suggested maximum number of database disk pages
   219    236       that SQLite will hold in memory at once per open database file.)^  Whether
................................................................................
   273    290       setting is irrelevant.</p>
   274    291   }
   275    292   
   276    293   LegacyPragma count_changes {
   277    294       <p><b>PRAGMA count_changes;
   278    295          <br>PRAGMA count_changes = </b>boolean</i><b>;</b></p>
   279    296   
   280         -    DISCLAIMER
   281         -  
   282    297       <p>Query or change the count-changes flag. Normally, when the
   283    298       count-changes flag is not set, [INSERT], [UPDATE] and [DELETE] statements
   284    299       return no data. When count-changes is set, each of these commands 
   285    300       returns a single row of data consisting of one integer value - the
   286    301       number of rows inserted, modified or deleted by the command. The 
   287    302       returned change count does not include any insertions, modifications
   288    303       or deletions performed by triggers, or any changes made automatically
................................................................................
   291    306       <p>Another way to get the row change counts is to use the
   292    307       [sqlite3_changes()] or [sqlite3_total_changes()] interfaces.
   293    308       There is a subtle different, though.  When an INSERT, UPDATE, or
   294    309       DELETE is run against a view using an [INSTEAD OF trigger],
   295    310       the count_changes pragma reports the number of rows in the view
   296    311       that fired the trigger, whereas [sqlite3_changes()] and
   297    312       [sqlite3_total_changes()] do not.
          313  +
          314  +    DISCLAIMER
   298    315   }
   299    316   
   300    317   LegacyPragma default_cache_size {
   301    318       ^(<b>PRAGMA default_cache_size;
   302    319          <br>PRAGMA default_cache_size = </b><i>Number-of-pages</i><b>;</b></p>
   303    320   
   304    321       <p>This pragma queries or sets the suggested maximum number of pages
................................................................................
   313    330       DISCLAIMER
   314    331   }
   315    332   
   316    333   LegacyPragma empty_result_callbacks {
   317    334       <p><b>PRAGMA empty_result_callbacks;
   318    335          <br>PRAGMA empty_result_callbacks = </b><i>boolean</i><b>;</b></p>
   319    336   
   320         -    DISCLAIMER
   321         -
   322    337       <p>Query or change the empty-result-callbacks flag.</p>
   323    338   
   324    339       <p>The empty-result-callbacks flag affects the [sqlite3_exec()] API only.
   325    340       Normally, when the empty-result-callbacks flag is cleared, the
   326    341       callback function supplied to the [sqlite3_exec()] is not invoked
   327    342       for commands that return zero rows of data.  When empty-result-callbacks
   328    343       is set in this situation, the callback function is invoked exactly once,
   329    344       with the third parameter set to 0 (NULL). This is to enable programs  
   330    345       that use the [sqlite3_exec()] API to retrieve column-names even when
   331    346       a query returns no data.</p>
          347  +
          348  +    DISCLAIMER
   332    349   }   
   333    350       
   334    351   
   335    352   Pragma encoding {
   336    353      <p>^(<b>PRAGMA encoding;
   337    354          <br>PRAGMA encoding = "UTF-8";
   338    355          <br>PRAGMA encoding = "UTF-16";
................................................................................
   386    403   }
   387    404   
   388    405   
   389    406   LegacyPragma full_column_names {
   390    407       <p><b>PRAGMA full_column_names;
   391    408          <br>PRAGMA full_column_names = </b><i>boolean</i><b>;</b></p>
   392    409   
   393         -    DISCLAIMER
   394         -
   395    410       <p>Query or change the full_column_names flag. This flag together 
   396    411       with the [short_column_names] flag determine
   397    412       the way SQLite assigns names to result columns of [SELECT] statements.
   398    413       Result columns are named by applying the following rules in order:
   399    414       <ol>
   400    415       <li><p>If there is an AS clause on the result, then the name of
   401    416           the column is the right-hand side of the AS clause.</p></li>
................................................................................
   407    422           source table name prefix:  COLUMN.</p></li>
   408    423       <li><p>If both pragmas [short_column_names] and [full_column_names]
   409    424           are OFF then case (2) applies.
   410    425           </p></li>
   411    426       <li><p>The name of the result column is a combination of the source table
   412    427           and source column name:  TABLE.COLUMN</p></li>
   413    428       </ol>
          429  +
          430  +    DISCLAIMER
   414    431   }
   415    432   
   416    433   Pragma fullfsync {
   417    434       <p>^(<b>PRAGMA fullfsync
   418    435          <br>PRAGMA fullfsync = </b><i>boolean</i><b>;</b></p>
   419    436       <p>Query or change the fullfsync flag.)^ ^This flag
   420    437       determines whether or not the F_FULLFSYNC syncing method is used
................................................................................
   817    834       </p>
   818    835   }
   819    836   
   820    837   LegacyPragma short_column_names {
   821    838       <p><b>PRAGMA short_column_names;
   822    839          <br>PRAGMA short_column_names = </b><i>boolean</i><b>;</b></p>
   823    840   
   824         -    DISCLAIMER
   825         -
   826    841       <p>Query or change the short-column-names flag. This flag affects
   827    842       the way SQLite names columns of data returned by [SELECT] statements.
   828    843       See the [full_column_names] pragma for full details.
   829    844       </p>
          845  +
          846  +    DISCLAIMER
   830    847   }
   831    848   
   832    849   Pragma shrink_memory {
   833    850       <p><b>PRAGMA shrink_memory</b></p>
   834    851   
   835    852       <p>^This pragma causes the database connection on which it is invoked
   836    853       to free up as much memory as it can, by calling
................................................................................
   945    962   LegacyPragma temp_store_directory {
   946    963       <p><b>PRAGMA temp_store_directory;
   947    964          <br>PRAGMA temp_store_directory = '</b><i>directory-name</i><b>';</b></p>
   948    965       <p>Query or change the value of the [sqlite3_temp_directory] global
   949    966       variable, which many operating-system interface backends use to
   950    967       determine where to store [temporary tables] and indices.</p>
   951    968   
   952         -    DISCLAIMER
   953         -
   954    969       <p>When the temp_store_directory setting is changed, all existing temporary
   955    970       tables, indices, triggers, and viewers in the database connection that
   956    971       issued the pragma are immediately deleted.  In
   957    972       practice, temp_store_directory should be set immediately after the first
   958    973       database connection for a process is opened.  If the temp_store_directory
   959    974       is changed for one database connection while other database connections
   960    975       are open in the same process, then the behavior is undefined and
................................................................................
   973    988       error is raised if <i>directory-name</i> is not found or is not
   974    989       writable. </p>
   975    990   
   976    991       <p>The default directory for temporary files depends on the OS.  Some
   977    992       OS interfaces may choose to ignore this variable and place temporary
   978    993       files in some other directory different from the directory specified
   979    994       here.  In that sense, this pragma is only advisory.</p>
          995  +
          996  +    DISCLAIMER
   980    997   }
   981    998   
   982    999   LegacyPragma data_store_directory {
   983   1000       <p><b>PRAGMA data_store_directory;
   984   1001          <br>PRAGMA data_store_directory = '</b><i>directory-name</i><b>';</b></p>
   985   1002       <p>Query or change the value of the [sqlite3_data_directory] global
   986   1003       variable, which windows operating-system interface backends use to
   987   1004       determine where to store database files specified using a relative
   988   1005       pathname.</p>
   989   1006   
   990         -    DISCLAIMER
   991         -
   992   1007       <p>Changing the data_store_directory setting is <u>not</u> threadsafe.
   993   1008       Never change the data_store_directory setting if another thread
   994   1009       within the application is running any SQLite interface at the same time.
   995   1010       Doing so results in undefined behavior.  Changing the data_store_directory
   996   1011       setting writes to the [sqlite3_data_directory] global
   997   1012       variable and that global variable is not protected by a mutex.</p>
   998   1013   
   999   1014       <p>This facility is provided for WinRT which does not have an OS
  1000   1015       mechanism for reading or changing the current working directory.
  1001   1016       The use of this pragma in any other context is discouraged and may
  1002   1017       be disallowed in future releases.</p>
         1018  +
         1019  +    DISCLAIMER
  1003   1020   }
  1004   1021   
  1005   1022   Pragma collation_list {
  1006   1023       <p>^(<b>PRAGMA collation_list;</b></p>
  1007   1024       <p>Return a list of the collating sequences defined for the current
  1008   1025       database connection.</p>)^
  1009   1026   }
................................................................................
  1144   1161       integrity_check.  Otherwise the two pragmas are the same.
  1145   1162       </p>
  1146   1163   }
  1147   1164   
  1148   1165   DebugPragma parser_trace {
  1149   1166       <p><b>PRAGMA parser_trace = </b><i>boolean</i><b>; </b></p>
  1150   1167   
  1151         -    DISCLAIMER
  1152         -
  1153   1168       <p>If SQLite has been compiled with the [SQLITE_DEBUG] compile-time
  1154   1169       option, then the parser_trace pragma can be used to turn on tracing
  1155   1170       for the SQL parser used internally by SQLite.
  1156   1171       This feature is used for debugging SQLite itself.</p>
         1172  +
         1173  +    DISCLAIMER
  1157   1174   }
  1158   1175   
  1159   1176   DebugPragma vdbe_trace {
  1160   1177       <p><b>PRAGMA vdbe_trace = </b><i>boolean</i><b>;</b></p>
  1161   1178   
  1162         -    DISCLAIMER
  1163         -
  1164   1179       <p>If SQLite has been compiled with the [SQLITE_DEBUG] compile-time
  1165   1180       option, then the vdbe_trace pragma can be used to cause virtual machine
  1166   1181       opcodes to be printed on standard output as they are evaluated.
  1167   1182       This feature is used for debugging SQLite.  See the 
  1168   1183       <a href="vdbe.html#trace">VDBE documentation</a> for more 
  1169   1184       information.</p>
         1185  +
         1186  +    DISCLAIMER
  1170   1187   }
  1171   1188   
  1172   1189   DebugPragma vdbe_listing {
  1173   1190       <p><b>PRAGMA vdbe_listing = </b><i>boolean</i><b>;</b></p>
  1174   1191   
  1175         -    DISCLAIMER
  1176         -
  1177   1192       <p>If SQLite has been compiled with the [SQLITE_DEBUG] compile-time
  1178   1193       option, then the vdbe_listing pragma can be used to cause a complete
  1179   1194       listing of the virtual machine opcodes to appear on standard output
  1180   1195       as each statement is evaluated.
  1181   1196       With listing is on, the entire content of a program is printed
  1182   1197       just prior to beginning execution.  The statement
  1183   1198       executes normally after the listing is printed.
  1184   1199       This feature is used for debugging SQLite itself.  See the 
  1185   1200       <a href="vdbe.html#trace">VDBE documentation</a> for more 
  1186   1201       information.</p>
         1202  +
         1203  +    DISCLAIMER
  1187   1204   }
  1188   1205   
  1189   1206   DebugPragma vdbe_debug {
  1190   1207       <p><b>PRAGMA vdbe_debug = </b><i>boolean</i><b>;</b></p>
  1191   1208   
  1192         -    DISCLAIMER
  1193         -
  1194   1209       <p>If SQLite has been compiled with the [SQLITE_DEBUG] compile-time
  1195   1210       option, then the vdbe_debug pragma is a shorthand for three other
  1196   1211       debug-only pragmas: vdbe_addoptrace, vdbe_listing, and vdbe_trace.
  1197   1212       This feature is used for debugging SQLite itself.  See the 
  1198   1213       <a href="vdbe.html#trace">VDBE documentation</a> for more 
  1199   1214       information.</p>
         1215  +
         1216  +    DISCLAIMER
  1200   1217   }
  1201   1218   DebugPragma vdbe_addoptrace {
  1202   1219       <p><b>PRAGMA vdbe_addoptrace = </b><i>boolean</i><b>;</b></p>
  1203   1220   
  1204         -    DISCLAIMER
  1205         -
  1206   1221       <p>If SQLite has been compiled with the [SQLITE_DEBUG] compile-time
  1207   1222       option, then the vdbe_addoptrace pragma can be used to cause a complete
  1208   1223       VDBE opcodes to be displayed as they are created during code generation.
  1209   1224       This feature is used for debugging SQLite itself.  See the 
  1210   1225       <a href="vdbe.html#trace">VDBE documentation</a> for more 
  1211   1226       information.</p>
         1227  +
         1228  +    DISCLAIMER
  1212   1229   }
  1213   1230   
  1214   1231   Pragma wal_checkpoint {
  1215   1232       <p><b>PRAGMA </b><i>database</i><b>.wal_checkpoint;</b><br>
  1216   1233       <b>PRAGMA </b><i>database</i><b>.wal_checkpoint(PASSIVE);</b><br>
  1217   1234       <b>PRAGMA </b><i>database</i><b>.wal_checkpoint(FULL);</b><br>
  1218   1235       <b>PRAGMA </b><i>database</i><b>.wal_checkpoint(RESTART);</b>