Documentation Source Text

Check-in [bcf687da9c]
Login

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

Overview
Comment:Update the documentation for PRAGMA synchronous=EXTRA.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: bcf687da9c17229228dd17619a83fa68365cdab9
User & Date: drh 2016-02-03 19:47:59.319
Context
2016-02-08
19:55
Update fts3 documentation to mention that fts3_tokenzer(x,y) is only available if SQLITE_ENABLE_FTS3_TOKENIZER is defined at compile time. (check-in: 27ee6d0233 user: dan tags: trunk)
2016-02-03
19:47
Update the documentation for PRAGMA synchronous=EXTRA. (check-in: bcf687da9c user: drh tags: trunk)
2016-02-02
03:15
Update the change log. (check-in: 430d0b230f user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/changes.in.
21
22
23
24
25
26
27


28

29
30
31
32
33
34
35
}

chng {2016-03-00 (3.11.0)} {
<p><b>General improvements:</b>
<li>Enhanced [WAL mode] so that it works efficiently with transactions that are
    larger than the [cache_size].
<li>Added the [FTS5 detail option].


<li>Added the [SQLITE_EXTRA_DURABLE] compile-time option.

<li>Enhanced the [query planner] so that it is able to use
    a [covering index] as part of the [OR optimization].
<li>Many micro-optimizations, resulting in a library that is both smaller
    and faster than the previous release.
<p><b>Enhancements to the [command-line shell]:</b>
<li>Added the ".vfslist" [dot-commands|dot-command].
<p><b>Enhancements to the [TCL Interface]:</b>







>
>
|
>







21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
}

chng {2016-03-00 (3.11.0)} {
<p><b>General improvements:</b>
<li>Enhanced [WAL mode] so that it works efficiently with transactions that are
    larger than the [cache_size].
<li>Added the [FTS5 detail option].
<li>Added the "EXTRA" option to [PRAGMA synchronous] that does a sync of the
    containing directory when a rollback journal is unlinked in DELETE mode,
    for better durability.  The [SQLITE_EXTRA_DURABLE] compile-time option enables
    [PRAGMA synchronous=EXTRA] by default.
<li>Enhanced the [query planner] so that it is able to use
    a [covering index] as part of the [OR optimization].
<li>Many micro-optimizations, resulting in a library that is both smaller
    and faster than the previous release.
<p><b>Enhancements to the [command-line shell]:</b>
<li>Added the ".vfslist" [dot-commands|dot-command].
<p><b>Enhancements to the [TCL Interface]:</b>
Changes to pages/compile.in.
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
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
  the [SQLITE_LIMIT_WORKER_THREADS] parameter.  The [SQLITE_LIMIT_WORKER_THREADS]
  parameter sets the maximum number of auxiliary threads that a single
  [prepared statement] will launch to assist it with a query.  If not specified,
  the default maximum is 0.
  The value set here cannot be more than [SQLITE_MAX_WORKER_THREADS].
}

COMPILE_OPTION {SQLITE_EXTRA_DURABLE=<i>&lt;0 or 1&gt;</i>} {
  If the SQLITE_EXTRA_DURABLE=1 compile-time option is used then SQLite works
  harder to make transactions durable across power failures.
  The default behavior (SQLITE_EXTRA_DURABLE=0) is that the a committed transaction
  might rollback following recovery from a power loss.
  The default behavior is faster and since
  most applications prefer extra performance and are happy to
  endure an occasional rollback following a power-loss recovery.
  Applications for which transaction durability is more important can set
  this compile-time option for extra protection.
  <p>
  In the current implementation ([version 3.11.0]), this option only makes a
  difference when committing a transaction in [PRAGMA journal_mode=DELETE]
  and [PRAGMA synchronous=FULL].  In that scenario, the directory containing the
  rollback journal is synced after the rollback journal is deleted, to ensure
  that the rollback journal stays deleted after recovery from power loss.
  <p>
  Additional notes:
  <ol>
  <li><p>
  This option only affects transaction durability following power loss.
  The default configuration always ensures consistency, including after 
  a power loss, and ensures durable in the absence of a power loss, 
  regardless of this setting.
  <li><p>
  [PRAGMA synchronous=FULL] must be set at run time, in addition to this
  compile-time option, for power-loss durability.  
  FULL synchronous is the default when using a [rollback journal]
  but NORMAL synchronous is the default for [WAL mode].
  <li><p>
  Transactions are consistent and durable across power-loss with
  [PRAGMA journal_mode=TRUNCATE] and [PRAGMA journal_mode=PERSIST] and
  [PRAGMA journal_mode=WAL] even
  without this compile-time option, as long as [PRAGMA synchronous=FULL] is set.
  </ol>
}

