/ Check-in [6a944f02]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Fixes for problems with small caches and SAVEPOINT rollback in WAL mode.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | wal
Files: files | file ages | folders
SHA1: 6a944f028d4a070bef29e1fbc6fbef481ebcd34c
User & Date: dan 2010-04-26 16:57:10
Context
2010-04-26
17:42
Do not attempt to set journal_mode=wal on :memory: or temp file databases. check-in: 30d01344 user: dan tags: wal
16:57
Fixes for problems with small caches and SAVEPOINT rollback in WAL mode. check-in: 6a944f02 user: dan tags: wal
12:39
Add the "wal" permutation to run existing test files savepoint.test and savepoint2.test in WAL mode. check-in: 205e5d8a user: dan tags: wal
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/pager.c.

217
218
219
220
221
222
223

224
225
226
227
228
229
230
....
1535
1536
1537
1538
1539
1540
1541

1542
1543
1544
1545
1546
1547
1548
....
1604
1605
1606
1607
1608
1609
1610



1611

1612
1613
1614
1615
1616
1617
1618
....
1621
1622
1623
1624
1625
1626
1627

1628
1629
1630
1631
1632
1633
1634
....
1685
1686
1687
1688
1689
1690
1691

1692
1693
1694
1695
1696
1697
1698
....
2424
2425
2426
2427
2428
2429
2430




2431
2432
2433
2434
2435
2436
2437
2438
2439
2440
2441

2442
2443
2444
2445
2446
2447
2448
....
3322
3323
3324
3325
3326
3327
3328




3329

3330
3331
3332
3333
3334
3335
3336
....
5338
5339
5340
5341
5342
5343
5344



5345
5346
5347
5348
5349
5350
5351
typedef struct PagerSavepoint PagerSavepoint;
struct PagerSavepoint {
  i64 iOffset;                 /* Starting offset in main journal */
  i64 iHdrOffset;              /* See above */
  Bitvec *pInSavepoint;        /* Set of pages in this savepoint */
  Pgno nOrig;                  /* Original number of pages in file */
  Pgno iSubRec;                /* Index of first record in sub-journal */

};

/*
** A open page cache is an instance of the following structure.
**
** errCode
**
................................................................................
  assert( (isMainJrnl&~1)==0 );      /* isMainJrnl is 0 or 1 */
  assert( (isSavepnt&~1)==0 );       /* isSavepnt is 0 or 1 */
  assert( isMainJrnl || pDone );     /* pDone always used on sub-journals */
  assert( isSavepnt || pDone==0 );   /* pDone never used on non-savepoint */

  aData = pPager->pTmpSpace;
  assert( aData );         /* Temp storage must have already been allocated */


  /* Read the page number and page data from the journal or sub-journal
  ** file. Return an error code to the caller if an IO error occurs.
  */
  jfd = isMainJrnl ? pPager->jfd : pPager->sjfd;
  rc = read32bits(jfd, *pOffset, &pgno);
  if( rc!=SQLITE_OK ) return rc;
