/ Check-in [6e2da589]
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:Increase the default PMA size from 10 to 250 pages and provide the SQLITE_SORTER_PMASZ compile-time option to change this default. Add needed mutex call when clearing the KeyInfo cache in shared-cache mode.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | apple-osx
Files: files | file ages | folders
SHA1: 6e2da589ad562e0e412eb6572a8681225259b46e
User & Date: drh 2014-12-09 15:01:07
Context
2014-12-16
00:29
Merge latest fixes and enhancements from trunk into apple-osx. check-in: 2c1d8dda user: drh tags: apple-osx
2014-12-09
15:01
Increase the default PMA size from 10 to 250 pages and provide the SQLITE_SORTER_PMASZ compile-time option to change this default. Add needed mutex call when clearing the KeyInfo cache in shared-cache mode. check-in: 6e2da589 user: drh tags: apple-osx
14:42
Increase the default minimum PMA size for multi-threaded sorting from 10x the page size to 250x the page size. Provide the SQLITE_SORTER_PMASZ compile-time option to change this default. check-in: b05340fe user: drh tags: trunk
2014-12-05
20:16
Merge the shared-cache KeyInfo fix from trunk. check-in: 8a9b43f9 user: drh tags: apple-osx
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/analyze.c.

  1592   1592         int i;                    /* Used to iterate through samples */
  1593   1593         tRowcnt sumEq = 0;        /* Sum of the nEq values */
  1594   1594         tRowcnt avgEq = 0;
  1595   1595         tRowcnt nRow;             /* Number of rows in index */
  1596   1596         i64 nSum100 = 0;          /* Number of terms contributing to sumEq */
  1597   1597         i64 nDist100;             /* Number of distinct values in index */
  1598   1598   
  1599         -      if( pIdx->aiRowEst==0 || pIdx->aiRowEst[iCol+1]==0 ){
         1599  +      if( !pIdx->aiRowEst || iCol>=pIdx->nKeyCol || pIdx->aiRowEst[iCol+1]==0 ){
  1600   1600           nRow = pFinal->anLt[iCol];
  1601   1601           nDist100 = (i64)100 * pFinal->anDLt[iCol];
  1602   1602           nSample--;
  1603   1603         }else{
  1604   1604           nRow = pIdx->aiRowEst[0];
  1605   1605           nDist100 = ((i64)100 * pIdx->aiRowEst[0]) / pIdx->aiRowEst[iCol+1];
  1606   1606         }

Changes to src/btree.c.

  6808   6808     szScratch =
  6809   6809          nMaxCells*sizeof(u8*)                       /* apCell */
  6810   6810        + nMaxCells*sizeof(u16)                       /* szCell */
  6811   6811        + pBt->pageSize;                              /* aSpace1 */
  6812   6812   
  6813   6813     /* EVIDENCE-OF: R-28375-38319 SQLite will never request a scratch buffer
  6814   6814     ** that is more than 6 times the database page size. */
  6815         -  assert( szScratch<=6*pBt->pageSize );
         6815  +  assert( szScratch<=6*(int)pBt->pageSize );
  6816   6816     apCell = sqlite3ScratchMalloc( szScratch ); 
  6817   6817     if( apCell==0 ){
  6818   6818       rc = SQLITE_NOMEM;
  6819   6819       goto balance_cleanup;
  6820   6820     }
  6821   6821     szCell = (u16*)&apCell[nMaxCells];
  6822   6822     aSpace1 = (u8*)&szCell[nMaxCells];
................................................................................
  6882   6882           assert( pOld->hdrOffset==0 );
  6883   6883           /* The right pointer of the child page pOld becomes the left
  6884   6884           ** pointer of the divider cell */
  6885   6885           memcpy(apCell[nCell], &pOld->aData[8], 4);
  6886   6886         }else{
  6887   6887           assert( leafCorrection==4 );
  6888   6888           if( szCell[nCell]<4 ){
  6889         -          /* Do not allow any cells smaller than 4 bytes. */
         6889  +          /* Do not allow any cells smaller than 4 bytes. If a smaller cell
         6890  +          ** does exist, pad it with 0x00 bytes. */
         6891  +          assert( szCell[nCell]==3 );
         6892  +          assert( apCell[nCell]==&pTemp[iSpace1-3] );
         6893  +          pTemp[iSpace1++] = 0x00;
  6890   6894             szCell[nCell] = 4;
  6891   6895           }
  6892   6896         }
  6893   6897         nCell++;
  6894   6898       }
  6895   6899     }
  6896   6900   
