SQLite Forum

Timeline
Login

23 forum posts by user dmatson

2021-08-04
22:12 Reply: Switching pragma synchronous to upgrade durability (artifact: d1a6bc7c4c user: dmatson)

To note conclusions here (per offline conversations):

  1. In non-WAL mode, transactions are automatically durable with pragma synchronous set to NORMAL; nothing extra needs to be done for that case.
  2. For WAL mode (again, with pragma synchronous set to NORMAL), sqlite3_wal_checkpoint_v2 with SQLITE_CHECKPOINT_FULL or above is sufficient to ensure durability of previously-committed transactions cheaper options are also available. (SQLITE_CHECKPOINT_PASSIVE would not be sufficient as it may not get that far depending on other concurrent readers/writers.)
  3. A more efficient WAL-mode option is just to get the journal file to be flushed (i.e., on Windows, getting FlushFileBuffers to be called for the journal file); this approach will cause previously-committed transactions to become durable with less overhead. Further details follow.

To flush the journal file: Use sqlite3_file_control to get SQLITE_FCNTL_JOURNAL_POINTER and then call pMethods->xSync on the sqlite3_file* with SQLITE_SYNC_NORMAL. However, in order for the sqlite3_file* to be fully populated, the journal file must be fully opened. Preparing SQL statements such as "SELECT 1 FROM sqlite_schema" would work (no need to execute, just prepare), or without needing to work with a statement object, just calling sqlite3_table_column_metadata(connection, "main", "sqlite_schema", NULL, NULL, NULL, NULL, NULL, NULL) ensures the journal file is opened enough to flush its buffers (in WAL mode; in non-WAL mode that call is not sufficient to fully populate the journal sqlite3_file*, but for non-WAL mode durability is already achieved by synchronous NORMAL mode as noted above).

2021-07-15
04:01 Reply: Custom error messages for required table-valued function parameter (artifact: d4556ea17c user: dmatson)

I changed the code back to use SQLITE_ERROR normally and SQLITE_CONSTRAINT only for the "present but unusable" case. None of the saved queries I had triggered the original problem, and none of the complex queries I came up with did either. Perhaps I was mistaken, or can't remember the right query to trigger this case, or any bug in the query planner has since been fixed.

I'll leave the code in the new state and see if we encounter this problem again, but unless that happens, please consider this report resolved.

Thanks,

David

2021-07-14
00:13 Reply: Query shape to get plan with SeekGE on a composite key (artifact: 8111bc86fa user: dmatson)

Excellent - that's just what I was looking for.

Thanks, Richard!

David