................................................................................
  ** in the main journal either because the page is not in cache or else
  ** the page is marked as needSync==0.
  **
  ** 2008-04-14:  When attempting to vacuum a corrupt database file, it
  ** is possible to fail a statement on a database that does not yet exist.
  ** Do not attempt to write if database file has never been opened.
  */



  pPg = pager_lookup(pPager, pgno);

  assert( pPg || !MEMDB );
  PAGERTRACE(("PLAYBACK %d page %d hash(%08x) %s\n",
           PAGERID(pPager), pgno, pager_datahash(pPager->pageSize, (u8*)aData),
           (isMainJrnl?"main-journal":"sub-journal")
  ));
  if( isMainJrnl ){
    isSynced = pPager->noSync || (*pOffset <= pPager->journalHdr);
................................................................................
  }
  if( (pPager->state>=PAGER_EXCLUSIVE)
   && isOpen(pPager->fd)
   && isSynced
  ){
    i64 ofst = (pgno-1)*(i64)pPager->pageSize;
    testcase( !isSavepnt && pPg!=0 && (pPg->flags&PGHDR_NEED_SYNC)!=0 );

    rc = sqlite3OsWrite(pPager->fd, (u8*)aData, pPager->pageSize, ofst);
    if( pgno>pPager->dbFileSize ){
      pPager->dbFileSize = pgno;
    }
    if( pPager->pBackup ){
      CODEC1(pPager, aData, pgno, 3, rc=SQLITE_NOMEM);
      sqlite3BackupUpdate(pPager->pBackup, pgno, (u8*)aData);
................................................................................
      ** the PGHDR_NEED_SYNC flag is cleared, if the page is written to
      ** again within this transaction, it will be marked as dirty but
      ** the PGHDR_NEED_SYNC flag will not be set. It could then potentially
      ** be written out into the database file before its journal file
      ** segment is synced. If a crash occurs during or following this,
      ** database corruption may ensue.
      */

      sqlite3PcacheMakeClean(pPg);
    }
#ifdef SQLITE_CHECK_PAGES
    pPg->pageHash = pager_pagehash(pPg);
#endif
    /* If this was page 1, then restore the value of Pager.dbFileVers.
    ** Do this before any decoding. */
................................................................................
  /* Finally,  rollback pages from the sub-journal.  Page that were
  ** previously rolled back out of the main journal (and are hence in pDone)
  ** will be skipped.  Out-of-range pages are also skipped.
  */
  if( pSavepoint ){
    u32 ii;            /* Loop counter */
    i64 offset = pSavepoint->iSubRec*(4+pPager->pageSize);




    for(ii=pSavepoint->iSubRec; rc==SQLITE_OK && ii<pPager->nSubRec; ii++){
      assert( offset==ii*(4+pPager->pageSize) );
      rc = pager_playback_one_page(pPager, &offset, pDone, 0, 1);
    }
    assert( rc!=SQLITE_DONE );
  }

  sqlite3BitvecDestroy(pDone);
  if( rc==SQLITE_OK ){
    pPager->journalOff = szJ;
  }

  return rc;
}

