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