sqlite> SELECT KeyPart1, hex(KeyPart2), KeyPart3, KeyPart4 FROM Test WHERE (KeyPart1,KeyPart2,KeyPart3,KeyPart4) > (1,x'abcdef',2,3) ORDER BY KeyPart1, KeyPart2, KeyPart3, KeyPart4 LIMIT 1;
1|ABCDEF|3|4
sqlite> EXPLAIN QUERY PLAN SELECT KeyPart1, hex(KeyPart2), KeyPart3, KeyPart4 FROM Test WHERE (KeyPart1,KeyPart2,KeyPart3,KeyPart4) > (1,x'abcdef',2,3) ORDER BY KeyPart1, KeyPart2, KeyPart3, KeyPart4 LIMIT 1;
QUERY PLAN
`--SEARCH Test USING PRIMARY KEY ((KeyPart1,KeyPart2,KeyPart3,KeyPart4)>(?,?,?,?))
sqlite> EXPLAIN SELECT KeyPart1, hex(KeyPart2), KeyPart3, KeyPart4 FROM Test WHERE (KeyPart1,KeyPart2,KeyPart3,KeyPart4) > (1,x'abcdef',2,3) ORDER BY KeyPart1, KeyPart2, KeyPart3, KeyPart4 LIMIT 1;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     39    0                    0   Start at 39
1     Noop           1     9     0                    0
2     Integer        1     1     0                    0   r[1]=1; LIMIT counter
3     OpenRead       2     2     0     k(4,,,,)       0   root=2 iDb=0; sqlite_autoindex_Test_1
4     Integer        1     2     0                    0   r[2]=1
5     Blob           3     3     0     ???             0   r[3]=??? (len=3)
6     Integer        2     4     0                    0   r[4]=2
7     Integer        3     5     0                    0   r[5]=3
8     IsNull         2     38    0                    0   if r[2]==NULL goto 38
9     SeekGE         2     38    2     4              0   key=r[2..5]
10      Integer        1     6     0                    0   r[6]=1
11      Column         2     0     7                    0   r[7]=Test.KeyPart1
12      Gt             8     29    7     BINARY-8       68  if r[7]>r[8] goto 29
13      ElseEq         0     16    0                    0
14      ZeroOrNull     7     6     8                    0   r[6] = 0 OR NULL
15      Goto           0     29    0                    0
16      Column         2     1     7                    0   r[7]=Test.KeyPart2
17      Gt             9     29    7     BINARY-8       65  if r[7]>r[9] goto 29
18      ElseEq         0     21    0                    0
19      ZeroOrNull     7     6     9                    0   r[6] = 0 OR NULL
20      Goto           0     29    0                    0
21      Column         2     2     7                    0   r[7]=Test.KeyPart3
22      Gt             10    29    7     BINARY-8       68  if r[7]>r[10] goto 29
23      ElseEq         0     26    0                    0
24      ZeroOrNull     7     6     10                   0   r[6] = 0 OR NULL
25      Goto           0     29    0                    0
26      Column         2     3     7                    0   r[7]=Test.KeyPart4
27      Gt             11    29    7     BINARY-8       68  if r[7]>r[11] goto 29
28      ZeroOrNull     7     6     11                   0   r[6] = 0 OR NULL
29      IfNot          6     37    1                    0
30      Column         2     0     12                   0   r[12]=Test.KeyPart1
31      Column         2     1     6                    0   r[6]=Test.KeyPart2
32      Function       0     6     13    hex(1)         0   r[13]=func(r[6])
33      Column         2     2     14                   0   r[14]=Test.KeyPart3
34      Column         2     3     15                   0   r[15]=Test.KeyPart4
35      ResultRow      12    4     0                    0   output=r[12..15]
36      DecrJumpZero   1     38    0                    0   if (--r[1])==0 goto 38
37    Next           2     10    0                    0
38    Halt           0     0     0                    0
39    Transaction    0     0     1     0              1   usesStmtJournal=0
40    Integer        1     8     0                    0   r[8]=1
41    Blob           3     9     0     ???             0   r[9]=??? (len=3)
42    Integer        2     10    0                    0   r[10]=2
43    Integer        3     11    0                    0   r[11]=3
44    Goto           0     1     0                    0
sqlite>
2021-07-13
23:04 Post: Query shape to get plan with SeekGE on a composite key (artifact: d3d85931ae user: dmatson)

I'm moving some data access logic from an ISAM-style DB to SQLite. The ISAM-style code did a single SeekGE on a composite key for a table that includes a BLOB column as part of the key. Is there a way to shape a SQL query such that the SQLite query planner will do a single SeekGE on the entire composite key? (In particular, in this case I just want to find the next key according to the table's primary key ordering, which in theory should be able to be done quite efficiently. Since this is a WITHOUT ROWID table, I'd hope it doesn't even need to touch the page with the actual row data and just use the b-tree pages with the primary key values.)

Here's the table, with some sample data:

sqlite> CREATE TABLE Test(KeyPart1 INTEGER, KeyPart2 BLOB, KeyPart3 INTEGER, KeyPart4 INTEGER, Value BLOB, PRIMARY KEY(KeyPart1, KeyPart2, KeyPart3, KeyPart4)) WITHOUT ROWID;
sqlite> INSERT INTO Test(KeyPart1, KeyPart2, KeyPart3, KeyPart4, Value) VALUES(1, x'abcdef', 2, 3, x'deadbeef');
sqlite> INSERT INTO Test(KeyPart1, KeyPart2, KeyPart3, KeyPart4, Value) VALUES(1, x'abcdef', 3, 4, x'baadc0de');
sqlite> INSERT INTO Test(KeyPart1, KeyPart2, KeyPart3, KeyPart4, Value) VALUES(1, x'abcdef0123', 0, 1, x'beadbead');
sqlite> INSERT INTO Test(KeyPart1, KeyPart2, KeyPart3, KeyPart4, Value) VALUES(2, x'012345', 2, 5, x'cadbed');
sqlite> INSERT INTO Test(KeyPart1, KeyPart2, KeyPart3, KeyPart4, Value) VALUES(3, x'01', 1, 6, x'beadcafe');

The query I started with gets a SeekGE only on the first column of the key. In the case where most values in the table have the same value for the first key, it has basically the same performance as a table scan (far worse than the old ISAM-style database in terms of CPU, File, and Disk I/O usage according to performance traces):

sqlite> SELECT KeyPart1, hex(KeyPart2), KeyPart3, KeyPart4 FROM Test WHERE KeyPart1 > 1 OR (KeyPart1 = 1 AND (KeyPart2 > x'abcdef' OR (KeyPart2 = x'abcdef' AND (KeyPart3 > 2 OR (KeyPart3 = 2 AND KeyPart4 > 3))))) ORDER BY KeyPart1, KeyPart2, KeyPart3, KeyPart4 LIMIT 1;
1|ABCDEF|3|4
sqlite> EXPLAIN QUERY PLAN SELECT KeyPart1, hex(KeyPart2), KeyPart3, KeyPart4 FROM Test WHERE KeyPart1 > 1 OR (KeyPart1 = 1 AND (KeyPart2 > x'abcdef' OR (KeyPart2 = x'abcdef' AND (KeyPart3 > 2 OR (KeyPart3 = 2 AND KeyPart4 > 3))))) ORDER BY KeyPart1, KeyPart2, KeyPart3, KeyPart4 LIMIT 1;
QUERY PLAN
`--SEARCH Test USING PRIMARY KEY (KeyPart1>?)
sqlite> EXPLAIN SELECT KeyPart1, hex(KeyPart2), KeyPart3, KeyPart4 FROM Test WHERE KeyPart1 > 1 OR (KeyPart1 = 1 AND (KeyPart2 > x'abcdef' OR (KeyPart2 = x'abcdef' AND (KeyPart3 > 2 OR (KeyPart3 = 2 AND KeyPart4 > 3))))) ORDER BY KeyPart1, KeyPart2, KeyPart3, KeyPart4 LIMIT 1;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     29    0                    0   Start at 29
1     Noop           1     9     0                    0
2     Integer        1     1     0                    0   r[1]=1; LIMIT counter
3     OpenRead       2     2     0     k(4,,,,)       0   root=2 iDb=0; sqlite_autoindex_Test_1
4     Integer        1     2     0                    0   r[2]=1
5     SeekGE         2     28    2     1              0   key=r[2]
6       Column         2     0     3                    0   r[3]=Test.KeyPart1
7       Gt             4     20    3     BINARY-8       68  if r[3]>r[4] goto 20
8       Column         2     0     3                    0   r[3]=Test.KeyPart1
9       Ne             4     27    3     BINARY-8       84  if r[3]!=r[4] goto 27
10      Column         2     1     3                    0   r[3]=Test.KeyPart2
11      Gt             5     20    3     BINARY-8       65  if r[3]>r[5] goto 20
12      Column         2     1     3                    0   r[3]=Test.KeyPart2
13      Ne             5     27    3     BINARY-8       81  if r[3]!=r[5] goto 27
14      Column         2     2     3                    0   r[3]=Test.KeyPart3
15      Gt             6     20    3     BINARY-8       68  if r[3]>r[6] goto 20
16      Column         2     2     3                    0   r[3]=Test.KeyPart3
17      Ne             6     27    3     BINARY-8       84  if r[3]!=r[6] goto 27
18      Column         2     3     3                    0   r[3]=Test.KeyPart4
19      Le             7     27    3     BINARY-8       84  if r[3]<=r[7] goto 27
20      Column         2     0     8                    0   r[8]=Test.KeyPart1
21      Column         2     1     3                    0   r[3]=Test.KeyPart2
22      Function       0     3     9     hex(1)         0   r[9]=func(r[3])
23      Column         2     2     10                   0   r[10]=Test.KeyPart3
24      Column         2     3     11                   0   r[11]=Test.KeyPart4
25      ResultRow      8     4     0                    0   output=r[8..11]
26      DecrJumpZero   1     28    0                    0   if (--r[1])==0 goto 28
27    Next           2     6     0                    0
28    Halt           0     0     0                    0
29    Transaction    0     0     1     0              1   usesStmtJournal=0
30    Integer        1     4     0                    0   r[4]=1
31    Blob           3     5     0     ???             0   r[5]=??? (len=3)
32    Integer        2     6     0                    0   r[6]=2
33    Integer        3     7     0                    0   r[7]=3
34    Goto           0     1     0                    0

With a more flattened query shape, it's probably better but still not a single SeekGE and uses a temp b-tree:

sqlite> SELECT KeyPart1, hex(KeyPart2), KeyPart3, KeyPart4 FROM Test WHERE (KeyPart1 = 1 AND KeyPart2 = x'abcdef' AND KeyPart3 = 2 AND KeyPart4 > 3) OR (KeyPart1 = 1 AND KeyPart2 = x'abcdef' AND KeyPart3 > 2) OR (KeyPart1 = 1 AND KeyPart2 > x'abcdef') OR (KeyPart1 > 1) ORDER BY KeyPart1, KeyPart2, KeyPart3, KeyPart4 LIMIT 1;
1|ABCDEF|3|4
sqlite> EXPLAIN QUERY PLAN SELECT KeyPart1, hex(KeyPart2), KeyPart3, KeyPart4 FROM Test WHERE (KeyPart1 = 1 AND KeyPart2 = x'abcdef' AND KeyPart3 = 2 AND KeyPart4 > 3) OR (KeyPart1 = 1 AND KeyPart2 = x'abcdef' AND KeyPart3 > 2) OR (KeyPart1 = 1 AND KeyPart2 > x'abcdef') OR (KeyPart1 > 1) ORDER BY KeyPart1, KeyPart2, KeyPart3, KeyPart4 LIMIT 1;
QUERY PLAN
|--MULTI-INDEX OR
|  |--INDEX 1
|  |  `--SEARCH Test USING PRIMARY KEY (KeyPart1=? AND KeyPart2=? AND KeyPart3=? AND KeyPart4>?)
|  |--INDEX 2
|  |  `--SEARCH Test USING PRIMARY KEY (KeyPart1=? AND KeyPart2=? AND KeyPart3>?)
|  |--INDEX 3
|  |  `--SEARCH Test USING PRIMARY KEY (KeyPart1=? AND KeyPart2>?)
|  `--INDEX 4
|     `--SEARCH Test USING PRIMARY KEY (KeyPart1>?)
`--USE TEMP B-TREE FOR ORDER BY
sqlite> EXPLAIN SELECT KeyPart1, hex(KeyPart2), KeyPart3, KeyPart4 FROM Test WHERE (KeyPart1 = 1 AND KeyPart2 = x'abcdef' AND KeyPart3 = 2 AND KeyPart4 > 3) OR (KeyPart1 = 1 AND KeyPart2 = x'abcdef' AND KeyPart3 > 2) OR (KeyPart1 = 1 AND KeyPart2 > x'abcdef') OR (KeyPart1 > 1) ORDER BY KeyPart1, KeyPart2, KeyPart3, KeyPart4 LIMIT 1;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     79    0                    0   Start at 79
1     OpenEphemeral  1     9     0     k(4,B,B,B,B)   0   nColumn=9
2     Integer        1     1     0                    0   r[1]=1; LIMIT counter
3     OpenRead       0     2     0     k(4,,,,)       0   root=2 iDb=0; Test
4     OpenEphemeral  3     4     0     k(4,,,,)       0   nColumn=4
5     Integer        56    2     0                    0   r[2]=56
6     Integer        1     4     0                    0   r[4]=1
7     Blob           3     5     0     ???             0   r[5]=??? (len=3)
8     Integer        2     6     0                    0   r[6]=2
9     Integer        3     7     0                    0   r[7]=3
10    SeekGT         0     20    4     4              0   key=r[4..7]
11      IdxGT          0     20    4     3              0   key=r[4..6]
12      Column         0     0     8                    0   r[8]=Test.KeyPart1
13      Column         0     1     9                    0   r[9]=Test.KeyPart2
14      Column         0     2     10                   0   r[10]=Test.KeyPart3
15      Column         0     3     11                   0   r[11]=Test.KeyPart4
16      MakeRecord     8     4     3                    0   r[3]=mkrec(r[8..11])
17      IdxInsert      3     3     8     4              0   key=r[3]
18      Gosub          2     57    0                    0
19    Next           0     11    0                    0
20    Integer        1     12    0                    0   r[12]=1
21    Blob           3     13    0     ???             0   r[13]=??? (len=3)
22    Integer        2     14    0                    0   r[14]=2
23    SeekGT         0     34    12    3              0   key=r[12..14]
24      IdxGT          0     34    12    2              0   key=r[12..13]
25      Column         0     0     8                    0   r[8]=Test.KeyPart1
26      Column         0     1     9                    0   r[9]=Test.KeyPart2
27      Column         0     2     10                   0   r[10]=Test.KeyPart3
28      Column         0     3     11                   0   r[11]=Test.KeyPart4
29      Found          3     33    8     4              0   key=r[8..11]
30      MakeRecord     8     4     3                    0   r[3]=mkrec(r[8..11])
31      IdxInsert      3     3     8     4              16  key=r[3]
32      Gosub          2     57    0                    0
33    Next           0     24    0                    0
34    Integer        1     15    0                    0   r[15]=1
35    Blob           3     16    0     ???             0   r[16]=??? (len=3)
36    SeekGT         0     47    15    2              0   key=r[15..16]
37      IdxGT          0     47    15    1              0   key=r[15]
38      Column         0     0     8                    0   r[8]=Test.KeyPart1
39      Column         0     1     9                    0   r[9]=Test.KeyPart2
40      Column         0     2     10                   0   r[10]=Test.KeyPart3
41      Column         0     3     11                   0   r[11]=Test.KeyPart4
42      Found          3     46    8     4              0   key=r[8..11]
43      MakeRecord     8     4     3                    0   r[3]=mkrec(r[8..11])
44      IdxInsert      3     3     8     4              16  key=r[3]
45      Gosub          2     57    0                    0
46    Next           0     37    0                    0
47    Integer        1     17    0                    0   r[17]=1
48    SeekGT         0     56    17    1              0   key=r[17]
49      Column         0     0     8                    0   r[8]=Test.KeyPart1
50      Column         0     1     9                    0   r[9]=Test.KeyPart2
51      Column         0     2     10                   0   r[10]=Test.KeyPart3
52      Column         0     3     11                   0   r[11]=Test.KeyPart4
53      Found          3     55    8     4              0   key=r[8..11]
54      Gosub          2     57    0                    0
55    Next           0     49    0                    0
56    Goto           0     71    0                    0
57    Column         0     0     18                   0   r[18]=Test.KeyPart1
58    Column         0     1     19                   0   r[19]=Test.KeyPart2
59    Column         0     2     20                   0   r[20]=Test.KeyPart3
60    Column         0     3     21                   0   r[21]=Test.KeyPart4
61    Sequence       1     22    0                    0   r[22]=cursor[1].ctr++
62    IfNotZero      1     66    0                    0   if r[1]!=0 then r[1]--, goto 66
63    Last           1     0     0                    0
64    IdxLE          1     70    18    4              0   key=r[18..21]
65    Delete         1     0     0                    0
66    Column         0     1     28                   0   r[28]=Test.KeyPart2
67    Function       0     28    23    hex(1)         0   r[23]=func(r[28])
68    MakeRecord     18    6     27                   0   r[27]=mkrec(r[18..23])
69    IdxInsert      1     27    18    6              0   key=r[27]
70    Return         2     0     0                    0
71    Sort           1     78    0                    0
72      Column         1     3     26                   0   r[26]=KeyPart4
73      Column         1     2     25                   0   r[25]=KeyPart3
74      Column         1     5     24                   0   r[24]=hex(KeyPart2)
75      Column         1     0     23                   0   r[23]=KeyPart1
76      ResultRow      23    4     0                    0   output=r[23..26]
77    Next           1     72    0                    0
78    Halt           0     0     0                    0
79    Transaction    0     0     1     0              1   usesStmtJournal=0
80    Goto           0     1     0                    0