/*
** Change the maximum number of in-memory pages that are allowed.
*/
void sqlite3PagerSetCachesize(Pager *pPager, int mxPage){
................................................................................

  assert( pPg->pPager==pPager );
  assert( pPg->flags&PGHDR_DIRTY );

  pPg->pDirty = 0;
  if( pagerUseLog(pPager) ){
    /* Write a single frame for this page to the log. */




    rc = pagerLogFrames(pPager, pPg, 0, 0, 0);

  }else{
    /* The doNotSync flag is set by the sqlite3PagerWrite() function while it
    ** is journalling a set of two or more database pages that are stored
    ** on the same disk sector. Syncing the journal is not allowed while
    ** this is happening as it is important that all members of such a
    ** set of pages are synced to disk together. So, if the page this function
    ** is trying to make clean will require a journal sync and the doNotSync
................................................................................
        aNew[ii].iOffset = JOURNAL_HDR_SZ(pPager);
      }
      aNew[ii].iSubRec = pPager->nSubRec;
      aNew[ii].pInSavepoint = sqlite3BitvecCreate(nPage);
      if( !aNew[ii].pInSavepoint ){
        return SQLITE_NOMEM;
      }



    }

    /* Open the sub-journal, if it is not already opened. */
    rc = openSubJournal(pPager);
    assertTruncateConstraint(pPager);
  }








>







 







>







 







>
>
>
|
>







 







>







 







>







 







>
>
>
>











>







 







>
>
>
>
|
>







 







>
>
>







217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
....
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
....
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
....
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
....
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
....
2432
2433
2434
2435
2436
2437
2438
2439
2440
2441
2442
2443
2444
2445
2446
2447
2448
2449
2450
2451
2452
2453
2454
2455
2456
2457
2458
2459
2460
2461
....
3335
3336
3337
3338
3339
3340
3341
3342
3343
3344
3345
3346
3347
3348
3349
3350
3351
3352
3353
3354
....
5356
5357
5358
5359
5360
5361
5362
5363
5364
5365
5366
5367
5368
5369
5370
5371
5372
typedef struct PagerSavepoint PagerSavepoint;
struct PagerSavepoint {
  i64 iOffset;                 /* Starting offset in main journal */
  i64 iHdrOffset;              /* See above */
  Bitvec *pInSavepoint;        /* Set of pages in this savepoint */
  Pgno nOrig;                  /* Original number of pages in file */
  Pgno iSubRec;                /* Index of first record in sub-journal */
  u32 iFrame;                  /* Last frame in WAL when savepoint opened */
};

/*
** A open page cache is an instance of the following structure.
**
** errCode
**
................................................................................
  assert( (isMainJrnl&~1)==0 );      /* isMainJrnl is 0 or 1 */
  assert( (isSavepnt&~1)==0 );       /* isSavepnt is 0 or 1 */
  assert( isMainJrnl || pDone );     /* pDone always used on sub-journals */
  assert( isSavepnt || pDone==0 );   /* pDone never used on non-savepoint */

  aData = pPager->pTmpSpace;
  assert( aData );         /* Temp storage must have already been allocated */
  assert( pagerUseLog(pPager)==0 || (!isMainJrnl && isSavepnt) );

  /* Read the page number and page data from the journal or sub-journal
  ** file. Return an error code to the caller if an IO error occurs.
  */
  jfd = isMainJrnl ? pPager->jfd : pPager->sjfd;
  rc = read32bits(jfd, *pOffset, &pgno);
  if( rc!=SQLITE_OK ) return rc;
................................................................................
  ** in the main journal either because the page is not in cache or else
  ** the page is marked as needSync==0.
  **
  ** 2008-04-14:  When attempting to vacuum a corrupt database file, it
  ** is possible to fail a statement on a database that does not yet exist.
  ** Do not attempt to write if database file has never been opened.
  */
  if( pagerUseLog(pPager) ){
    pPg = 0;
  }else{
    pPg = pager_lookup(pPager, pgno);
  }
  assert( pPg || !MEMDB );
  PAGERTRACE(("PLAYBACK %d page %d hash(%08x) %s\n",
           PAGERID(pPager), pgno, pager_datahash(pPager->pageSize, (u8*)aData),
           (isMainJrnl?"main-journal":"sub-journal")
  ));
  if( isMainJrnl ){
    isSynced = pPager->noSync || (*pOffset <= pPager->journalHdr);
................................................................................
  }
  if( (pPager->state>=PAGER_EXCLUSIVE)
   && isOpen(pPager->fd)
   && isSynced
  ){
    i64 ofst = (pgno-1)*(i64)pPager->pageSize;
    testcase( !isSavepnt && pPg!=0 && (pPg->flags&PGHDR_NEED_SYNC)!=0 );
    assert( !pagerUseLog(pPager) );
    rc = sqlite3OsWrite(pPager->fd, (u8*)aData, pPager->pageSize, ofst);
    if( pgno>pPager->dbFileSize ){
      pPager->dbFileSize = pgno;
    }
    if( pPager->pBackup ){
      CODEC1(pPager, aData, pgno, 3, rc=SQLITE_NOMEM);
      sqlite3BackupUpdate(pPager->pBackup, pgno, (u8*)aData);
................................................................................
      ** the PGHDR_NEED_SYNC flag is cleared, if the page is written to
      ** again within this transaction, it will be marked as dirty but
      ** the PGHDR_NEED_SYNC flag will not be set. It could then potentially
      ** be written out into the database file before its journal file
      ** segment is synced. If a crash occurs during or following this,
      ** database corruption may ensue.
      */
      assert( !pagerUseLog(pPager) );
      sqlite3PcacheMakeClean(pPg);
    }
#ifdef SQLITE_CHECK_PAGES
    pPg->pageHash = pager_pagehash(pPg);
#endif
    /* If this was page 1, then restore the value of Pager.dbFileVers.
    ** Do this before any decoding. */
................................................................................
  /* Finally,  rollback pages from the sub-journal.  Page that were
  ** previously rolled back out of the main journal (and are hence in pDone)
  ** will be skipped.  Out-of-range pages are also skipped.
  */
  if( pSavepoint ){
    u32 ii;            /* Loop counter */
    i64 offset = pSavepoint->iSubRec*(4+pPager->pageSize);

    if( pagerUseLog(pPager) ){
      rc = sqlite3WalSavepointUndo(pPager->pLog, pSavepoint->iFrame);
    }
    for(ii=pSavepoint->iSubRec; rc==SQLITE_OK && ii<pPager->nSubRec; ii++){
      assert( offset==ii*(4+pPager->pageSize) );
      rc = pager_playback_one_page(pPager, &offset, pDone, 0, 1);
    }
    assert( rc!=SQLITE_DONE );
  }

  sqlite3BitvecDestroy(pDone);
  if( rc==SQLITE_OK ){
    pPager->journalOff = szJ;
  }

  return rc;
}

/*
** Change the maximum number of in-memory pages that are allowed.
*/
void sqlite3PagerSetCachesize(Pager *pPager, int mxPage){
................................................................................

  assert( pPg->pPager==pPager );
  assert( pPg->flags&PGHDR_DIRTY );

  pPg->pDirty = 0;
  if( pagerUseLog(pPager) ){
    /* Write a single frame for this page to the log. */
    if( subjRequiresPage(pPg) ){ 
      rc = subjournalPage(pPg); 
    }
    if( rc==SQLITE_OK ){
      rc = pagerLogFrames(pPager, pPg, 0, 0, 0);
    }
  }else{
    /* The doNotSync flag is set by the sqlite3PagerWrite() function while it
    ** is journalling a set of two or more database pages that are stored
    ** on the same disk sector. Syncing the journal is not allowed while
    ** this is happening as it is important that all members of such a
    ** set of pages are synced to disk together. So, if the page this function
    ** is trying to make clean will require a journal sync and the doNotSync
................................................................................
        aNew[ii].iOffset = JOURNAL_HDR_SZ(pPager);
      }
      aNew[ii].iSubRec = pPager->nSubRec;
      aNew[ii].pInSavepoint = sqlite3BitvecCreate(nPage);
      if( !aNew[ii].pInSavepoint ){
        return SQLITE_NOMEM;
      }
      if( pagerUseLog(pPager) ){
        aNew[ii].iFrame = sqlite3WalSavepoint(pPager->pLog);
      }
    }

    /* Open the sub-journal, if it is not already opened. */
    rc = openSubJournal(pPager);
    assertTruncateConstraint(pPager);
  }

Changes to src/wal.c.

1664
1665
1666
1667
1668
1669
1670





















1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
  assert( pLog->isWriteLocked );
  logSummaryReadHdr(pLog, 0);
  for(iFrame=pLog->hdr.iLastPg+1; iFrame<=iMax && rc==SQLITE_OK; iFrame++){
    rc = xUndo(pUndoCtx, pLog->pSummary->aData[logSummaryEntry(iFrame)]);
  }
  return rc;
}






















/* 
** Return true if data has been written but not committed to the log file. 
*/
int sqlite3WalDirty(Log *pLog){
  assert( pLog->isWriteLocked );
  return( pLog->hdr.iLastPg!=((LogSummaryHdr*)pLog->pSummary->aData)->iLastPg );
}

/* 
** Write a set of frames to the log. The caller must hold at least a
** RESERVED lock on the database file.
*/
int sqlite3WalFrames(
  Log *pLog,                      /* Log handle to write to */
  int nPgsz,                      /* Database page-size in bytes */
  PgHdr *pList,                   /* List of dirty pages to write */
  Pgno nTruncate,                 /* Database size after this commit */
  int isCommit,                   /* True if this is a commit */







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










|
|







1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
  assert( pLog->isWriteLocked );
  logSummaryReadHdr(pLog, 0);
  for(iFrame=pLog->hdr.iLastPg+1; iFrame<=iMax && rc==SQLITE_OK; iFrame++){
    rc = xUndo(pUndoCtx, pLog->pSummary->aData[logSummaryEntry(iFrame)]);
  }
  return rc;
}

u32 sqlite3WalSavepoint(Log *pLog){
  assert( pLog->isWriteLocked );
  return pLog->hdr.iLastPg;
}

int sqlite3WalSavepointUndo(Log *pLog, u32 iFrame){
  int rc = SQLITE_OK;
  u8 aCksum[8];
  assert( pLog->isWriteLocked );

  pLog->hdr.iLastPg = iFrame;
  if( iFrame>0 ){
    i64 iOffset = logFrameOffset(iFrame, pLog->hdr.pgsz) + sizeof(u32)*2;
    rc = sqlite3OsRead(pLog->pFd, aCksum, sizeof(aCksum), iOffset);
    pLog->hdr.iCheck1 = sqlite3Get4byte(&aCksum[0]);
    pLog->hdr.iCheck2 = sqlite3Get4byte(&aCksum[4]);
  }

  return rc;
}

/* 
** Return true if data has been written but not committed to the log file. 
*/
int sqlite3WalDirty(Log *pLog){
  assert( pLog->isWriteLocked );
  return( pLog->hdr.iLastPg!=((LogSummaryHdr*)pLog->pSummary->aData)->iLastPg );
}

/* 
** Write a set of frames to the log. The caller must hold the write-lock
** on the log file (obtained using sqlite3WalWriteLock()).
*/
int sqlite3WalFrames(
  Log *pLog,                      /* Log handle to write to */
  int nPgsz,                      /* Database page-size in bytes */
  PgHdr *pList,                   /* List of dirty pages to write */
  Pgno nTruncate,                 /* Database size after this commit */
  int isCommit,                   /* True if this is a commit */

Changes to src/wal.h.

36
37
38
39
40
41
42



43
44
45
46
47
48
49

/* Obtain or release the WRITER lock. */
int sqlite3WalWriteLock(Log *pLog, int op);

/* Undo any frames written (but not committed) to the log */
int sqlite3WalUndo(Log *pLog, int (*xUndo)(void *, Pgno), void *pUndoCtx);




/* Return true if data has been written but not committed to the log file. */
int sqlite3WalDirty(Log *pLog);

/* Write a frame or frames to the log. */
int sqlite3WalFrames(Log *pLog, int, PgHdr *, Pgno, int, int);

/* Copy pages from the log to the database file */ 







>
>
>







36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

/* Obtain or release the WRITER lock. */
int sqlite3WalWriteLock(Log *pLog, int op);

/* Undo any frames written (but not committed) to the log */
int sqlite3WalUndo(Log *pLog, int (*xUndo)(void *, Pgno), void *pUndoCtx);

u32 sqlite3WalSavepoint(Log *pLog);
int sqlite3WalSavepointUndo(Log *pLog, u32 iFrame);

/* Return true if data has been written but not committed to the log file. */
int sqlite3WalDirty(Log *pLog);

/* Write a frame or frames to the log. */
int sqlite3WalFrames(Log *pLog, int, PgHdr *, Pgno, int, int);

/* Copy pages from the log to the database file */ 

Changes to test/permutations.test.

757
758
759
760
761
762
763

764
765
766
767
768
769
770
}

run_tests "wal" -description {
  Run tests with journal_mode=WAL
} -include {
  savepoint.test
  savepoint2.test

}

# End of tests
#############################################################################

if {$::perm::testmode eq "targets"} { puts "" ; exit }








>







757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
}