................................................................................
  7117   7121   
  7118   7122         /* Cell pCell is destined for new sibling page pNew. Originally, it
  7119   7123         ** was either part of sibling page iOld (possibly an overflow cell), 
  7120   7124         ** or else the divider cell to the left of sibling page iOld. So,
  7121   7125         ** if sibling page iOld had the same page number as pNew, and if
  7122   7126         ** pCell really was a part of sibling page iOld (not a divider or
  7123   7127         ** overflow cell), we can skip updating the pointer map entries.  */
  7124         -      if( pNew->pgno!=aPgno[iOld] || pCell<aOld || pCell>=&aOld[usableSize] ){
         7128  +      if( iOld>=nNew
         7129  +       || pNew->pgno!=aPgno[iOld]
         7130  +       || pCell<aOld
         7131  +       || pCell>=&aOld[usableSize]
         7132  +      ){
  7125   7133           if( !leafCorrection ){
  7126   7134             ptrmapPut(pBt, get4byte(pCell), PTRMAP_BTREE, pNew->pgno, &rc);
  7127   7135           }
  7128   7136           if( szCell[i]>pNew->minLocal ){
  7129   7137             ptrmapPutOvflPtr(pNew, pCell, &rc);
  7130   7138           }
  7131   7139         }

Changes to src/main.c.

  1033   1033   
  1034   1034     /* Close all database connections */
  1035   1035     for(j=0; j<db->nDb; j++){
  1036   1036       struct Db *pDb = &db->aDb[j];
  1037   1037       if( pDb->pBt ){
  1038   1038         if( pDb->pSchema ){
  1039   1039           /* Must clear the KeyInfo cache.  See ticket [e4a18565a36884b00edf] */
         1040  +        sqlite3BtreeEnter(pDb->pBt);
  1040   1041           for(i=sqliteHashFirst(&pDb->pSchema->idxHash); i; i=sqliteHashNext(i)){
  1041   1042             Index *pIdx = sqliteHashData(i);
  1042   1043             sqlite3KeyInfoUnref(pIdx->pKeyInfo);
  1043   1044             pIdx->pKeyInfo = 0;
  1044   1045           }
         1046  +        sqlite3BtreeLeave(pDb->pBt);
  1045   1047         }
  1046   1048         sqlite3BtreeClose(pDb->pBt);
  1047   1049         pDb->pBt = 0;
  1048   1050         if( j!=1 ){
  1049   1051           pDb->pSchema = 0;
  1050   1052         }
  1051   1053       }
................................................................................
  2883   2885     if( rc!=SQLITE_OK ){
  2884   2886       if( rc==SQLITE_IOERR_NOMEM ){
  2885   2887         rc = SQLITE_NOMEM;
  2886   2888       }
  2887   2889       sqlite3Error(db, rc);
  2888   2890       goto opendb_out;
  2889   2891     }
         2892  +  sqlite3BtreeEnter(db->aDb[0].pBt);
  2890   2893     db->aDb[0].pSchema = sqlite3SchemaGet(db, db->aDb[0].pBt);
         2894  +  sqlite3BtreeLeave(db->aDb[0].pBt);
  2891   2895     db->aDb[1].pSchema = sqlite3SchemaGet(db, 0);
  2892   2896   
  2893   2897     /* The default safety_level for the main database is 'full'; for the temp
  2894   2898     ** database it is 'NONE'. This matches the pager layer defaults.  
  2895   2899     */
  2896   2900     db->aDb[0].zName = "main";
  2897   2901     db->aDb[0].safety_level = 3;

Changes to src/test1.c.

  5892   5892       Tcl_WrongNumArgs(interp, 1, objv, "DB MODE ?NAME?");
  5893   5893       return TCL_ERROR;
  5894   5894     }
  5895   5895   
  5896   5896     if( objc==4 ){
  5897   5897       zDb = Tcl_GetString(objv[3]);
  5898   5898     }
  5899         -  if( getDbPointer(interp, Tcl_GetString(objv[1]), &db)
  5900         -   || Tcl_GetIndexFromObj(interp, objv[2], aMode, "mode", 0, &eMode) 
  5901         -  ){
         5899  +  if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) || (
         5900  +      TCL_OK!=Tcl_GetIntFromObj(0, objv[2], &eMode)
         5901  +   && TCL_OK!=Tcl_GetIndexFromObj(interp, objv[2], aMode, "mode", 0, &eMode) 
         5902  +  )){
  5902   5903       return TCL_ERROR;
  5903   5904     }
  5904   5905   
  5905   5906     rc = sqlite3_wal_checkpoint_v2(db, zDb, eMode, &nLog, &nCkpt);
  5906   5907     if( rc!=SQLITE_OK && rc!=SQLITE_BUSY ){
  5907         -    Tcl_SetResult(interp, (char *)sqlite3_errmsg(db), TCL_VOLATILE);
         5908  +    const char *zErrCode = sqlite3ErrName(rc);
         5909  +    Tcl_AppendResult(interp, zErrCode, " - ", (char *)sqlite3_errmsg(db), 0);
  5908   5910       return TCL_ERROR;
  5909   5911     }
  5910   5912   
  5911   5913     pRet = Tcl_NewObj();
  5912   5914     Tcl_ListObjAppendElement(interp, pRet, Tcl_NewIntObj(rc==SQLITE_BUSY?1:0));
  5913   5915     Tcl_ListObjAppendElement(interp, pRet, Tcl_NewIntObj(nLog));
  5914   5916     Tcl_ListObjAppendElement(interp, pRet, Tcl_NewIntObj(nCkpt));