Is there a different query shape I can use that makes it clearer I have the values to make up a whole composite key that I want to use for a Seek* operation?

Thanks,

David

2021-07-09
21:27 Reply: How to handle collation version changes (artifact: 4a1aa91998 user: dmatson)

I think it also means that it's OK for the collation to change completely - for example, it could even be backwards or otherwise entirely different from before... And for the REINDEX command, the old version of the collation is not needed/completely irrelevant - it just needs to have the new version loaded. Is that understanding all correct?

I confirmed this understanding offline.

Also, as Bill mentioned, a collation change could mean that two things that used to be distinct are now the same, no any UNIQUE constraint on a column with a collation could be violated by REINDEX, causing the REINDEX to fail.

So I think the only two points to be aware of here are:

  1. When changing the implementation of a collation, call REINDEX right after loading the new collation and before touching any data impacted by the new collation.
  2. Be aware that REINDEX with a UNIQUE constraint may fail if the collation change results in uniqueness changes.
20:34 Reply: Linkage #defines are unused (artifact: a7b4865a2e user: dmatson)

Larry,

Yes, if the amalgamation could omit (or mark as reserved) any macros it doesn't happen to use, that would be excellent. If they're still present in some form, having the amalgamation indicate which are actually used and which aren't would be nice. Any documentation or comments on how to use these macros correctly would also be appreciated.

After looking at the --apicall generation option Keith mentioned above, the only one left that I couldn't find used at all was SQLITE_STDCALL. (Specifically, it didn't show up in a search on the GitHub mirror repo, but that may not have found all cases.) But if it's omitted from the amalgamation, it won't make much difference to me whether SQLITE_STDCALL is still in the repo internally for some other case.

Thanks,

David

17:58 Edit reply: Support for blocking VFS (artifact: 00c23004b1 user: dmatson)

I had seen some related things in the source. It looks like that is POSIX-only - is that correct? I'm specifically looking for Windows support.

17:57 Reply: Switching pragma synchronous to upgrade durability (artifact: c88c95437a user: dmatson)

For example, would it need to be

BEGIN IMMEDIATE TRANSACTION;

instead?

We also have some places that do sqlite3_wal_checkpoint_v2 with SQLITE_CHECKPOINT_TRUNCATE - does that already achieve this effect, regardless of the pragma synchronous mode? The docs talk about syncing the database file - I'd guess that any checkpoint mode of SQLITE_CHECKPOINT_FULL or higher also means doing a sync on the WAL file, though it would be great to confirm.

Thanks,

David

01:53 Reply: Linkage #defines are unused (artifact: 991ce6ab0d user: dmatson)

Thanks, Larry.

In practice, when we compile winsqlite3.dll, we set all of these defines, even though it looks like they end up doing nothing - I think we looked at that comment and took everything it listed as being part of the interface. But we also have other copies of SQLite, which eventually should be merged, and the linkage is among the biggest difference. Understanding what actually matters vs. what doesn't can really help (especially when it's in the middle of a very complex system). We also have many other places where we list both SQLITE_API and SQLITE_STDCALL on the function signature of alternately-compiled stubs - cleaning this all up would be easier if it's clearer what these defines are and do (and if there are fewer to manage).

It would simplify things enough for me that I'd be willing to offer contributing a patch to clean this up, if that would help. I'm not sure if you have a contribution process though, or in this case if it would take less work on your side to do it without my involvement. Let me know what you think.

Thanks,

David

2021-07-08
23:50 Reply: Linkage #defines are unused (artifact: d0960cc580 user: dmatson)

Thanks, Keith!

That makes sense. Could/should the following defines be omitted from the amalgamation output when --apicall is not passed to mksqlite3c.tcl?

  • SQLITE_CALLBACK
  • SQLITE_SYSAPI
  • SQLITE_APICALL
  • SQLITE_STDCALL

Otherwise, would the comment at the top saying that these defines can be used to override linkage be technically incorrect, at least with respect to the final amalgamation file produced?

For SQLITE_STDCALL specifically, as far as I can tell, it looks like that one is no longer in use - compared to where it used to be in the commit where it was first added, its use has now apparently been replaced entirely by SQLITE_APICALL. Is SQLITE_STDCALL vestigial now?

Thanks,

David

23:06 Reply: How to handle collation version changes (artifact: 6d72c43947 user: dmatson)

Thanks, Keith! REINDEX sounds like exactly what I'm looking for here.

Just to confirm a couple of details:

There is a command to test the integrity of an index:

PRAGMA INTEGRITY_CHECK;

Compared to PRAGMA integrity_check, does PRAGMA quick_check skip checking the validity of the sort order per the collation? (Is skipping this step one of the things that makes it "quick"?)

They have to hold for the duration of the existence of the index which uses that collation.

I assume "index" here also includes "table" (if using a collation directly); is that correct?

A collation can change at any time that it is not in use.

...

Yes, there is a command for that.

REINDEX

I think this means that it's OK for a collation to change if and only if the very first thing done with the new collation is a REINDEX command - the data affected by this collation must not be queried or modified with the new collation before that command completes. I think it also means that it's OK for the collation to change completely - for example, it could even be backwards or otherwise entirely different from before - as long as the very first thing done with the DB file after loading the new collation is to issue the REINDEX command. And for the REINDEX command, the old version of the collation is not needed/completely irrelevant - it just needs to have the new version loaded. Is that understanding all correct?

Thanks,

David

22:48 Reply: Custom error messages for required table-valued function parameter (artifact: 9b7d4456ce user: dmatson)