run_tests "wal" -description {
  Run tests with journal_mode=WAL
} -include {
  savepoint.test
  savepoint2.test
  savepoint6.test
}

# End of tests
#############################################################################

if {$::perm::testmode eq "targets"} { puts "" ; exit }

Changes to test/savepoint6.test.

218
219
220
221
222
223
224

225
226
227
228
229
230
231
...
237
238
239
240
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
  execsql {SELECT count(*) FROM t1}
} {44}

foreach zSetup [list {
  set testname normal
  sqlite3 db test.db
} {

  set testname tempdb
  sqlite3 db ""
} {
  if {[catch {set ::permutations_test_prefix} z] == 0 && $z eq "journaltest"} {
    continue
  }
  set testname nosync
................................................................................
  sql { PRAGMA cache_size = 10 }
}] {

  unset -nocomplain ::lSavepoint
  unset -nocomplain ::aEntry

  catch { db close }
  file delete -force test.db
  eval $zSetup
  sql $DATABASE_SCHEMA



  do_test savepoint6-$testname.setup {
    savepoint one
    insert_rows [random_integers 100 1000]
    release one
    checkdb
  } {ok}
  
  for {set i 0} {$i < 1000} {incr i} {
    do_test savepoint6-$testname.$i.1 {
      savepoint_op
      checkdb
    } {ok}
  
    do_test savepoint6-$testname.$i.2 {
      database_op
      database_op
      checkdb
    } {ok}
  }


}