Changes to src/vdbesort.c.

   446    446   **
   447    447   **   void *SRVAL(SorterRecord *p) { return (void*)&p[1]; }
   448    448   */
   449    449   #define SRVAL(p) ((void*)((SorterRecord*)(p) + 1))
   450    450   
   451    451   /* The minimum PMA size is set to this value multiplied by the database
   452    452   ** page size in bytes.  */
   453         -#define SORTER_MIN_WORKING 10
          453  +#ifndef SQLITE_SORTER_PMASZ
          454  +# define SQLITE_SORTER_PMASZ 250
          455  +#endif
   454    456   
   455    457   /* Maximum number of PMAs that a single MergeEngine can merge */
   456    458   #define SORTER_MAX_MERGE_COUNT 16
   457    459   
   458    460   static int vdbeIncrSwap(IncrMerger*);
   459    461   static void vdbeIncrFree(IncrMerger *);
   460    462   
................................................................................
   845    847       pSorter->db = db;
   846    848       for(i=0; i<pSorter->nTask; i++){
   847    849         SortSubtask *pTask = &pSorter->aTask[i];
   848    850         pTask->pSorter = pSorter;
   849    851       }
   850    852   
   851    853       if( !sqlite3TempInMemory(db) ){
   852         -      pSorter->mnPmaSize = SORTER_MIN_WORKING * pgsz;
          854  +      pSorter->mnPmaSize = SQLITE_SORTER_PMASZ * pgsz;
   853    855         mxCache = db->aDb[0].pSchema->cache_size;
   854         -      if( mxCache<SORTER_MIN_WORKING ) mxCache = SORTER_MIN_WORKING;
          856  +      if( mxCache<SQLITE_SORTER_PMASZ ) mxCache = SQLITE_SORTER_PMASZ;
   855    857         pSorter->mxPmaSize = MIN((i64)mxCache*pgsz, SQLITE_MAX_MXPMASIZE);
   856    858   
   857    859         /* EVIDENCE-OF: R-26747-61719 When the application provides any amount of
   858    860         ** scratch memory using SQLITE_CONFIG_SCRATCH, SQLite avoids unnecessary
   859    861         ** large heap allocations.
   860    862         */
   861    863         if( sqlite3GlobalConfig.pScratch==0 ){

Changes to src/where.c.

  2944   2944     Vdbe *v,                        /* Vdbe to add scanstatus entry to */
  2945   2945     SrcList *pSrclist,              /* FROM clause pLvl reads data from */
  2946   2946     WhereLevel *pLvl,               /* Level to add scanstatus() entry for */
  2947   2947     int addrExplain                 /* Address of OP_Explain (or 0) */
  2948   2948   ){
  2949   2949     const char *zObj = 0;
  2950   2950     WhereLoop *pLoop = pLvl->pWLoop;
  2951         -  if( (pLoop->wsFlags & (WHERE_IPK|WHERE_VIRTUALTABLE))==0 ){
         2951  +  if( (pLoop->wsFlags & WHERE_VIRTUALTABLE)==0  &&  pLoop->u.btree.pIndex!=0 ){
  2952   2952       zObj = pLoop->u.btree.pIndex->zName;
  2953   2953     }else{
  2954   2954       zObj = pSrclist->a[pLvl->iFrom].zName;
  2955   2955     }
  2956   2956     sqlite3VdbeScanStatus(
  2957   2957         v, addrExplain, pLvl->addrBody, pLvl->addrVisit, pLoop->nOut, zObj
  2958   2958     );

Added test/e_walckpt.test.

            1  +# 2014 December 04
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +
           13  +set testdir [file dirname $argv0]
           14  +source $testdir/tester.tcl
           15  +source $testdir/lock_common.tcl
           16  +source $testdir/wal_common.tcl
           17  +set testprefix e_walckpt
           18  +
           19  +# The following two commands are used to determine if any of the files
           20  +# "test.db", "test.db2" and "test.db3" are modified by a test case.
           21  +#
           22  +# The [save_db_hashes] command saves a hash of the current contents of
           23  +# all three files in global variables. The [compare_db_hashes] compares
           24  +# the current contents with the saved hashes and returns a list of the
           25  +# files that have changed.
           26  +#
           27  +proc save_db_hashes {} {
           28  +  global H
           29  +  foreach f {test.db test.db2 test.db3} {
           30  +    set H($f) 0
           31  +    catch { set H($f) [md5file $f] }
           32  +  }
           33  +}
           34  +proc compare_db_hashes {} {
           35  +  global H
           36  +  set ret [list]
           37  +  foreach f {test.db test.db2 test.db3} {
           38  +    set expect 0
           39  +    catch { set expect [md5file $f] }
           40  +    if {$H($f) != $expect} { lappend ret $f }
           41  +  }
           42  +  set ret
           43  +}
           44  +
           45  +
           46  +# The following tests are run 3 times, each using a different method of 
           47  +# invoking a checkpoint:
           48  +#
           49  +#   1) Using sqlite3_wal_checkpoint_v2()
           50  +#   2) Using "PRAGMA wal_checkpoint"
           51  +#   3) Using sqlite3_wal_checkpoint() in place of checkpoint_v2(PASSIVE)
           52  +#
           53  +# Cases (2) and (3) are to show that the following statements are 
           54  +# correct, respectively:
           55  +#
           56  +# EVIDENCE-OF: R-36706-10507 The PRAGMA wal_checkpoint command can be
           57  +# used to invoke this interface from SQL.
           58  +#
           59  +# EVIDENCE-OF: R-41613-20553 The sqlite3_wal_checkpoint(D,X) is
           60  +# equivalent to
           61  +# sqlite3_wal_checkpoint_v2(D,X,SQLITE_CHECKPOINT_PASSIVE,0,0).
           62  +# 
           63  +foreach {tn script} {
           64  +  1 {
           65  +    proc checkpoint {db mode args} {
           66  +      eval sqlite3_wal_checkpoint_v2 [list $db] [list $mode] $args
           67  +    }
           68  +  }
           69  +
           70  +  2 {
           71  +    proc checkpoint {db mode args} {
           72  +      set sql "PRAGMA wal_checkpoint = $mode"
           73  +      if {[llength $args] && [lindex $args 0]!=""} {
           74  +        set sql "PRAGMA [lindex $args 0].wal_checkpoint = $mode"
           75  +      }
           76  +      set rc [catch { $db eval $sql } msg]
           77  +      if {$rc} {
           78  +        regsub {database} $msg {database:} msg
           79  +        error "[sqlite3_errcode $db] - $msg"
           80  +      }
           81  +      set msg
           82  +    }
           83  +  }
           84  +
           85  +  3 {
           86  +    proc checkpoint {db mode args} {
           87  +      if {$mode == "passive"} {
           88  +        set rc [eval sqlite3_wal_checkpoint [list $db] $args]
           89  +        if {$rc != "SQLITE_OK"} {
           90  +          error "$rc - [sqlite3_errmsg $db]"
           91  +        }
           92  +      } else {
           93  +        eval sqlite3_wal_checkpoint_v2 [list $db] [list $mode] $args
           94  +      }
           95  +    }
           96  +  }
           97  +
           98  +} {
           99  +
          100  +  eval $script
          101  +
          102  +  reset_db
          103  +  forcedelete test.db2 test.db3 test.db4
          104  +  execsql {
          105  +    ATTACH 'test.db2' AS aux;
          106  +    ATTACH 'test.db3' AS aux2;
          107  +    ATTACH 'test.db4' AS aux3;
          108  +    CREATE TABLE t1(x);
          109  +    CREATE TABLE aux.t2(x);
          110  +    CREATE TABLE aux2.t3(x);
          111  +    CREATE TABLE aux3.t4(x);
          112  +    PRAGMA main.journal_mode = WAL;
          113  +    PRAGMA aux.journal_mode = WAL;
          114  +    PRAGMA aux2.journal_mode = WAL;
          115  +    /* Leave aux4 in rollback mode */
          116  +  }
          117  +
          118  +  # EVIDENCE-OF: R-49787-09095 The sqlite3_wal_checkpoint_v2(D,X,M,L,C)
          119  +  # interface runs a checkpoint operation on database X of database
          120  +  # connection D in mode M. Status information is written back into
          121  +  # integers pointed to by L and C.
          122  +  #
          123  +  #     Tests 1, 2 and 3 below verify the "on database X" part of the
          124  +  #     above. Other parts of this requirement are tested below.
          125  +  #
          126  +  # EVIDENCE-OF: R-00653-06026 If parameter zDb is NULL or points to a
          127  +  # zero length string, then the specified operation is attempted on all
          128  +  # WAL databases attached to database connection db.
          129  +  #
          130  +  #     Tests 4 and 5 below test this.
          131  +  #
          132  +  foreach {tn2 zDb dblist} {
          133  +    1 main  test.db
          134  +    2 aux   test.db2
          135  +    3 aux2  test.db3
          136  +    4 ""    {test.db test.db2 test.db3}
          137  +    5 -     {test.db test.db2 test.db3}
          138  +    6 temp  {}
          139  +  } {
          140  +    do_test $tn.1.$tn2 {
          141  +      execsql {
          142  +        INSERT INTO t1 VALUES(1);
          143  +        INSERT INTO t2 VALUES(2);
          144  +        INSERT INTO t3 VALUES(3);
          145  +      }
          146  +      save_db_hashes
          147  +
          148  +      if {$zDb == "-"} {
          149  +        checkpoint db passive
          150  +      } else {
          151  +        checkpoint db passive $zDb
          152  +      }
          153  +
          154  +      compare_db_hashes
          155  +    } $dblist
          156  +  }
          157  +
          158  +  # EVIDENCE-OF: R-38207-48996 If zDb is not NULL (or a zero length
          159  +  # string) and is not the name of any attached database, SQLITE_ERROR is
          160  +  # returned to the caller.
          161  +  do_test $tn.2.1 {
          162  +    list [catch { checkpoint db passive notadb } msg] $msg
          163  +  } {1 {SQLITE_ERROR - unknown database: notadb}}
          164  +
          165  +  # EVIDENCE-OF: R-14303-42483 If database zDb is the name of an attached
          166  +  # database that is not in WAL mode, SQLITE_OK is returned and both
          167  +  # *pnLog and *pnCkpt set to -1.
          168  +  #
          169  +  if {$tn==3} {
          170  +    # With sqlite3_wal_checkpoint() the two output variables cannot be 
          171  +    # tested. So just test that no error is returned when attempting to
          172  +    # checkpoint a db in rollback mode.
          173  +    do_test $tn.2.2.a { checkpoint db passive aux3 } {}
          174  +  } else {
          175  +    do_test $tn.2.2.b { checkpoint db passive aux3 } {0 -1 -1}
          176  +  }
          177  +
          178  +  # EVIDENCE-OF: R-62028-47212 All calls obtain an exclusive "checkpoint"
          179  +  # lock on the database file.
          180  +  db close
          181  +  testvfs tvfs
          182  +  tvfs filter xShmLock
          183  +  tvfs script filelock
          184  +  proc filelock {method file handle details} {
          185  +    # Test for an exclusive checkpoint lock. A checkpoint lock locks a
          186  +    # single byte starting at offset 1.
          187  +    if {$details == "1 1 lock exclusive"} { set ::seen_checkpoint_lock 1 }
          188  +  }
          189  +  sqlite3 db test.db -vfs tvfs
          190  +  do_test $tn.3.1 {
          191  +    execsql { INSERT INTO t1 VALUES('xyz') }
          192  +    unset -nocomplain ::seen_checkpoint_lock
          193  +    checkpoint db passive
          194  +    set ::seen_checkpoint_lock
          195  +  } {1}
          196  +  db close
          197  +  tvfs delete
          198  +  reset_db
          199  +
          200  +
          201  + 
          202  +
          203  +  #-----------------------------------------------------------------------
          204  +  # EVIDENCE-OF: R-10421-19736 If any other process is running a
          205  +  # checkpoint operation at the same time, the lock cannot be obtained and
          206  +  # SQLITE_BUSY is returned.
          207  +  #
          208  +  # EVIDENCE-OF: R-53820-33897 Even if there is a busy-handler configured,
          209  +  # it will not be invoked in this case.
          210  +  #
          211  +  testvfs tvfs
          212  +  tvfs filter xWrite
          213  +  sqlite3 db test.db -vfs tvfs
          214  +  sqlite3 db2 test.db -vfs tvfs
          215  +
          216  +  do_test $tn.3.2.1 {
          217  +    db2 eval {
          218  +      PRAGMA journal_mode = WAL;
          219  +      CREATE TABLE t1(x, y);
          220  +      INSERT INTO t1 VALUES(1,2);
          221  +      INSERT INTO t1 VALUES(3,4);
          222  +      INSERT INTO t1 VALUES(5,6);
          223  +    }
          224  +    file size test.db-wal
          225  +  } [wal_file_size 5 1024]
          226  +
          227  +
          228  +  # Connection [db] runs a checkpoint. During this checkpoint, each
          229  +  # time it calls xWrite() to write a page into the database file, we
          230  +  # attempt to start a checkpoint using [db2]. According to the 
          231  +  # first requirement being tested, this should return SQLITE_BUSY. According
          232  +  # to the second, the busy-handler belonging to [db2] should not be
          233  +  # invoked.
          234  +  #
          235  +  set ::write_count 0
          236  +  set ::write_errors [list]
          237  +  proc busy_callback {args} {
          238  +    lappend ::write_errors "busy handler called!"
          239  +  }
          240  +  proc write_callback {args} {
          241  +    set rc [catch {checkpoint db2 passive} msg]
          242  +    if {0==[regexp "database is locked" $msg] && $msg!="1 -1 -1"} {
          243  +      lappend ::write_errors "$rc $msg"
          244  +    } 
          245  +    incr ::write_count
          246  +  }
          247  +  db2 busy busy_callback
          248  +  tvfs script write_callback
          249  +
          250  +  do_test $tn.3.2.2 {
          251  +    db eval {SELECT * FROM sqlite_master}
          252  +    checkpoint db full
          253  +    set ::write_count
          254  +  } {2}
          255  +
          256  +  do_test $tn.3.2.3 {
          257  +    set ::write_errors
          258  +  } {}
          259  +
          260  +  db close
          261  +  db2 close
          262  +  tvfs delete
          263  +
          264  +  proc busy_handler {mode busy_handler_mode n} {
          265  +    incr ::busy_handler_counter
          266  +    switch -- $busy_handler_mode {
          267  +      1 {
          268  +        # Do nothing. Do not block.
          269  +        return 1
          270  +      }
          271  +
          272  +      2 {
          273  +        # Close first the reader, then later the writer.
          274  +        if {$n==5}  { catch {db2 eval commit} }
          275  +        if {$n==10} { catch {db3 eval commit} }
          276  +        return 0
          277  +      }
          278  +
          279  +      3 {
          280  +        # Close first the writer, then later the reader.
          281  +        if {$n==5}  { catch {db2 eval commit} }
          282  +        if {$n==10} { catch {db3 eval commit} }
          283  +        return 0
          284  +      }
          285  +    }
          286  +  }
          287  +
          288  +  foreach {mode busy_handler_mode} { 
          289  +    passive 1
          290  +    full    1
          291  +    full    2
          292  +    full    3
          293  +  } {
          294  +
          295  +    set ::sync_counter 0
          296  +
          297  +    proc tvfs_callback {method args} {
          298  +      set tail [file tail [lindex $args 0]]
          299  +      if {$method == "xSync" && $tail == "test.db"} {
          300  +        incr ::sync_counter
          301  +      }
          302  +
          303  +      if {$method == "xWrite" && $tail=="test.db"} {
          304  +        if {$::write_ok < 0} {
          305  +          set ::write_ok [expr ![catch {db5 eval { BEGIN IMMEDIATE }}]]
          306  +          catch { db5 eval ROLLBACK }
          307  +        }
          308  +        if {$::read_ok < 0} {
          309  +          set ::read_ok [expr ![catch {db5 eval { SELECT * FROM t1 }}]]
          310  +        }
          311  +      }
          312  +    }
          313  +
          314  +    catch { db close }
          315  +    forcedelete test.db
          316  +    testvfs tvfs
          317  +    sqlite3 db test.db -vfs tvfs
          318  +    #tvfs filter xSync
          319  +    tvfs script tvfs_callback
          320  +
          321  +    do_execsql_test $tn.4.$mode.0 {
          322  +      CREATE TABLE t1(a, b);
          323  +      CREATE TABLE t2(a, b);
          324  +      PRAGMA journal_mode = wal;
          325  +      INSERT INTO t1 VALUES(1, 2);
          326  +      INSERT INTO t1 VALUES(3, 4);
          327  +      INSERT INTO t1 VALUES(5, 6);
          328  +    } {wal}
          329  +
          330  +    # Open a reader on the current database snapshot.
          331  +    do_test $tn.4.$mode.1 {
          332  +      sqlite3 db2 test.db -vfs tvfs
          333  +      execsql {
          334  +        BEGIN;
          335  +          SELECT * FROM t1 UNION ALL SELECT * FROM t2;
          336  +      } db2
          337  +    } {1 2 3 4 5 6}
          338  +
          339  +    # Open a writer. Write a transaction. Then begin, but do not commit,
          340  +    # a second transaction.
          341  +    do_test $tn.4.$mode.2 {
          342  +      sqlite3 db3 test.db -vfs tvfs
          343  +      execsql {
          344  +        INSERT INTO t2 VALUES(7, 8);
          345  +        BEGIN;
          346  +          INSERT INTO t2 VALUES(9, 10);
          347  +          SELECT * FROM t1 UNION ALL SELECT * FROM t2;
          348  +      } db3
          349  +    } {1 2 3 4 5 6 7 8 9 10}
          350  +
          351  +    sqlite3 db5 test.db -vfs tvfs
          352  +
          353  +    # Register a busy-handler with connection [db].
          354  +    #
          355  +    db busy [list busy_handler $mode $busy_handler_mode]
          356  +    set ::sync_counter 0
          357  +    set ::busy_handler_counter 0
          358  +    set ::read_ok -1
          359  +    set ::write_ok -1
          360  +    
          361  +    do_test $tn.4.$mode.3 {
          362  +      checkpoint db $mode main
          363  +      set {} {}
          364  +    } {}
          365  +
          366  +    if { $mode=="passive" } {
          367  +      # EVIDENCE-OF: R-16333-64433 Checkpoint as many frames as possible
          368  +      # without waiting for any database readers or writers to finish, then
          369  +      # sync the database file if all frames in the log were checkpointed.
          370  +      #
          371  +      #   "As many frames as possible" means all but the last two transactions
          372  +      #   (the two that write to table t2, of which the scond is unfinished).
          373  +      #   So copying the db file only we see the t1 change, but not the t2
          374  +      #   modifications.
          375  +      #
          376  +      #   The busy handler is not invoked (see below) and the db reader and
          377  +      #   writer are still active - so the checkpointer did not wait for either
          378  +      #   readers or writers. As a result the checkpoint was not finished and
          379  +      #   so the db file is not synced.
          380  +      #
          381  +      # EVIDENCE-OF: R-62920-47450 The busy-handler callback is never invoked
          382  +      # in the SQLITE_CHECKPOINT_PASSIVE mode.
          383  +      #
          384  +      #   It's not. Test case "$tn.4.$mode.6".
          385  +      #
          386  +      do_test $tn.4.$mode.4 {
          387  +        forcecopy test.db abc.db
          388  +        sqlite3 db4 abc.db
          389  +        db4 eval { SELECT * FROM t1 UNION ALL SELECT * FROM t2 }
          390  +      } {1 2 3 4 5 6}
          391  +      do_test $tn.4.$mode.5 { set ::sync_counter } 0
          392  +      do_test $tn.4.$mode.6 { set ::busy_handler_counter } 0
          393  +      db4 close
          394  +  
          395  +      db2 eval COMMIT
          396  +      db3 eval COMMIT
          397  +  
          398  +      # EVIDENCE-OF: R-65499-53765 On the other hand, passive mode might leave
          399  +      # the checkpoint unfinished if there are concurrent readers or writers.
          400  +      #
          401  +      #   The reader and writer have now dropped their locks. And so a 
          402  +      #   checkpoint now is able to checkpoint more frames. Showing that the
          403  +      #   attempt above was left "unfinished".
          404  +      #
          405  +      #   Also, because the checkpoint finishes this time, the db is synced.
          406  +      #   Which is part of R-16333-64433 above.
          407  +      #
          408  +      do_test $tn.4.$mode.7 {
          409  +        checkpoint db $mode main
          410  +        forcecopy test.db abc.db
          411  +        sqlite3 db4 abc.db
          412  +        db4 eval { SELECT * FROM t1 UNION ALL SELECT * FROM t2 }
          413  +      } {1 2 3 4 5 6 7 8 9 10}
          414  +      do_test $tn.4.$mode.6 { set ::sync_counter } 1
          415  +      do_test $tn.4.$mode.7 { set ::busy_handler_counter } 0
          416  +      db4 close
          417  +    }
          418  +
          419  +    if { $mode=="full" } {
          420  +      if {$busy_handler_mode==2 || $busy_handler_mode==3} {
          421  +        # EVIDENCE-OF: R-59171-47567 This mode blocks (it invokes the
          422  +        # busy-handler callback) until there is no database writer and all
          423  +        # readers are reading from the most recent database snapshot.
          424  +        #
          425  +        #   Show that both the reader and writer have finished:
          426  +        #
          427  +        do_test $tn.4.$mode.7 {
          428  +          list [catchsql COMMIT db2] [catchsql COMMIT db3]
          429  +        } [list                                             \
          430  +            {1 {cannot commit - no transaction is active}}  \
          431  +            {1 {cannot commit - no transaction is active}}  \
          432  +        ]
          433  +
          434  +        # EVIDENCE-OF: R-29177-48281 It then checkpoints all frames in the log
          435  +        # file and syncs the database file.
          436  +        #
          437  +        do_test $tn.4.$mode.8 {
          438  +          forcecopy test.db abc.db
          439  +          sqlite3 db4 abc.db
          440  +          db4 eval { SELECT * FROM t1 UNION ALL SELECT * FROM t2 }
          441  +        } {1 2 3 4 5 6 7 8 9 10}
          442  +        do_test $tn.4.$mode.9 { set ::sync_counter } 1
          443  +        db4 close
          444  +
          445  +        # EVIDENCE-OF: R-51867-44713 This mode blocks new database writers
          446  +        # while it is pending, but new database readers are allowed to continue
          447  +        # unimpeded.
          448  +        do_test $tn.4.$mode.10 {
          449  +          list $::write_ok $::read_ok
          450  +        } {0 1}
          451  +
          452  +      }
          453  +    }
          454  +
          455  +    db2 close
          456  +    db3 close
          457  +    db5 close
          458  +  }
          459  +
          460  +  db close
          461  +  tvfs delete
          462  +}
          463  +
          464  +#-----------------------------------------------------------------------
          465  +# EVIDENCE-OF: R-03996-12088 The M parameter must be a valid checkpoint
          466  +# mode:
          467  +#
          468  +#   Valid checkpoint modes are 0, 1, 2 and 3.
          469  +#
          470  +sqlite3 db test.db
          471  +foreach {tn mode res} {
          472  +  0 -1001    {1 {SQLITE_MISUSE - not an error}}
          473  +  1 -1       {1 {SQLITE_MISUSE - not an error}}
          474  +  2  0       {0 {0 -1 -1}}
          475  +  3  1       {0 {0 -1 -1}}
          476  +  4  2       {0 {0 -1 -1}}
          477  +  5  3       {0 {0 -1 -1}}
          478  +  6  4       {1 {SQLITE_MISUSE - not an error}}
          479  +  7  114     {1 {SQLITE_MISUSE - not an error}}
          480  +  8  1000000 {1 {SQLITE_MISUSE - not an error}}
          481  +} {
          482  +  do_test 4.$tn {
          483  +    list [catch "sqlite3_wal_checkpoint_v2 db $mode" msg] $msg
          484  +  } $res
          485  +}
          486  +
          487  +
          488  +finish_test

Changes to test/threadtest3.c.

  1389   1389     launch_thread(&err, &threads, dynamic_triggers_2, 0);
  1390   1390     launch_thread(&err, &threads, dynamic_triggers_1, 0);
  1391   1391   
  1392   1392     join_all_threads(&err, &threads);
  1393   1393   
  1394   1394     print_and_free_err(&err);
  1395   1395   }
         1396  +
         1397  +
  1396   1398   
  1397   1399   #include "tt3_checkpoint.c"
         1400  +#include "tt3_index.c"
  1398   1401   
  1399   1402   int main(int argc, char **argv){
  1400   1403     struct ThreadTest {
  1401   1404       void (*xTest)(int);
  1402   1405       const char *zTest;
  1403   1406       int nMs;
  1404   1407     } aTest[] = {
................................................................................
  1410   1413       { walthread5, "walthread5",  1000 },
  1411   1414       
  1412   1415       { cgt_pager_1,      "cgt_pager_1", 0 },
  1413   1416       { dynamic_triggers, "dynamic_triggers", 20000 },
  1414   1417   
  1415   1418       { checkpoint_starvation_1, "checkpoint_starvation_1", 10000 },
  1416   1419       { checkpoint_starvation_2, "checkpoint_starvation_2", 10000 },
         1420  +
         1421  +    { create_drop_index_1, "create_drop_index_1", 10000 },
  1417   1422     };
  1418   1423   
  1419   1424     int i;
  1420   1425     char *zTest = 0;
  1421   1426     int nTest = 0;
  1422   1427     int bTestfound = 0;
  1423   1428     int bPrefix = 0;

Added test/tt3_index.c.

            1  +/*
            2  +** 2014 December 9
            3  +**
            4  +** The author disclaims copyright to this source code.  In place of
            5  +** a legal notice, here is a blessing:
            6  +**
            7  +**    May you do good and not evil.
            8  +**    May you find forgiveness for yourself and forgive others.
            9  +**    May you share freely, never taking more than you give.
           10  +**
           11  +*************************************************************************
           12  +**
           13  +**     create_drop_index_1
           14  +*/
           15  +
           16  +
           17  +static char *create_drop_index_thread(int iTid, int iArg){
           18  +  Error err = {0};                /* Error code and message */
           19  +  Sqlite db = {0};                /* SQLite database connection */
           20  +
           21  +  while( !timetostop(&err) ){
           22  +    opendb(&err, &db, "test.db", 0);
           23  +
           24  +    sql_script(&err, &db, 
           25  +
           26  +      "DROP INDEX IF EXISTS i1;"
           27  +      "DROP INDEX IF EXISTS i2;"
           28  +      "DROP INDEX IF EXISTS i3;"
           29  +      "DROP INDEX IF EXISTS i4;"
           30  +
           31  +      "CREATE INDEX IF NOT EXISTS i1 ON t1(a);"
           32  +      "CREATE INDEX IF NOT EXISTS i2 ON t1(b);"
           33  +      "CREATE INDEX IF NOT EXISTS i3 ON t1(c);"
           34  +      "CREATE INDEX IF NOT EXISTS i4 ON t1(d);"
           35  +
           36  +      "SELECT * FROM t1 ORDER BY a;"
           37  +      "SELECT * FROM t1 ORDER BY b;"
           38  +      "SELECT * FROM t1 ORDER BY c;"
           39  +      "SELECT * FROM t1 ORDER BY d;"
           40  +    );
           41  +
           42  +    closedb(&err, &db);
           43  +  }
           44  +
           45  +  print_and_free_err(&err);
           46  +  return sqlite3_mprintf("ok");
           47  +}
           48  +
           49  +static void create_drop_index_1(int nMs){
           50  +  Error err = {0};
           51  +  Sqlite db = {0};
           52  +  Threadset threads = {0};
           53  +
           54  +  opendb(&err, &db, "test.db", 1);
           55  +  sql_script(&err, &db, 
           56  +     "CREATE TABLE t1(a, b, c, d);"
           57  +     "WITH data(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM data WHERE x<100) "
           58  +     "INSERT INTO t1 SELECT x,x,x,x FROM data;"
           59  +  );
           60  +  closedb(&err, &db);
           61  +
           62  +  setstoptime(&err, nMs);
           63  +
           64  +  sqlite3_enable_shared_cache(1);
           65  +  launch_thread(&err, &threads, create_drop_index_thread, 0);
           66  +  launch_thread(&err, &threads, create_drop_index_thread, 0);
           67  +  launch_thread(&err, &threads, create_drop_index_thread, 0);
           68  +  launch_thread(&err, &threads, create_drop_index_thread, 0);
           69  +  launch_thread(&err, &threads, create_drop_index_thread, 0);
           70  +  sqlite3_enable_shared_cache(0);
           71  +
           72  +  join_all_threads(&err, &threads);
           73  +  print_and_free_err(&err);
           74  +}