I double-checked back in my repo's history and confirmed I used to have exactly that behavior (SQLITE_CONSTRAINT when present but unusable; SQLITE_ERROR otherwise), but I had to change it for some complex query cases - returning SQLITE_ERROR caused those queries to fail. I think it had to do with left joins when this function might have no input available or something like that.

If returning SQLITE_ERROR for a missing required parameter is expected to work in all cases, let me know, and I can repro the problem I was having with that approach.

Thanks,

David

20:53 Reply: Switching pragma synchronous to upgrade durability (artifact: 9fefdc5daf user: dmatson)

Excellent. Thanks for the help.

And just to clarify, any transaction will work, even if it is "empty"? For example, is it good enough just to do:

pragma synchronous=FULL;
BEGIN TRANSACTION;
COMMIT;

Or would I need to do something that forces the transaction to acquire a write lock or something like that?

Thanks,

David

03:08 Post: How to handle collation version changes (artifact: d0658c2246 user: dmatson)

According to the collation documentation, collating functions must obey some important constraints for SQLite to work correctly.

The collating function must obey the following properties for all strings A, B, and C:

If A==B then B==A.
If A==B and B==C then A==C.
If A<B THEN B>A.
If A<B and B<C then A<C.
If a collating function fails any of the above constraints and that collating function is registered and used, then the behavior of SQLite is undefined.

Do these properties have to hold for the life of the persisted .db file, or only during the process's lifetime?

Specifically, what if a collation needs to release a new version? For example, suppose Unicode defines a new code point, and the collation needs to sort it in the new-correct position for that now-defined code point. Is there way the collation can go through an upgrade process that is guaranteed not to break anything? If some users had started using the code point immediately after it was defined by the Unicode folks but before the collation upgrade was published, might the database file become unusable when the collation is upgraded?

Or, to be more concrete, suppose a collation uses Windows APIs such as CompareStringEx, which very occasionally changes (for example, on some Windows major version upgrades; whenever GetNLSVersionEx changes). Related documentation suggests re-indexing database strings to handle any changes in the new localization version. Is there a good way to do that with SQLite? If it's just an index, I suspect dropping and re-creating the index would work, but what if it's part of the table definition, such as:

CREATE TABLE UsesWindowsLocale(Name TEXT PRIMARY KEY COLLATE Unicode_Windows_en_US);

Where the collation does something like:

return CompareStringEx(
        L"en-US",
        0,
        (PCWCH)left,
        left_size / sizeof(wchar_t),
        (PCWCH)right,
        right_size / sizeof(wchar_t),
        NULL,
        NULL,
        0) - 2;

Is there a way to handle a Windows version upgrade that includes locale changes without dropping and re-creating the table?

If not, could/should there be? Perhaps some mechanism could allow telling "you had a valid collation before, and you have a new one now; please re-index" or even "here's the old and here's the new; please update the internal b-tree sorting accordingly."

Thanks,

David

02:40 Edit reply: Linkage #defines are unused (artifact: 464c347919 user: dmatson)

Also, to clarify, the statements above were made within the scope of the sqlite3.c file, where these lines were listed. Unless sqlite3.c is #included into another compilation unit (which seems unusual, though not impossible), I believe these defines in this file will have no effect, since they will only impact the compilation of this .c file (which doesn't use them). (And, even for the #include scenario, it seems like they have minimal usefulness, as the outer file can #define and then use whatever names it desires.)

There may be other reasons to have them, but within the sqlite3.c file itself many of them appear to be unnecessary, perhaps vestigial.

02:36 Reply: Linkage #defines are unused (artifact: 264f9510e1 user: dmatson)

Also, to clarify, the statement above were made within the scope of the sqlite3.c file, where these lines were listed. Unless sqlite3.c is #included into another compilation unit (which seems unusual, though not impossible), I believe these defines in this file will have no effect, since they will only impact the compilation of this .c file (which doesn't use them). (And, even for the #include scenario, it seems like they have minimal usefulness, as the outer file can #define and then use whatever names it desires.)

There may be other reasons to have them, but within the sqlite3.c file itself many of them appear to be unnecessary, perhaps vestigial.

01:52 Reply: Linkage #defines are unused (artifact: afc6910a78 user: dmatson)

Could you clarify how #defines not used by sqlite3.c help in those cases? For example, the only reference inside sqlite3.c to SQLITE_APICALL or SQLITE_SYSAPI are the lines pasted above. How does having these values #defined to empty help with client applications? (If they are unused by sqlite3.c, couldn't client applications create and use their own defines?)

Thanks,

David

01:17 Post: Linkage #defines are unused (artifact: 88857ecad4 user: dmatson)

The amalgamation sqlite3.c has:

/*
** Provide the ability to override linkage features of the interface.
*/
#ifndef SQLITE_EXTERN
# define SQLITE_EXTERN extern
#endif
#ifndef SQLITE_API
# define SQLITE_API
#endif
#ifndef SQLITE_CDECL
# define SQLITE_CDECL
#endif
#ifndef SQLITE_APICALL
# define SQLITE_APICALL
#endif
#ifndef SQLITE_STDCALL
# define SQLITE_STDCALL SQLITE_APICALL
#endif
#ifndef SQLITE_CALLBACK
# define SQLITE_CALLBACK
#endif
#ifndef SQLITE_SYSAPI
# define SQLITE_SYSAPI
#endif

But most of these #defines are either unused or nearly unused. SQLITE_API is used extensively, but SQLITE_CDECL is only used 3 times, SQLITE_EXTERN is only used once, and SQLITE_APICALL, SQLITE_STDCALL, SQLITE_CALLBACK and SQLITE_SYSAPI are never used.

Should these #defines be used? For example, should the last three parameters to sqlite3_create_function be marked as SQLITE_CALLBACK? (Or, if not, should these #defines be removed from the amalgamation?)

Thanks,

David

01:08 Post: Error message from table-valued function xConnect is ignored (artifact: 2a4f835af8 user: dmatson)

When developing a table-valued function, I've tried to provide a custom error from xConnect when the table can't be created for some reason (such as due to a programmer syntax error in the SQL passed to sqlite3_declare_vtab). xConnect has an explicit char** pzErr parameter, but it is ignored in this case.

