Documentation Source Text

Check-in [306698cb1d]
Login

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

Overview
Comment:Clarify that PRAGMA wal_autocheckpoint always causes a PASSIVE checkpoint. Provide clearer definitions for PASSIVE, FULL, and RESET checkpoints.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 306698cb1d87a6ffa83a1fa733a9c624a20135ce
User & Date: drh 2014-07-18 19:06:05
Context
2014-07-22
12:24
Enhance the file format description to allow unrecognized text tokens at the end of the stat column in sqlite_stat1. check-in: 3e1aad19c5 user: drh tags: trunk
2014-07-18
19:06
Clarify that PRAGMA wal_autocheckpoint always causes a PASSIVE checkpoint. Provide clearer definitions for PASSIVE, FULL, and RESET checkpoints. check-in: 306698cb1d user: drh tags: trunk
2014-07-07
13:13
Clarification of the automerge documentation in FTS3. check-in: a8e5cf50a0 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/pragma.in.

220
221
222
223
224
225
226




227
228
229
230
231
232
233
....
1303
1304
1305
1306
1307
1308
1309

1310


1311
1312
1313
1314






1315





















1316
1317
1318
1319
1320
1321
1322
....
1340
1341
1342
1343
1344
1345
1346
1347

1348
1349
1350
1351
1352
1353
1354
    <p>^(<b>PRAGMA busy_timeout;
         <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 pragma enables or disables the ability of the pager
................................................................................

    <p>^If the [write-ahead log] is enabled (via the [journal_mode pragma]),
    this pragma causes a [checkpoint] operation to run on database
    <i>database</i>, or on all attached databases if <i>database</i>
    is omitted.  ^If [write-ahead log] mode is disabled, this pragma is a
    harmless no-op.</p>


    <p>^Invoking this pragma is equivalent to calling the


    [sqlite3_wal_checkpoint_v2()] C interface with a 
    [SQLITE_CHECKPOINT_PASSIVE | 3rd parameter]
    corresponding to the argument of the PRAGMA.  ^Invoking this
    pragma without an argument is equivalent to calling the






    [sqlite3_wal_checkpoint()] C interface.</p>






















    <p>^The wal_checkpoint pragma returns a single row with three
    integer columns.  ^The first column is usually 0 but will be
    1 if a RESTART or FULL checkpoint was blocked from completing,
    for example because another thread or process was actively
    using the database.  ^In other words, the first column is 0 if the
    equivalent call to [sqlite3_wal_checkpoint_v2()] would have returned
................................................................................
    When the [write-ahead log] is enabled (via the
    [journal_mode pragma]) a checkpoint will be run automatically whenever
    the write-ahead log equals or exceeds <i>N</i> pages in length.
    Setting the auto-checkpoint size to zero or a negative value
    turns auto-checkpointing off.</p>
    
    <p>^This pragma is a wrapper around the
    [sqlite3_wal_autocheckpoint()] C interface.</p>


    <p>^Autocheckpointing is enabled by default with an interval
    of 1000 or [SQLITE_DEFAULT_WAL_AUTOCHECKPOINT].</p>

}

Pragma ignore_check_constraints {







>
>
>
>







 







>
|
>
>


|
<
>
>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







|
>







220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
....
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320

1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
....
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
    <p>^(<b>PRAGMA busy_timeout;
         <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()].

    <p>Each database connection can only have a single
    [sqlite3_busy_handler|busy handler].  This PRAGMA sets the busy handler
    for the process, possibly overwriting any previously set busy handler.
}