unset -nocomplain ::lSavepoint
unset -nocomplain ::aEntry

finish_test







>







 







|


>
>








|











>
>






218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
...
238
239
240
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
  execsql {SELECT count(*) FROM t1}
} {44}

foreach zSetup [list {
  set testname normal
  sqlite3 db test.db
} {
  if {[wal_is_wal_mode]} continue
  set testname tempdb
  sqlite3 db ""
} {
  if {[catch {set ::permutations_test_prefix} z] == 0 && $z eq "journaltest"} {
    continue
  }
  set testname nosync
................................................................................
  sql { PRAGMA cache_size = 10 }
}] {

  unset -nocomplain ::lSavepoint
  unset -nocomplain ::aEntry

  catch { db close }
  file delete -force test.db test.db-wal test.db-journal
  eval $zSetup
  sql $DATABASE_SCHEMA

  wal_set_journal_mode

  do_test savepoint6-$testname.setup {
    savepoint one
    insert_rows [random_integers 100 1000]
    release one
    checkdb
  } {ok}
  
  for {set i 0} {$i < 50} {incr i} {
    do_test savepoint6-$testname.$i.1 {
      savepoint_op
      checkdb
    } {ok}
  
    do_test savepoint6-$testname.$i.2 {
      database_op
      database_op
      checkdb
    } {ok}
  }

  wal_check_journal_mode savepoint6-$testname.walok
}