Specifically, vtabCallConstructor copies this error message and returns it up to sqlite3VtabEponymousTableInit, which again copies in correctly. But when it returns to sqlite3LocateTable, that function overwrites the custom error message with "no such table":

  if( p==0 ){
    const char *zMsg = flags & LOCATE_VIEW ? "no such view" : "no such table";
    if( zDbase ){
      sqlite3ErrorMsg(pParse, "%s: %s.%s", zMsg, zDbase, zName);
    }else{
      sqlite3ErrorMsg(pParse, "%s: %s", zMsg, zName);
    }
...

Could SQLite preserve the error message in this case?

Below is source with a repro.

Thanks,

David

#include <assert.h>
#include <stdio.h>
#include <string.h>
#include "sqlite3.h"

static int testVTabConnect(sqlite3* connection, void* pAux, int argc, const char* const* argv, sqlite3_vtab** ppVtab,
    char** pzErr)
{
    (void)pAux;
    (void)argc;
    (void)argv;

    sqlite3_vtab* table = (sqlite3_vtab*)sqlite3_malloc(sizeof(*table));

    if (table == NULL)
    {
        return SQLITE_NOMEM;
    }

    memset(table, 0, sizeof(*table));

    int rc = sqlite3_declare_vtab(connection, "CREATE TABLE x(Value INTEGER PRIMARY KEY) BAD SYNTAX HERE;");

    if (rc != SQLITE_OK)
    {
        sqlite3_free(table);

        const char* connectionError = sqlite3_errmsg(connection);
        size_t errorLength = strlen(connectionError);
        char* errorMessageCopy = (char*)sqlite3_malloc64((sqlite3_uint64)errorLength + 1);

        if (errorMessageCopy == NULL)
        {
            return SQLITE_NOMEM;
        }

        strncpy_s(errorMessageCopy, errorLength + 1, connectionError, errorLength);
        *pzErr = errorMessageCopy;
        return rc;
    }

    *ppVtab = table;
    return SQLITE_OK;
}

static int testVTabDisconnect(sqlite3_vtab* pVtab)
{
    sqlite3_free(pVtab);
    return SQLITE_OK;
}

static int testVTabBestIndex(sqlite3_vtab* tab, sqlite3_index_info* pIdxInfo)
{
    (void)tab;

    pIdxInfo->idxNum = 0;
    pIdxInfo->idxStr = NULL;

    pIdxInfo->estimatedCost = 1;
    pIdxInfo->estimatedRows = 1;

    return SQLITE_OK;
}

struct test_vtab_cursor
{
    sqlite3_vtab_cursor base; // This base struct must come first.
    sqlite_int64 rowid;
};

typedef struct test_vtab_cursor test_vtab_cursor;

static int testVTabOpen(sqlite3_vtab* p, sqlite3_vtab_cursor** ppCursor)
{
    (void)p;

    test_vtab_cursor* cursor = (test_vtab_cursor*)sqlite3_malloc(sizeof(*cursor));

    if (cursor == NULL)
    {
        return SQLITE_NOMEM;
    }

    memset(cursor, 0, sizeof(*cursor));

    *ppCursor = &cursor->base;

    return SQLITE_OK;
}

static int testVTabClose(sqlite3_vtab_cursor* cur)
{
    test_vtab_cursor* cursor = (test_vtab_cursor*)cur;
    sqlite3_free(cursor);
    return SQLITE_OK;
}

static int testVTabFilter(sqlite3_vtab_cursor* pVtabCursor, int idxNum, const char* idxStr, int argc,
    sqlite3_value** argv)
{
    assert(idxNum == 0);
    (void)idxNum;
    assert(idxStr == NULL);
    (void)idxStr;
    assert(argc == 0);
    (void)argc;
    (void)argv;

    test_vtab_cursor* cursor = (test_vtab_cursor*)pVtabCursor;
    cursor->rowid = 0;

    return SQLITE_OK;
}

static int testVTabNext(sqlite3_vtab_cursor* cur)
{
    test_vtab_cursor* cursor = (test_vtab_cursor*)cur;
    ++(cursor->rowid);
    return SQLITE_OK;
}

static int testVTabEof(sqlite3_vtab_cursor* cur)
{
    test_vtab_cursor* cursor = (test_vtab_cursor*)cur;
    return cursor->rowid < 1 ? 0 : 1;
}

static int testVTabColumn(sqlite3_vtab_cursor* cur, sqlite3_context* ctx, int i)
{
    assert(i == 0);
    (void)i;

    test_vtab_cursor* cursor = (test_vtab_cursor*)cur;
    sqlite3_result_int64(ctx, cursor->rowid);
    return SQLITE_OK;
}

static int testVTabRowid(sqlite3_vtab_cursor* cur, sqlite_int64* pRowid)
{
    test_vtab_cursor* cursor = (test_vtab_cursor*)cur;
    *pRowid = cursor->rowid;
    return SQLITE_OK;
}

static sqlite3_module testVTabModule = {
  3, // iVersion
  NULL, // xCreate
  testVTabConnect, // xConnect
  testVTabBestIndex, // xBestIndex
  testVTabDisconnect, //xDisconnect
  NULL, // xDestroy
  testVTabOpen, //int xOpen
  testVTabClose, //int xClose
  testVTabFilter, //int xFilter
  testVTabNext, // int xNext
  testVTabEof, // int xEof
  testVTabColumn, // xColumn
  testVTabRowid, // xRowid
  NULL, // xUpdate
  NULL, // xBegin
  NULL, // xSync
  NULL, // xCommit
  NULL, // xRollback
  NULL, // xFindFunction
  NULL, // xRename
  NULL, // xSavepoint
  NULL, // xRelease
  NULL, // xRollbackTo
  NULL, // xShadowName
};

int main()
{
    int rc = 0;
    int rcCleanup = 0;
    int rcCurrentCleanup = 0;
    sqlite3* db = NULL;
    sqlite3_stmt* stmt = NULL;

    rc = sqlite3_open("D:\\test.db", &db);

    if (rc)
    {
        goto cleanup;
    }

    rc = sqlite3_create_module_v2(db, "testvtab", &testVTabModule, NULL, NULL);

    if (rc)
    {
        goto cleanup;
    }

    rc = sqlite3_prepare_v2(db, "SELECT count(*) FROM testvtab();", -1, &stmt, NULL);

    if (rc)
    {
        goto cleanup;
    }

    rc = sqlite3_step(stmt);

    if (rc != SQLITE_ROW)
    {
        goto cleanup;
    }

    printf("%i\n", sqlite3_column_int(stmt, 0));

    rc = sqlite3_step(stmt);

    if (rc != SQLITE_DONE)
    {
        goto cleanup;
    }

    rc = SQLITE_OK;

cleanup:
    if (rc && db)
    {
        fprintf(stderr, "%s\n", sqlite3_errmsg(db));
    }

    if (stmt)
    {
        rcCurrentCleanup = sqlite3_finalize(stmt);

        if (rcCurrentCleanup && !rcCleanup)
        {
            rcCleanup = rcCurrentCleanup;
        }
    }

    if (db)
    {
        rcCurrentCleanup = sqlite3_close_v2(db);

        if (rcCurrentCleanup && !rcCleanup)
        {
            rcCleanup = rcCurrentCleanup;
        }
    }

    if (rcCleanup)
    {
        return rcCleanup;
    }

    return rc;
}
00:49 Post: Custom error messages for required table-valued function parameter (artifact: ba585bdac3 user: dmatson)

I have a number of table-valued functions that require one or more parameters, and I'd like to give a helpful error message if a required parameter is not specified. I've tried setting sqlite3_vtab*'s zErrMsg, but it's ignored when returning SQLITE_CONSTRAINT, which is what the documentation says should be returned for this case. (If I return anything else, it makes the query planner unhappy with at least some queries involving this function.)

Specifically, the docs say:

The SQLITE_CONSTRAINT return is useful for table-valued functions that have required parameters. If the aConstraint[].usable field is false for one of the required parameter, then the xBestIndex method should return SQLITE_CONSTRAINT.

(from section 2.3.3)

Below is sample source that reproduces the problem. It shows a nice error message when the parameter passed is wrong, but only a generic "no query solution" when the required parameter is missing. (Near the end, change "SELECT count() FROM testvtab(NULL);" to "SELECT count() FROM testvtab();" to see the case where a custom error message cannot be provided back to the application.)

Could SQLite add support for setting the error message when all solutions fail with SQLITE_CONSTRAINT? I recognize there are multiple possible invocations to xBestIndex in this case, and it would have to choose one of them (say, the first or the last that failed with SQLITE_CONSTRAINT), but which one wouldn't matter for my purposes.

Thanks,

David

#include <assert.h>
#include <stdbool.h>
#include <stdio.h>
#include <string.h>
#include "sqlite3.h"

enum test_vtab_column
{
    test_vtab_column_value = 0,
    test_vtab_column_source
};

static int testVTabConnect(sqlite3* connection, void* pAux, int argc, const char* const* argv, sqlite3_vtab** ppVtab,
    char** pzErr)
{
    (void)pAux;
    (void)argc;
    (void)argv;

    sqlite3_vtab* table = (sqlite3_vtab*)sqlite3_malloc(sizeof(*table));

    if (table == NULL)
    {
        return SQLITE_NOMEM;
    }

    memset(table, 0, sizeof(*table));

    int rc = sqlite3_declare_vtab(connection, "CREATE TABLE x("
        "Value INTEGER PRIMARY KEY, "
        "Source HIDDEN INTEGER NOT NULL);");

    if (rc != SQLITE_OK)
    {
        sqlite3_free(table);

        const char* connectionError = sqlite3_errmsg(connection);
        size_t errorLength = strlen(connectionError);
        char* errorMessageCopy = (char*)sqlite3_malloc64((sqlite3_uint64)errorLength + 1);

        if (errorMessageCopy == NULL)
        {
            return SQLITE_NOMEM;
        }

        strncpy_s(errorMessageCopy, errorLength + 1, connectionError, errorLength);
        *pzErr = errorMessageCopy;
        return rc;
    }

    *ppVtab = table;
    return SQLITE_OK;
}

static int testVTabDisconnect(sqlite3_vtab* pVtab)
{
    sqlite3_free(pVtab);
    return SQLITE_OK;
}

static bool try_set_error_message(sqlite3_vtab* table, const char* message)
{
    size_t length = strlen(message);
    char* copy = (char*)sqlite3_malloc64((sqlite3_uint64)length + 1);

    if (copy == NULL)
    {
        return false;
    }

    strncpy_s(copy, length + 1, message, length);

    sqlite3_free(table->zErrMsg);
    table->zErrMsg = copy;
    return true;
}

static int testVTabBestIndex(sqlite3_vtab* tab, sqlite3_index_info* pIdxInfo)
{
    int sourceDataConstraintIndex = -1;

    for (int index = 0; index < pIdxInfo->nConstraint; ++index)
    {
        struct sqlite3_index_constraint* constraint = &pIdxInfo->aConstraint[index];

        if (!constraint->usable)
        {
            continue;
        }

        if (constraint->iColumn == test_vtab_column_source && constraint->op == SQLITE_INDEX_CONSTRAINT_EQ)
        {
            sourceDataConstraintIndex = index;
        }
    }

    if (sourceDataConstraintIndex == -1)
    {
        if (!try_set_error_message(tab, "testvtab requires a Source value"))
        {
            return SQLITE_NOMEM;
        }

        return SQLITE_CONSTRAINT;
    }

    if (pIdxInfo->nOrderBy == 1 && pIdxInfo->aOrderBy[0].iColumn == test_vtab_column_value &&
        pIdxInfo->aOrderBy[0].desc == 0)
    {
        pIdxInfo->orderByConsumed = 1;
    }

    pIdxInfo->idxNum = 0;
    pIdxInfo->idxStr = NULL;

    pIdxInfo->aConstraintUsage[sourceDataConstraintIndex].argvIndex = 1;
    pIdxInfo->aConstraintUsage[sourceDataConstraintIndex].omit = 1;

    pIdxInfo->estimatedCost = 1;

    return SQLITE_OK;
}

struct test_vtab_cursor
{
    sqlite3_vtab_cursor base; // This base struct must come first.
    sqlite_int64 rowid;
    sqlite_int64 source;
};

typedef struct test_vtab_cursor test_vtab_cursor;

static int testVTabOpen(sqlite3_vtab* p, sqlite3_vtab_cursor** ppCursor)
{
    (void)p;

    test_vtab_cursor* cursor = (test_vtab_cursor*)sqlite3_malloc(sizeof(*cursor));

    if (cursor == NULL)
    {
        return SQLITE_NOMEM;
    }

    memset(cursor, 0, sizeof(*cursor));

    *ppCursor = &cursor->base;

    return SQLITE_OK;
}

static int testVTabClose(sqlite3_vtab_cursor* cur)
{
    test_vtab_cursor* cursor = (test_vtab_cursor*)cur;
    sqlite3_free(cursor);
    return SQLITE_OK;
}

static int testVTabFilter(sqlite3_vtab_cursor* pVtabCursor, int idxNum, const char* idxStr, int argc,
    sqlite3_value** argv)
{
    assert(idxNum == 0);
    (void)idxNum;
    assert(idxStr == NULL);
    (void)idxStr;
    assert(argc == 1);
    (void)argc;

    test_vtab_cursor* cursor = (test_vtab_cursor*)pVtabCursor;

    sqlite3_vtab* table = cursor->base.pVtab;
    sqlite3_value* arg = argv[0];

    if (sqlite3_value_type(arg) != SQLITE_INTEGER)
    {
        return try_set_error_message(table, "testvtab requires a Source value that is an INTEGER") ?
            SQLITE_ERROR : SQLITE_NOMEM;
    }

    cursor->rowid = 0;
    cursor->source = sqlite3_value_int64(arg);

    return SQLITE_OK;
}

static int testVTabNext(sqlite3_vtab_cursor* cur)
{
    test_vtab_cursor* cursor = (test_vtab_cursor*)cur;
    ++(cursor->rowid);
    return SQLITE_OK;
}

static int testVTabEof(sqlite3_vtab_cursor* cur)
{
    test_vtab_cursor* cursor = (test_vtab_cursor*)cur;
    return cursor->rowid < cursor->source ? 0 : 1;
}

static int testVTabColumn(sqlite3_vtab_cursor* cur, sqlite3_context* ctx, int i)
{
    test_vtab_cursor* cursor = (test_vtab_cursor*)cur;

    assert(i <= test_vtab_column_source);

    switch (i)
    {
    case test_vtab_column_value:
        sqlite3_result_int64(ctx, cursor->rowid);
        return SQLITE_OK;
    default:
        assert(i == test_vtab_column_source);
        sqlite3_result_int64(ctx, cursor->source);
        return SQLITE_OK;
    }
}

static int testVTabRowid(sqlite3_vtab_cursor* cur, sqlite_int64* pRowid)
{
    test_vtab_cursor* cursor = (test_vtab_cursor*)cur;
    *pRowid = cursor->rowid;
    return SQLITE_OK;
}

static sqlite3_module testVTabModule = {
  3, // iVersion
  NULL, // xCreate
  testVTabConnect, // xConnect
  testVTabBestIndex, // xBestIndex
  testVTabDisconnect, //xDisconnect
  NULL, // xDestroy
  testVTabOpen, //int xOpen
  testVTabClose, //int xClose
  testVTabFilter, //int xFilter
  testVTabNext, // int xNext
  testVTabEof, // int xEof
  testVTabColumn, // xColumn
  testVTabRowid, // xRowid
  NULL, // xUpdate
  NULL, // xBegin
  NULL, // xSync
  NULL, // xCommit
  NULL, // xRollback
  NULL, // xFindFunction
  NULL, // xRename
  NULL, // xSavepoint
  NULL, // xRelease
  NULL, // xRollbackTo
  NULL, // xShadowName
};

int main()
{
    int rc = 0;
    int rcCleanup = 0;
    int rcCurrentCleanup = 0;
    sqlite3* db = NULL;
    sqlite3_stmt* stmt = NULL;

    rc = sqlite3_open("D:\\test.db", &db);

    if (rc)
    {
        goto cleanup;
    }

    rc = sqlite3_create_module_v2(db, "testvtab", &testVTabModule, NULL, NULL);

    if (rc)
    {
        goto cleanup;
    }

    rc = sqlite3_prepare_v2(db, "SELECT count(*) FROM testvtab(NULL);", -1, &stmt, NULL);

    if (rc)
    {
        goto cleanup;
    }

    rc = sqlite3_step(stmt);

    if (rc != SQLITE_ROW)
    {
        goto cleanup;
    }

    printf("%i\n", sqlite3_column_int(stmt, 0));

    rc = sqlite3_step(stmt);

    if (rc != SQLITE_DONE)
    {
        goto cleanup;
    }

    rc = SQLITE_OK;

cleanup:
    if (rc && db)
    {
        fprintf(stderr, "%s\n", sqlite3_errmsg(db));
    }

    if (stmt)
    {
        rcCurrentCleanup = sqlite3_finalize(stmt);

        if (rcCurrentCleanup && !rcCleanup)
        {
            rcCleanup = rcCurrentCleanup;
        }
    }

    if (db)
    {
        rcCurrentCleanup = sqlite3_close_v2(db);

        if (rcCurrentCleanup && !rcCleanup)
        {
            rcCleanup = rcCurrentCleanup;
        }
    }

    if (rcCleanup)
    {
        return rcCleanup;
    }

    return rc;
}
00:34 Reply: Support for blocking VFS (artifact: c46d57b71a user: dmatson)

I had seen some related thing in the source. It looks like that is POSIX-only - is that correct? I'm specifically looking for Windows support.

2021-07-07
22:31 Post: Support for blocking VFS (artifact: 9797f8e50b user: dmatson)

We're using SQLite in a component with significant performance and parallelism requirements, and we would like to rely on OS locking rather than retry loops to handle multiple readers/writers. Stated another way, we'd like to find a way to ensure that we never see SQLITE_BUSY returned to the application and instead wait on an OS handle whenever such a condition would occur.

From looking at the source, it appears that the win32 VFS uses OS primitives that would support blocking rather than failing with SQLITE_BUSY when there is contention, but SQLite currently only uses these primitives in non-blocking mode. For example:

/*
** Currently, SQLite never calls the LockFileEx function without wanting the
** call to fail immediately if the lock cannot be obtained.
*/
#ifndef SQLITE_LOCKFILEEX_FLAGS
# define SQLITE_LOCKFILEEX_FLAGS (LOCKFILE_FAIL_IMMEDIATELY)
#endif

I was able to implementing something similar with a wrapper VFS for journal_mode = DELETE, by following the source for the current win32 VFS and replacing the non-blocking calls with blocking calls (I can share the code if that context would clarify things on this thread). However, for WAL mode, some calls to ShmLock appear to be "probes" that require the call to fail immediately if the lock isn't available, including cases with just one thread where the lock is guaranteed not to be available. For non-WAL mode, it looks like a correct implementation can block/wait to acquire the lock, but for WAL mode, the contract appears to prohibit waiting.

Two main questions: 1. For non-WAL-mode, how feasible would it be to support a blocking mode directly in SQLite? (rather than using a custom VFS, as seems possible today) 2. For WAL mode, would it be feasible to extend the VFS layer to make it possible to implement such a blocking version via a custom VFS?

Thanks,

David

22:14 Post: Switching pragma synchronous to upgrade durability (artifact: ed0e5d1865 user: dmatson)

If a transaction on a connection previously committed with pragma synchronous set to NORMAL (in WAL mode), but then I need to ensure the transaction is durable, can I switch pragma synchronous to FULL and commit another transaction to force durability for the previous transaction? Or is there some other way to ensure the database is in a fully-durable state?

If yes, would I need to use the same connection as the previous transaction, or would a new connection also produce the same effect?

Thanks,

David