Pragma cache_spill {
    <p>^(<b>PRAGMA cache_spill;
         <br>PRAGMA cache_spill=</b><i>boolean</i><b>;</b>)^</p>

    <p>^(The cache_spill pragma enables or disables the ability of the pager
................................................................................

    <p>^If the [write-ahead log] is enabled (via the [journal_mode pragma]),
    this pragma causes a [checkpoint] operation to run on database
    <i>database</i>, or on all attached databases if <i>database</i>
    is omitted.  ^If [write-ahead log] mode is disabled, this pragma is a
    harmless no-op.</p>

    <p>^Invoking this
    pragma without an argument is equivalent to calling the
    [sqlite3_wal_checkpoint()] C interface.</p>
    ^Invoking this pragma with an argument is equivalent to calling the
    [sqlite3_wal_checkpoint_v2()] C interface with a 
    [SQLITE_CHECKPOINT_PASSIVE | 3rd parameter]
    corresponding to the argument:


    <dl>
    <dt>PASSIVE<dd>
      Checkpoint as many frames as possible without waiting for any database 
      readers or writers to finish. Sync the db file if all frames in the log
      are checkpointed. This mode is the same as calling 
      sqlite3_wal_checkpoint(). The
      [sqlite3_busy_handler|busy-handler callback] is never invoked.
   
    <dt>FULL<dd>
      This mode blocks 
      (invokes the [sqlite3_busy_handler|busy-handler callback])
      until there is no
      database writer and all readers are reading from the most recent database
      snapshot. It then checkpoints all frames in the log file and syncs the
      database file. This call blocks database writers while it is running,
      but not database readers.
   
    <dt>RESTART<dd>
      This mode works the same way as SQLITE_CHECKPOINT_FULL, except after 
      checkpointing the log file it blocks (calls the 
      [sqlite3_busy_handler|busy-handler callback])
      until all readers are reading from the database file only. This ensures 
      that the next client to write to the database file restarts the log file 
      from the beginning. This call blocks database writers while it is running,
      but not database readers.
    </dl>


    <p>^The wal_checkpoint pragma returns a single row with three
    integer columns.  ^The first column is usually 0 but will be
    1 if a RESTART or FULL checkpoint was blocked from completing,
    for example because another thread or process was actively
    using the database.  ^In other words, the first column is 0 if the
    equivalent call to [sqlite3_wal_checkpoint_v2()] would have returned
................................................................................
    When the [write-ahead log] is enabled (via the
    [journal_mode pragma]) a checkpoint will be run automatically whenever
    the write-ahead log equals or exceeds <i>N</i> pages in length.
    Setting the auto-checkpoint size to zero or a negative value
    turns auto-checkpointing off.</p>
    
    <p>^This pragma is a wrapper around the
    [sqlite3_wal_autocheckpoint()] C interface.
    All automatic checkpoints are [sqlite3_wal_checkpoint_v2|PASSIVE].</p>

    <p>^Autocheckpointing is enabled by default with an interval
    of 1000 or [SQLITE_DEFAULT_WAL_AUTOCHECKPOINT].</p>

}

Pragma ignore_check_constraints {

Changes to pages/wal.in.

263
264
265
266
267
268
269
270
271
272

















273
274
275
276
277
278
279
using the [wal_checkpoint pragma] or by calling the
[sqlite3_wal_checkpoint()] C interface.  The automatic checkpoint
threshold can be changed or automatic checkpointing can be completely
disabled using the [wal_autocheckpoint pragma] or by calling the
[sqlite3_wal_autocheckpoint()] C interface.  A program can also 
use [sqlite3_wal_hook()] to register a callback to be invoked whenever
any transaction commits to the WAL.  This callback can then invoke
[sqlite3_wal_checkpoint()] to for a checkpoint based on whatever
criteria it thinks is appropriate.  (The automatic checkpoint mechanism
is implemented as a simple wrapper around [sqlite3_wal_hook()].)</p>


















<h3>Persistence of WAL mode</h3>

<p>Unlike the other journaling modes, 
[journal_mode | PRAGMA journal_mode=WAL] is
persistent.  If a process sets WAL mode, then closes and reopens the
database, the database will come back in WAL mode.  In contrast, if







|


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







263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
using the [wal_checkpoint pragma] or by calling the
[sqlite3_wal_checkpoint()] C interface.  The automatic checkpoint
threshold can be changed or automatic checkpointing can be completely
disabled using the [wal_autocheckpoint pragma] or by calling the
[sqlite3_wal_autocheckpoint()] C interface.  A program can also 
use [sqlite3_wal_hook()] to register a callback to be invoked whenever
any transaction commits to the WAL.  This callback can then invoke
[sqlite3_wal_checkpoint()] or [sqlite3_wal_checkpoint_v2()] based on whatever
criteria it thinks is appropriate.  (The automatic checkpoint mechanism
is implemented as a simple wrapper around [sqlite3_wal_hook()].)</p>

<h3>Application-Initiated Checkpoints</h3>

<p>An application can initiate a checkpoint using any writable database
connection on the database simply by invoking
[sqlite3_wal_checkpoint()] or [sqlite3_wal_checkpoint_v2()].
There are three subtypes of checkpoints that vary in their aggressiveness:
PASSIVE, FULL, and RESTART.  The default checkpoint style is PASSIVE, which
does as much work as it can without interfering with other database
connections, and which might not run to completion if there are
concurrent readers or writers.
All checkpoints initiated by [sqlite3_wal_checkpoint()] and
by the automatic checkpoint mechanism are PASSIVE.  FULL and RESTART
checkpoints try harder to run the checkpoint to completion and can only
be initiated by a call to [sqlite3_wal_checkpoint_v2()].  See the
[sqlite3_wal_checkpoint_v2()] documentation for additional information
on FULL and RESET checkpoints.

<h3>Persistence of WAL mode</h3>

<p>Unlike the other journaling modes, 
[journal_mode | PRAGMA journal_mode=WAL] is
persistent.  If a process sets WAL mode, then closes and reopens the
database, the database will come back in WAL mode.  In contrast, if