unset -nocomplain ::lSavepoint
unset -nocomplain ::aEntry

finish_test

Changes to test/wal.test.

162
163
164
165
166
167
168
169
170
171
172
173
174












































175












176
177
178

179
180
181
182
183
184
185
...
187
188
189
190
191
192
193
194

195
196
197
198

199
200
201

202
203
204
205
206
207
208
209
210
211

212
213
214
215
216
217

218
219
220
221
222
223
224
...
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
do_test wal-4.3 {
  execsql {
    COMMIT;
    SELECT * FROM t1;
  }
} {a b}

do_test wal-4.4 {
  db close
  sqlite3 db test.db
  db func blob blob
  list [execsql { SELECT * FROM t1 }] [file size test.db-wal]
} {{a b} 0}












































do_test wal-4.5 {












  execsql { PRAGMA cache_size = 10 }
  execsql {
    CREATE TABLE t2(a, b);

    INSERT INTO t2 VALUES(blob(400), blob(400));
    SAVEPOINT tr;
      INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /*  2 */
      INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /*  4 */
      INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /*  8 */
      INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /* 16 */
      INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /* 32 */
................................................................................
      INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /*  4 */
      INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /*  8 */
      INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /* 16 */
      INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /* 32 */
      SELECT count(*) FROM t2;
  }
} {32}
do_test wal-4.6 {

  execsql { ROLLBACK TO tr }
} {}
do_test wal-4.7 {
  set logsize [file size test.db-wal]

  execsql {
      INSERT INTO t1 VALUES('x', 'y');
    RELEASE tr;

  }
  expr { $logsize == [file size test.db-wal] }
} {1}
do_test wal-4.8 {
  execsql { SELECT count(*) FROM t2 }
} {1}
do_test wal-4.9 {
  file copy -force test.db test2.db
  file copy -force test.db-wal test2.db-wal
  sqlite3 db2 test2.db

  execsql { SELECT count(*) FROM t2 ; SELECT count(*) FROM t1 } db2
} {1 2}
do_test wal-4.10 {
  execsql { PRAGMA integrity_check } db2
} {ok}
db2 close