COMPILE_OPTION {SQLITE_FTS3_MAX_EXPR_DEPTH=<i>N</i>} {
  This macro sets the maximum depth of the search tree that corresponds to
  the right-hand side of the MATCH operator in an [FTS3] or [FTS4] full-text
  index.  The full-text search uses a recursive algorithm, so the depth of
  the tree is limited to prevent using too much stack space.  The default







|
<
<
<
<
<
<
<
<
|
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
|
<
<
<
<
<
<
<
<
<







241
242
243
244
245
246
247
248








249















250









251
252
253
254
255
256
257
  the [SQLITE_LIMIT_WORKER_THREADS] parameter.  The [SQLITE_LIMIT_WORKER_THREADS]
  parameter sets the maximum number of auxiliary threads that a single
  [prepared statement] will launch to assist it with a query.  If not specified,
  the default maximum is 0.
  The value set here cannot be more than [SQLITE_MAX_WORKER_THREADS].
}

COMPILE_OPTION {SQLITE_EXTRA_DURABLE} {








  The SQLITE_EXTRA_DURABLE compile-time option causes the default















  [PRAGMA synchronous] setting to be EXTRA, rather than FULL.









}

COMPILE_OPTION {SQLITE_FTS3_MAX_EXPR_DEPTH=<i>N</i>} {
  This macro sets the maximum depth of the search tree that corresponds to
  the right-hand side of the MATCH operator in an [FTS3] or [FTS4] full-text
  index.  The full-text search uses a recursive algorithm, so the depth of
  the tree is limited to prevent using too much stack space.  The default
Changes to pages/pragma.in.
967
968
969
970
971
972
973
974
975
976
977











978
979
980
981
982
983



984
985
986
987
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
    function.
    </p>
}

Pragma synchronous {
    <p>^(<b>PRAGMA DB.synchronous;
        <br>PRAGMA DB.synchronous = </b>
          <i>0 | OFF | 1 | NORMAL | 2 | FULL</i><b>;</b></p>

    <p>Query or change the setting of the "synchronous" flag.)^
    ^The first (query) form will return the synchronous setting as an 











    integer.  ^When synchronous is FULL (2), the SQLite database engine will
    use the xSync method of the [VFS] to ensure that all content is safely
    written to the disk surface prior to continuing.
    This ensures that an operating system crash or power failure will
    not corrupt the database.
    FULL synchronous is very safe, but it is also slower. 



    ^When synchronous is NORMAL (1), the SQLite database
    engine will still sync at the most critical moments, but less often
    than in FULL mode.  There is a very small (though non-zero) chance that
    a power failure at just the wrong time could corrupt the database in
    NORMAL mode.  But in practice, you are more likely to suffer
    a catastrophic disk failure or some other unrecoverable hardware
    fault.


    ^With synchronous OFF (0), SQLite continues without syncing
    as soon as it has handed data off to the operating system.
    If the application running SQLite crashes, the data will be safe, but
    the database might become corrupted if the operating system
    crashes or the computer loses power before that data has been written
    to the disk surface.  On the other hand, some
    operations are as much as 50 or more times faster with synchronous OFF.

    </p>
 
    <p>^In [WAL] mode when synchronous is NORMAL (1), the WAL file is
    synchronized before each [checkpoint] and the database file is
    synchronized after each completed [checkpoint] and the WAL file
    header is synchronized when a WAL file begins to be reused after
    a checkpoint, but no sync operations occur during most transactions.
    ^With synchronous=FULL in WAL mode, an additional
    sync operation of the WAL file happens after each transaction commit.
    The extra WAL sync following each transaction help ensure that 
    transactions are durable across a power loss, but they do not aid
    in preserving consistency.
    If durability is not a concern, then synchronous=NORMAL is normally
    all one needs in WAL mode.</p>

    <p>^The default setting is synchronous=FULL, except in [WAL mode] when
    the default is synchronous=NORMAL.</p>



    <p>See also the [fullfsync] and [checkpoint_fullfsync] pragmas.</p>
}