reopen_db
do_test wal-5.1 {
  execsql {
    CREATE TEMP TABLE t2(a, b);
    INSERT INTO t2 VALUES(1, 2);
  }
................................................................................
  execsql {
    PRAGMA cache_size = 10;
    BEGIN;
      INSERT INTO t1 SELECT blob(900) FROM t1;   -- 32
      SELECT count(*) FROM t1;
  }
  list [expr [file size test.db]/1024] [file size test.db-wal]
} [list 37 [log_file_size 35 1024]]
do_test wal-11.11 {
  execsql {
      SELECT count(*) FROM t1;
    ROLLBACK;
    SELECT count(*) FROM t1;
  }
} {32 16}
do_test wal-11.12 {
  list [expr [file size test.db]/1024] [file size test.db-wal]
} [list 37 [log_file_size 35 1024]]
do_test wal-11.13 {
  execsql {
    INSERT INTO t1 VALUES( blob(900) );
    SELECT count(*) FROM t1;
    PRAGMA integrity_check;
  }
} {17 ok}
do_test wal-11.14 {
  list [expr [file size test.db]/1024] [file size test.db-wal]
} [list 37 [log_file_size 35 1024]]


#-------------------------------------------------------------------------
# This block of tests, wal-12.*, tests the fix for a problem that 
# could occur if a log that is a prefix of an older log is written 
# into a reused log file.
#







|





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



>







 







|
>


|

>



>



|
|
|
|



>


|



>







 







|









|









|







162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
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
237
238
239
240
241
242
...
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
...
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
do_test wal-4.3 {
  execsql {
    COMMIT;
    SELECT * FROM t1;
  }
} {a b}

do_test wal-4.4.1 {
  db close
  sqlite3 db test.db
  db func blob blob
  list [execsql { SELECT * FROM t1 }] [file size test.db-wal]
} {{a b} 0}
do_test wal-4.4.2 {
  execsql { PRAGMA cache_size = 10 }
  execsql {
    CREATE TABLE t2(a, b);
    INSERT INTO t2 VALUES(blob(400), blob(400));
    SAVEPOINT tr;
      INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /*  2 */
      INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /*  4 */
      INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /*  8 */
      INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /* 16 */
      INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /* 32 */
      INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /*  2 */
      INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /*  4 */
      INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /*  8 */
      INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /* 16 */
      INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /* 32 */
      SELECT count(*) FROM t2;
  }
} {32}
do_test wal-4.4.3 {
  execsql { ROLLBACK TO tr }
} {}
do_test wal-4.4.4 {
  set logsize [file size test.db-wal]
  execsql {
      INSERT INTO t1 VALUES('x', 'y');
    RELEASE tr;
  }
  expr { $logsize == [file size test.db-wal] }
} {1}
do_test wal-4.4.5 {
  execsql { SELECT count(*) FROM t2 }
} {1}
do_test wal-4.4.6 {
  file copy -force test.db test2.db
  file copy -force test.db-wal test2.db-wal
  sqlite3 db2 test2.db
  execsql { SELECT count(*) FROM t2 ; SELECT count(*) FROM t1 } db2
} {1 2}
do_test wal-4.4.7 {
  execsql { PRAGMA integrity_check } db2
} {ok}
db2 close

do_test wal-4.5.1 {
  reopen_db
  db func blob blob
  execsql {
    PRAGMA journal_mode = WAL;
    CREATE TABLE t1(a, b);
    INSERT INTO t1 VALUES('a', 'b');
  }
  sqlite3 db test.db
  db func blob blob
  list [execsql { SELECT * FROM t1 }] [file size test.db-wal]
} {{a b} 0}
do_test wal-4.5.2 {
  execsql { PRAGMA cache_size = 10 }
  execsql {
    CREATE TABLE t2(a, b);
    BEGIN;
    INSERT INTO t2 VALUES(blob(400), blob(400));
    SAVEPOINT tr;
      INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /*  2 */
      INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /*  4 */
      INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /*  8 */
      INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /* 16 */
      INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /* 32 */
................................................................................
      INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /*  4 */
      INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /*  8 */
      INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /* 16 */
      INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /* 32 */
      SELECT count(*) FROM t2;
  }
} {32}
do_test wal-4.5.3 {
breakpoint
  execsql { ROLLBACK TO tr }
} {}
do_test wal-4.5.4 {
  set logsize [file size test.db-wal]
breakpoint
  execsql {
      INSERT INTO t1 VALUES('x', 'y');
    RELEASE tr;
    COMMIT;
  }
  expr { $logsize == [file size test.db-wal] }
} {1}
do_test wal-4.5.5 {
  execsql { SELECT count(*) FROM t2 ; SELECT count(*) FROM t1 }
} {1 2}
do_test wal-4.5.6 {
  file copy -force test.db test2.db
  file copy -force test.db-wal test2.db-wal
  sqlite3 db2 test2.db
breakpoint
  execsql { SELECT count(*) FROM t2 ; SELECT count(*) FROM t1 } db2
} {1 2}
do_test wal-4.5.7 {
  execsql { PRAGMA integrity_check } db2
} {ok}
db2 close


reopen_db
do_test wal-5.1 {
  execsql {
    CREATE TEMP TABLE t2(a, b);
    INSERT INTO t2 VALUES(1, 2);
  }
................................................................................
  execsql {
    PRAGMA cache_size = 10;
    BEGIN;
      INSERT INTO t1 SELECT blob(900) FROM t1;   -- 32
      SELECT count(*) FROM t1;
  }
  list [expr [file size test.db]/1024] [file size test.db-wal]
} [list 37 [log_file_size 37 1024]]
do_test wal-11.11 {
  execsql {
      SELECT count(*) FROM t1;
    ROLLBACK;
    SELECT count(*) FROM t1;
  }
} {32 16}
do_test wal-11.12 {
  list [expr [file size test.db]/1024] [file size test.db-wal]
} [list 37 [log_file_size 37 1024]]
do_test wal-11.13 {
  execsql {
    INSERT INTO t1 VALUES( blob(900) );
    SELECT count(*) FROM t1;
    PRAGMA integrity_check;
  }
} {17 ok}
do_test wal-11.14 {
  list [expr [file size test.db]/1024] [file size test.db-wal]
} [list 37 [log_file_size 37 1024]]


#-------------------------------------------------------------------------
# This block of tests, wal-12.*, tests the fix for a problem that 
# could occur if a log that is a prefix of an older log is written 
# into a reused log file.
#