Pragma temp_store {
    <p>^(<b>PRAGMA temp_store;
        <br>PRAGMA temp_store = </b>







|



>
>
>
>
>
>
>
>
>
>
>
|




|
>
>
>






|
>
>





|
|
>















|
|
>
>







967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
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
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
    function.
    </p>
}

Pragma synchronous {
    <p>^(<b>PRAGMA DB.synchronous;
        <br>PRAGMA DB.synchronous = </b>
          <i>0 | OFF | 1 | NORMAL | 2 | FULL | 3 | EXTRA</i><b>;</b></p>

    <p>Query or change the setting of the "synchronous" flag.)^
    ^The first (query) form will return the synchronous setting as an 
    integer.  The second form changes the synchronous setting.
    The meanings of the various synchronous settings are as follows:</p>
    <dl>
    <dt><b>EXTRA</b> (3)</dt>
    <dd>
    ^EXTRA synchronous is like FULL with the addition that the directory
    containing a [rollback journal] is synced after that journal is unlinked
    to commit a transaction in DELETE mode.  EXTRA provides additional
    durability if the commit is followed closely by a power loss.</dd>
    <dt><b>FULL</b> (2)</dt>
    <dd>
    ^When synchronous is FULL (2), the SQLite database engine will
    use the xSync method of the [VFS] to ensure that all content is safely
    written to the disk surface prior to continuing.
    This ensures that an operating system crash or power failure will
    not corrupt the database.
    FULL synchronous is very safe, but it is also slower.  FULL is the
    usual default setting when not in [WAL mode].</dd>
    <dt><b>NORMAL</b> (1)</dt>
    <dd>
    ^When synchronous is NORMAL (1), the SQLite database
    engine will still sync at the most critical moments, but less often
    than in FULL mode.  There is a very small (though non-zero) chance that
    a power failure at just the wrong time could corrupt the database in
    NORMAL mode.  But in practice, you are more likely to suffer
    a catastrophic disk failure or some other unrecoverable hardware
    fault.  NORMAL is the default when in [WAL mode].</dd>
    <dt><b>OFF</b> (0)</dt>
    <dd>
    ^With synchronous OFF (0), SQLite continues without syncing
    as soon as it has handed data off to the operating system.
    If the application running SQLite crashes, the data will be safe, but
    the database might become corrupted if the operating system
    crashes or the computer loses power before that data has been written
    to the disk surface.  On the other hand, commits can be orders of
    magnitude faster with synchronous OFF.
    </dd></dl>
    </p>
 
    <p>^In [WAL] mode when synchronous is NORMAL (1), the WAL file is
    synchronized before each [checkpoint] and the database file is
    synchronized after each completed [checkpoint] and the WAL file
    header is synchronized when a WAL file begins to be reused after
    a checkpoint, but no sync operations occur during most transactions.
    ^With synchronous=FULL in WAL mode, an additional
    sync operation of the WAL file happens after each transaction commit.
    The extra WAL sync following each transaction help ensure that 
    transactions are durable across a power loss, but they do not aid
    in preserving consistency.
    If durability is not a concern, then synchronous=NORMAL is normally
    all one needs in WAL mode.</p>

    <p>^The default setting is usually synchronous=FULL, 
    except in [WAL mode] when the default is synchronous=NORMAL.
    The [SQLITE_EXTRA_DURABLE] compile-time option changes the
    default to synchronous=EXTRA.</p>

    <p>See also the [fullfsync] and [checkpoint_fullfsync] pragmas.</p>
}

Pragma temp_store {
    <p>^(<b>PRAGMA temp_store;
        <br>PRAGMA temp_store = </b>