/ Check-in [32bb2d58]
Login

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

Overview
Comment:Fix multiple performance regressions (ticket #2298 among them) and add tests to make sure they do not come back. (CVS 3839)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 32bb2d5859906b4fb0f6083eedd7f3a81b9cf5e2
User & Date: drh 2007-04-13 02:14:30
Context
2007-04-13
03:23
Additional tests designed to detect future performance regressions. (CVS 3840) check-in: 764e7262 user: drh tags: trunk
02:14
Fix multiple performance regressions (ticket #2298 among them) and add tests to make sure they do not come back. (CVS 3839) check-in: 32bb2d58 user: drh tags: trunk
2007-04-12
21:25
Changes toward fixes for tickets #2296 and #2291. (CVS 3838) check-in: 0dd3e2e4 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/btree.c.

     5      5   ** a legal notice, here is a blessing:
     6      6   **
     7      7   **    May you do good and not evil.
     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12         -** $Id: btree.c,v 1.354 2007/04/09 12:45:03 drh Exp $
           12  +** $Id: btree.c,v 1.355 2007/04/13 02:14:30 drh Exp $
    13     13   **
    14     14   ** This file implements a external (disk-based) database using BTrees.
    15     15   ** For a detailed discussion of BTrees, refer to
    16     16   **
    17     17   **     Donald E. Knuth, THE ART OF COMPUTER PROGRAMMING, Volume 3:
    18     18   **     "Sorting And Searching", pages 473-480. Addison-Wesley
    19     19   **     Publishing Company, Reading, Massachusetts.
................................................................................
  1392   1392     pPage->nCell = 0;
  1393   1393     pPage->isInit = 1;
  1394   1394   }
  1395   1395   
  1396   1396   /*
  1397   1397   ** Get a page from the pager.  Initialize the MemPage.pBt and
  1398   1398   ** MemPage.aData elements if needed.
         1399  +**
         1400  +** If the noContent flag is set, it means that we do not care about
         1401  +** the content of the page at this time.  So do not go to the disk
         1402  +** to fetch the content.  Just fill in the content with zeros for now.
         1403  +** If in the future we call sqlite3PagerWrite() on this page, that
         1404  +** means we have started to be concerned about content and the disk
         1405  +** read should occur at that point.
  1399   1406   */
  1400         -static int getPage(BtShared *pBt, Pgno pgno, MemPage **ppPage, int clrFlag){
         1407  +static int getPage(BtShared *pBt, Pgno pgno, MemPage **ppPage, int noContent){
  1401   1408     int rc;
  1402   1409     MemPage *pPage;
  1403   1410     DbPage *pDbPage;
  1404   1411   
  1405         -  rc = sqlite3PagerAcquire(pBt->pPager, pgno, (DbPage**)&pDbPage, clrFlag);
         1412  +  rc = sqlite3PagerAcquire(pBt->pPager, pgno, (DbPage**)&pDbPage, noContent);
  1406   1413     if( rc ) return rc;
  1407   1414     pPage = (MemPage *)sqlite3PagerGetExtra(pDbPage);
  1408   1415     pPage->aData = sqlite3PagerGetData(pDbPage);
  1409   1416     pPage->pDbPage = pDbPage;
  1410   1417     pPage->pBt = pBt;
  1411   1418     pPage->pgno = pgno;
  1412   1419     pPage->hdrOffset = pPage->pgno==1 ? 100 : 0;
  1413   1420     *ppPage = pPage;
  1414         -  if( clrFlag ){
  1415         -    sqlite3PagerDontRollback(pPage->pDbPage);
  1416         -  }
  1417   1421     return SQLITE_OK;
  1418   1422   }
  1419   1423   
  1420   1424   /*
  1421   1425   ** Get a page from the pager and initialize it.  This routine
  1422   1426   ** is just a convenience wrapper around separate calls to
  1423   1427   ** getPage() and initPage().
................................................................................
  3826   3830                    *pPgno, closest+1, k, pTrunk->pgno, n-1));
  3827   3831             if( closest<k-1 ){
  3828   3832               memcpy(&aData[8+closest*4], &aData[4+k*4], 4);
  3829   3833             }
  3830   3834             put4byte(&aData[4], k-1);
  3831   3835             rc = getPage(pBt, *pPgno, ppPage, 1);
  3832   3836             if( rc==SQLITE_OK ){
         3837  +            sqlite3PagerDontRollback((*ppPage)->pDbPage);
  3833   3838               rc = sqlite3PagerWrite((*ppPage)->pDbPage);
  3834   3839               if( rc!=SQLITE_OK ){
  3835   3840                 releasePage(*ppPage);
  3836   3841               }
  3837   3842             }
  3838   3843             searchList = 0;
  3839   3844           }
................................................................................
  3944   3949       }else{
  3945   3950         /* Add the newly freed page as a leaf on the current trunk */
  3946   3951         rc = sqlite3PagerWrite(pTrunk->pDbPage);
  3947   3952         if( rc==SQLITE_OK ){
  3948   3953           put4byte(&pTrunk->aData[4], k+1);
  3949   3954           put4byte(&pTrunk->aData[8+k*4], pPage->pgno);
  3950   3955   #ifndef SQLITE_SECURE_DELETE
  3951         -        sqlite3PagerDontWrite(pBt->pPager, pPage->pgno);
         3956  +        sqlite3PagerDontWrite(pPage->pDbPage);
  3952   3957   #endif
  3953   3958         }
  3954   3959         TRACE(("FREE-PAGE: %d leaf on trunk page %d\n",pPage->pgno,pTrunk->pgno));
  3955   3960       }
  3956   3961       releasePage(pTrunk);
  3957   3962     }
  3958   3963     return rc;
................................................................................
  3978   3983     nOvfl = (info.nPayload - info.nLocal + ovflPageSize - 1)/ovflPageSize;
  3979   3984     assert( ovflPgno==0 || nOvfl>0 );
  3980   3985     while( nOvfl-- ){
  3981   3986       MemPage *pOvfl;
  3982   3987       if( ovflPgno==0 || ovflPgno>sqlite3PagerPagecount(pBt->pPager) ){
  3983   3988         return SQLITE_CORRUPT_BKPT;
  3984   3989       }
  3985         -    rc = getPage(pBt, ovflPgno, &pOvfl, 0);
         3990  +    rc = getPage(pBt, ovflPgno, &pOvfl, nOvfl==0);
  3986   3991       if( rc ) return rc;
  3987   3992       if( nOvfl ){
  3988   3993         ovflPgno = get4byte(pOvfl->aData);
  3989   3994       }
  3990   3995       rc = freePage(pOvfl);
  3991   3996       sqlite3PagerUnref(pOvfl->pDbPage);
  3992   3997       if( rc ) return rc;
................................................................................
  6557   6562       DbPage *pDbPage;
  6558   6563       if( i==iSkip ) continue;
  6559   6564       rc = sqlite3PagerGet(pBtFrom->pPager, i, &pDbPage);
  6560   6565       if( rc ) break;
  6561   6566       rc = sqlite3PagerOverwrite(pBtTo->pPager, i, sqlite3PagerGetData(pDbPage));
  6562   6567       sqlite3PagerUnref(pDbPage);
  6563   6568     }
         6569  +
         6570  +  /* If the file is shrinking, journal the pages that are being truncated
         6571  +  ** so that they can be rolled back if the commit fails.
         6572  +  */
  6564   6573     for(i=nPage+1; rc==SQLITE_OK && i<=nToPage; i++){
  6565   6574       DbPage *pDbPage;
  6566   6575       if( i==iSkip ) continue;
  6567   6576       rc = sqlite3PagerGet(pBtTo->pPager, i, &pDbPage);
  6568   6577       if( rc ) break;
  6569   6578       rc = sqlite3PagerWrite(pDbPage);
         6579  +    sqlite3PagerDontWrite(pDbPage);
         6580  +    /* Yeah.  It seems wierd to call DontWrite() right after Write().  But
         6581  +    ** that is because the names of those procedures do not exactly 
         6582  +    ** represent what they do.  Write() really means "put this page in the
         6583  +    ** rollback journal and mark it as dirty so that it will be written
         6584  +    ** to the database file later."  DontWrite() undoes the second part of
         6585  +    ** that and prevents the page from being written to the database.  The
         6586  +    ** page is still on the rollback journal, though.  And that is the whole
         6587  +    ** point of this loop: to put pages on the rollback journal. */
  6570   6588       sqlite3PagerUnref(pDbPage);
  6571         -    sqlite3PagerDontWrite(pBtTo->pPager, i);
  6572   6589     }
  6573   6590     if( !rc && nPage<nToPage ){
  6574   6591       rc = sqlite3PagerTruncate(pBtTo->pPager, nPage);
  6575   6592     }
         6593  +
  6576   6594     if( rc ){
  6577   6595       sqlite3BtreeRollback(pTo);
  6578   6596     }
  6579   6597     return rc;  
  6580   6598   }
  6581   6599   #endif /* SQLITE_OMIT_VACUUM */
  6582   6600   

Changes to src/os_common.h.

    93     93   int sqlite3_diskfull = 0;
    94     94   #define SimulateIOError(CODE)  \
    95     95     if( sqlite3_io_error_pending || sqlite3_io_error_hit ) \
    96     96        if( sqlite3_io_error_pending-- == 1 \
    97     97            || (sqlite3_io_error_persist && sqlite3_io_error_hit) ) \
    98     98                   { local_ioerr(); CODE; }
    99     99   static void local_ioerr(){
          100  +  IOTRACE(("IOERR\n"));
   100    101     sqlite3_io_error_hit = 1;
   101    102   }
   102    103   #define SimulateDiskfullError(CODE) \
   103    104      if( sqlite3_diskfull_pending ){ \
   104    105        if( sqlite3_diskfull_pending == 1 ){ \
   105    106          local_ioerr(); \
   106    107          sqlite3_diskfull = 1; \

Changes to src/pager.c.

    14     14   ** The pager is used to access a database disk file.  It implements
    15     15   ** atomic commit and rollback through the use of a journal file that
    16     16   ** is separate from the database file.  The pager also implements file
    17     17   ** locking to prevent two processes from writing the same database
    18     18   ** file simultaneously, or one process from reading the database while
    19     19   ** another is writing.
    20     20   **
    21         -** @(#) $Id: pager.c,v 1.326 2007/04/09 11:20:54 danielk1977 Exp $
           21  +** @(#) $Id: pager.c,v 1.327 2007/04/13 02:14:30 drh Exp $
    22     22   */
    23     23   #ifndef SQLITE_OMIT_DISKIO
    24     24   #include "sqliteInt.h"
    25     25   #include "os.h"
    26     26   #include "pager.h"
    27     27   #include <assert.h>
    28     28   #include <string.h>
................................................................................
   158    158     PgHdr *pNextHash, *pPrevHash;  /* Hash collision chain for PgHdr.pgno */
   159    159     PgHdr *pNextFree, *pPrevFree;  /* Freelist of pages where nRef==0 */
   160    160     PgHdr *pNextAll;               /* A list of all pages */
   161    161     u8 inJournal;                  /* TRUE if has been written to journal */
   162    162     u8 dirty;                      /* TRUE if we need to write back changes */
   163    163     u8 needSync;                   /* Sync journal before writing this page */
   164    164     u8 alwaysRollback;             /* Disable DontRollback() for this page */
          165  +  u8 needRead;                   /* Read content if PagerWrite() is called */
   165    166     short int nRef;                /* Number of users of this page */
   166    167     PgHdr *pDirty, *pPrevDirty;    /* Dirty pages */
   167    168     u32 notUsed;                   /* Buffer space */
   168    169   #ifdef SQLITE_CHECK_PAGES
   169    170     u32 pageHash;
   170    171   #endif
   171    172     /* pPager->pageSize bytes of page data follow this header */
................................................................................
   293    294     Pager *pNext;               /* Linked list of pagers in this thread */
   294    295   #endif
   295    296     char *pTmpSpace;            /* Pager.pageSize bytes of space for tmp use */
   296    297     u32 iChangeCount;           /* Db change-counter for which cache is valid */
   297    298   };
   298    299   
   299    300   /*
   300         -** If SQLITE_TEST is defined then increment the variable given in
   301         -** the argument
          301  +** The following global variables hold counters used for
          302  +** testing purposes only.  These variables do not exist in
          303  +** a non-testing build.  These variables are not thread-safe.
   302    304   */
   303    305   #ifdef SQLITE_TEST
   304         -# define TEST_INCR(x)  x++
          306  +int sqlite3_pager_readdb_count = 0;    /* Number of full pages read from DB */
          307  +int sqlite3_pager_writedb_count = 0;   /* Number of full pages written to DB */
          308  +int sqlite3_pager_writej_count = 0;    /* Number of pages written to journal */
          309  +int sqlite3_pager_pgfree_count = 0;    /* Number of cache pages freed */
          310  +# define PAGER_INCR(v)  v++
   305    311   #else
   306         -# define TEST_INCR(x)
          312  +# define PAGER_INCR(v)
   307    313   #endif
          314  +
          315  +
   308    316   
   309    317   /*
   310    318   ** Journal files begin with the following magic string.  The data
   311    319   ** was obtained from /dev/random.  It is used only as a sanity check.
   312    320   **
   313    321   ** Since version 2.8.0, the journal format contains additional sanity
   314    322   ** checking information.  If the power fails while the journal is begin
................................................................................
   894    902   ** opened.  Any outstanding pages are invalidated and subsequent attempts
   895    903   ** to access those pages will likely result in a coredump.
   896    904   */
   897    905   static void pager_reset(Pager *pPager){
   898    906     PgHdr *pPg, *pNext;
   899    907     if( pPager->errCode ) return;
   900    908     for(pPg=pPager->pAll; pPg; pPg=pNext){
          909  +    IOTRACE(("PGFREE %p %d\n", pPager, pPg->pgno));
          910  +    PAGER_INCR(sqlite3_pager_pgfree_count);
   901    911       pNext = pPg->pNextAll;
   902    912       sqliteFree(pPg);
   903    913     }
   904    914     pPager->pStmt = 0;
   905    915     pPager->pFirst = 0;
   906    916     pPager->pFirstSynced = 0;
   907    917     pPager->pLast = 0;
................................................................................
  1215   1225     }  
  1216   1226     if( master_open ){
  1217   1227       sqlite3OsClose(&master);
  1218   1228     }
  1219   1229     return rc;
  1220   1230   }
  1221   1231   
  1222         -#if 0
  1223         -/*
  1224         -** Make every page in the cache agree with what is on disk.  In other words,
  1225         -** reread the disk to reset the state of the cache.
  1226         -**
  1227         -** This routine is called after a rollback in which some of the dirty cache
  1228         -** pages had never been written out to disk.  We need to roll back the
  1229         -** cache content and the easiest way to do that is to reread the old content
  1230         -** back from the disk.
  1231         -*/
  1232         -static int pager_reload_cache(Pager *pPager){
  1233         -  PgHdr *pPg;
  1234         -  int rc = SQLITE_OK;
  1235         -  for(pPg=pPager->pAll; pPg; pPg=pPg->pNextAll){
  1236         -    char *zBuf = pPager->pTmpSpace;        /* Temp storage for one page */
  1237         -    if( !pPg->dirty ) continue;
  1238         -    if( (int)pPg->pgno <= pPager->origDbSize ){
  1239         -      rc = sqlite3OsSeek(pPager->fd, pPager->pageSize*(i64)(pPg->pgno-1));
  1240         -      if( rc==SQLITE_OK ){
  1241         -        rc = sqlite3OsRead(pPager->fd, zBuf, pPager->pageSize);
  1242         -      }
  1243         -      PAGERTRACE3("REFETCH %d page %d\n", PAGERID(pPager), pPg->pgno);
  1244         -      if( rc ) break;
  1245         -      CODEC1(pPager, zBuf, pPg->pgno, 2);
  1246         -    }else{
  1247         -      memset(zBuf, 0, pPager->pageSize);
  1248         -    }
  1249         -    if( pPg->nRef==0 || memcmp(zBuf, PGHDR_TO_DATA(pPg), pPager->pageSize) ){
  1250         -      memcpy(PGHDR_TO_DATA(pPg), zBuf, pPager->pageSize);
  1251         -      if( pPager->xReiniter ){
  1252         -        pPager->xReiniter(pPg, pPager->pageSize);
  1253         -      }else{
  1254         -        memset(PGHDR_TO_EXTRA(pPg, pPager), 0, pPager->nExtra);
  1255         -      }
  1256         -    }
  1257         -    pPg->needSync = 0;
  1258         -    pPg->dirty = 0;
  1259         -#ifdef SQLITE_CHECK_PAGES
  1260         -    pPg->pageHash = pager_pagehash(pPg);
  1261         -#endif
  1262         -  }
  1263         -  pPager->pDirty = 0;
  1264         -  return rc;
  1265         -}
  1266         -#endif
  1267   1232   
  1268   1233   static void pager_truncate_cache(Pager *pPager);
  1269   1234   
  1270   1235   /*
  1271   1236   ** Truncate the main file of the given pager to the number of pages
  1272   1237   ** indicated. Also truncate the cached representation of the file.
  1273   1238   */
................................................................................
  2045   2010       if( pPg->pgno<=dbSize ){
  2046   2011         ppPg = &pPg->pNextAll;
  2047   2012       }else if( pPg->nRef>0 ){
  2048   2013         memset(PGHDR_TO_DATA(pPg), 0, pPager->pageSize);
  2049   2014         ppPg = &pPg->pNextAll;
  2050   2015       }else{
  2051   2016         *ppPg = pPg->pNextAll;
         2017  +      IOTRACE(("PGFREE %p %d\n", pPager, pPg->pgno));
         2018  +      PAGER_INCR(sqlite3_pager_pgfree_count);
  2052   2019         unlinkPage(pPg);
  2053   2020         makeClean(pPg);
  2054   2021         sqliteFree(pPg);
  2055   2022         pPager->nPage--;
  2056   2023       }
  2057   2024     }
  2058   2025   }
................................................................................
  2466   2433       ** than Pager.dbSize, this means sqlite3PagerTruncate() was called to
  2467   2434       ** make the file smaller (presumably by auto-vacuum code). Do not write
  2468   2435       ** any such pages to the file.
  2469   2436       */
  2470   2437       if( pList->pgno<=pPager->dbSize ){
  2471   2438         char *pData = CODEC2(pPager, PGHDR_TO_DATA(pList), pList->pgno, 6);
  2472   2439         PAGERTRACE3("STORE %d page %d\n", PAGERID(pPager), pList->pgno);
  2473         -      IOTRACE(("PGOUT %p %d\n", pPager, pList->pgno))
         2440  +      IOTRACE(("PGOUT %p %d\n", pPager, pList->pgno));
  2474   2441         rc = sqlite3OsWrite(pPager->fd, pData, pPager->pageSize);
  2475         -      TEST_INCR(pPager->nWrite);
         2442  +      PAGER_INCR(sqlite3_pager_writedb_count);
         2443  +      PAGER_INCR(pPager->nWrite);
  2476   2444       }
  2477   2445   #ifndef NDEBUG
  2478   2446       else{
  2479   2447         PAGERTRACE3("NOSTORE %d page %d\n", PAGERID(pPager), pList->pgno);
  2480   2448       }
  2481   2449   #endif
  2482   2450       if( rc ) return rc;
................................................................................
  2670   2638           if( pPg==pPager->pAll ){
  2671   2639              pPager->pAll = pPg->pNextAll;
  2672   2640           }else{
  2673   2641             for( pTmp=pPager->pAll; pTmp->pNextAll!=pPg; pTmp=pTmp->pNextAll ){}
  2674   2642             pTmp->pNextAll = pPg->pNextAll;
  2675   2643           }
  2676   2644           nReleased += sqliteAllocSize(pPg);
         2645  +        IOTRACE(("PGFREE %p %d\n", pPager, pPg->pgno));
         2646  +        PAGER_INCR(sqlite3_pager_pgfree_count);
  2677   2647           sqliteFree(pPg);
  2678   2648         }
  2679   2649   
  2680   2650         if( rc!=SQLITE_OK ){
  2681   2651           /* An error occured whilst writing to the database file or 
  2682   2652           ** journal in pager_recycle(). The error is not returned to the 
  2683   2653           ** caller of this function. Instead, set the Pager.errCode variable.
................................................................................
  2702   2672     int rc;
  2703   2673     assert( MEMDB==0 );
  2704   2674     rc = sqlite3OsSeek(pPager->fd, (pgno-1)*(i64)pPager->pageSize);
  2705   2675     if( rc==SQLITE_OK ){
  2706   2676       rc = sqlite3OsRead(pPager->fd, PGHDR_TO_DATA(pPg),
  2707   2677                             pPager->pageSize);
  2708   2678     }
  2709         -  IOTRACE(("PGIN %p %d\n", pPager, pgno))
         2679  +  PAGER_INCR(sqlite3_pager_readdb_count);
         2680  +  PAGER_INCR(pPager->nRead);
         2681  +  IOTRACE(("PGIN %p %d\n", pPager, pgno));
  2710   2682     PAGERTRACE3("FETCH %d page %d\n", PAGERID(pPager), pPg->pgno);
  2711   2683     CODEC1(pPager, PGHDR_TO_DATA(pPg), pPg->pgno, 3);
  2712   2684     return rc;
  2713   2685   }
  2714   2686   
  2715   2687   
  2716   2688   /*
................................................................................
  2835   2807               return rc;
  2836   2808             }
  2837   2809             iChangeCounter = (zC[0]<<24) + (zC[1]<<16) + (zC[2]<<8) + zC[3];
  2838   2810           }
  2839   2811   
  2840   2812           if( iChangeCounter!=pPager->iChangeCount ){
  2841   2813             pager_reset(pPager);
         2814  +          pPager->iChangeCount = iChangeCounter;
  2842   2815           }
  2843   2816         }
  2844   2817       }
  2845   2818       assert( pPager->exclusiveMode || pPager->state<=PAGER_SHARED );
  2846   2819       if( pPager->state==PAGER_UNLOCK ){
  2847   2820         pPager->state = PAGER_SHARED;
  2848   2821       }
................................................................................
  2955   2928   ** to find a page in the in-memory cache first.  If the page is not already
  2956   2929   ** in memory, this routine goes to disk to read it in whereas _lookup()
  2957   2930   ** just returns 0.  This routine acquires a read-lock the first time it
  2958   2931   ** has to go to disk, and could also playback an old journal if necessary.
  2959   2932   ** Since _lookup() never goes to disk, it never has to deal with locks
  2960   2933   ** or journal files.
  2961   2934   **
  2962         -** If clrFlag is false, the page contents are actually read from disk.
  2963         -** If clfFlag is true, it means the page is about to be erased and
  2964         -** rewritten without first being read so there is no point it doing
  2965         -** the disk I/O.
         2935  +** If noContent is false, the page contents are actually read from disk.
         2936  +** If noContent is true, it means that we do not care about the contents
         2937  +** of the page at this time, so do not do a disk read.  Just fill in the
         2938  +** page content with zeros.  But mark the fact that we have not read the
         2939  +** content by setting the PgHdr.needRead flag.  Later on, if 
         2940  +** sqlite3PagerWrite() is called on this page, that means that the
         2941  +** content is needed and the disk read should occur at that point.
  2966   2942   */
  2967         -int sqlite3PagerAcquire(Pager *pPager, Pgno pgno, DbPage **ppPage, int clrFlag){
         2943  +int sqlite3PagerAcquire(
         2944  +  Pager *pPager,      /* The pager open on the database file */
         2945  +  Pgno pgno,          /* Page number to fetch */
         2946  +  DbPage **ppPage,    /* Write a pointer to the page here */
         2947  +  int noContent       /* Do not bother reading content from disk if true */
         2948  +){
  2968   2949     PgHdr *pPg;
  2969   2950     int rc;
  2970   2951   
  2971   2952     assert( pPager->state==PAGER_UNLOCK || pPager->nRef>0 || pgno==1 );
  2972   2953   
  2973   2954     /* The maximum page number is 2^31. Return SQLITE_CORRUPT if a page
  2974   2955     ** number greater than this, or zero, is requested.
................................................................................
  2996   2977     assert( pPager->state!=PAGER_UNLOCK );
  2997   2978   
  2998   2979     pPg = pager_lookup(pPager, pgno);
  2999   2980     if( pPg==0 ){
  3000   2981       /* The requested page is not in the page cache. */
  3001   2982       int nMax;
  3002   2983       int h;
  3003         -    TEST_INCR(pPager->nMiss);
         2984  +    PAGER_INCR(pPager->nMiss);
  3004   2985       rc = pagerAllocatePage(pPager, &pPg);
  3005   2986       if( rc!=SQLITE_OK ){
  3006   2987         return rc;
  3007   2988       }
  3008   2989   
  3009   2990       pPg->pgno = pgno;
  3010   2991       assert( !MEMDB || pgno>pPager->stmtSize );
................................................................................
  3032   3013         rc = pPager->errCode;
  3033   3014         return rc;
  3034   3015       }
  3035   3016   
  3036   3017       /* Populate the page with data, either by reading from the database
  3037   3018       ** file, or by setting the entire page to zero.
  3038   3019       */
  3039         -    if( nMax<(int)pgno || MEMDB || (clrFlag && !pPager->alwaysRollback) ){
         3020  +    if( nMax<(int)pgno || MEMDB || noContent ){
  3040   3021         memset(PGHDR_TO_DATA(pPg), 0, pPager->pageSize);
         3022  +      pPg->needRead = noContent;
         3023  +      IOTRACE(("ZERO %p %d\n", pPager, pgno));
  3041   3024       }else{
  3042   3025         rc = readDbPage(pPager, pPg, pgno);
  3043   3026         if( rc!=SQLITE_OK && rc!=SQLITE_IOERR_SHORT_READ ){
  3044   3027           pPg->pgno = 0;
  3045   3028           sqlite3PagerUnref(pPg);
  3046   3029           return rc;
  3047         -      }else{
  3048         -        TEST_INCR(pPager->nRead);
  3049   3030         }
         3031  +    }
         3032  +    /* If this was page 1, then restore the value of Pager.iChangeCount */
         3033  +    if( pgno==1 ){
         3034  +      pPager->iChangeCount = retrieve32bits(pPg, 24);
  3050   3035       }
  3051   3036   
  3052   3037       /* Link the page into the page hash table */
  3053   3038       h = pgno & (pPager->nHash-1);
  3054   3039       assert( pgno!=0 );
  3055   3040       pPg->pNextHash = pPager->aHash[h];
  3056   3041       pPager->aHash[h] = pPg;
................................................................................
  3061   3046   
  3062   3047   #ifdef SQLITE_CHECK_PAGES
  3063   3048       pPg->pageHash = pager_pagehash(pPg);
  3064   3049   #endif
  3065   3050     }else{
  3066   3051       /* The requested page is in the page cache. */
  3067   3052       assert(pPager->nRef>0 || pgno==1);
  3068         -    TEST_INCR(pPager->nHit);
         3053  +    PAGER_INCR(pPager->nHit);
  3069   3054       page_ref(pPg);
  3070   3055     }
  3071   3056     *ppPage = pPg;
  3072   3057     return SQLITE_OK;
  3073   3058   }
  3074   3059   
  3075   3060   /*
................................................................................
  3360   3345     if( pPager->readOnly ){
  3361   3346       return SQLITE_PERM;
  3362   3347     }
  3363   3348   
  3364   3349     assert( !pPager->setMaster );
  3365   3350   
  3366   3351     CHECK_PAGE(pPg);
         3352  +
         3353  +  /* If this page was previously acquired with noContent==1, that means
         3354  +  ** we didn't really read in the content of the page.  This can happen
         3355  +  ** (for example) when the page is being moved to the freelist.  But
         3356  +  ** now we are (perhaps) moving the page off of the freelist for
         3357  +  ** reuse and we need to know its original content so that content
         3358  +  ** can be stored in the rollback journal.  So do the read at this
         3359  +  ** time.
         3360  +  */
         3361  +  if( pPg->needRead ){
         3362  +    rc = readDbPage(pPager, pPg, pPg->pgno);
         3363  +    if( rc==SQLITE_OK ){
         3364  +      pPg->needRead = 0;
         3365  +    }else{
         3366  +      return rc;
         3367  +    }
         3368  +  }
  3367   3369   
  3368   3370     /* Mark the page as dirty.  If the page has already been written
  3369   3371     ** to the journal then we can return right away.
  3370   3372     */
  3371   3373     makeDirty(pPg);
  3372   3374     if( pPg->inJournal && (pageInStatement(pPg) || pPager->stmtInUse==0) ){
  3373   3375       pPager->dirtyCache = 1;
................................................................................
  3421   3423             pData2 -= 4;
  3422   3424             saved = *(u32*)pEnd;
  3423   3425             put32bits(pEnd, cksum);
  3424   3426             szPg = pPager->pageSize+8;
  3425   3427             put32bits(pData2, pPg->pgno);
  3426   3428             rc = sqlite3OsWrite(pPager->jfd, pData2, szPg);
  3427   3429             IOTRACE(("JOUT %p %d %lld %d\n", pPager, pPg->pgno,
  3428         -                   pPager->journalOff, szPg))
         3430  +                   pPager->journalOff, szPg));
         3431  +          PAGER_INCR(sqlite3_pager_writej_count);
  3429   3432             pPager->journalOff += szPg;
  3430   3433             PAGERTRACE4("JOURNAL %d page %d needSync=%d\n",
  3431   3434                     PAGERID(pPager), pPg->pgno, pPg->needSync);
  3432   3435             *(u32*)pEnd = saved;
  3433   3436   
  3434   3437   	  /* An error has occured writing to the journal file. The 
  3435   3438             ** transaction will be rolled back by the layer above.
................................................................................
  3604   3607     }
  3605   3608     return rc;
  3606   3609   }
  3607   3610   #endif
  3608   3611   
  3609   3612   /*
  3610   3613   ** A call to this routine tells the pager that it is not necessary to
  3611         -** write the information on page "pgno" back to the disk, even though
         3614  +** write the information on page pPg back to the disk, even though
  3612   3615   ** that page might be marked as dirty.
  3613   3616   **
  3614   3617   ** The overlying software layer calls this routine when all of the data
  3615   3618   ** on the given page is unused.  The pager marks the page as clean so
  3616   3619   ** that it does not get written to disk.
  3617   3620   **
  3618   3621   ** Tests show that this optimization, together with the
................................................................................
  3626   3629   ** a transaction then removed from the freelist during a later part
  3627   3630   ** of the same transaction and reused for some other purpose.  When it
  3628   3631   ** is first added to the freelist, this routine is called.  When reused,
  3629   3632   ** the sqlite3PagerDontRollback() routine is called.  But because the
  3630   3633   ** page contains critical data, we still need to be sure it gets
  3631   3634   ** rolled back in spite of the sqlite3PagerDontRollback() call.
  3632   3635   */
  3633         -void sqlite3PagerDontWrite(Pager *pPager, Pgno pgno){
  3634         -  PgHdr *pPg;
         3636  +void sqlite3PagerDontWrite(DbPage *pDbPage){
         3637  +  PgHdr *pPg = pDbPage;
         3638  +  Pager *pPager = pPg->pPager;
  3635   3639   
  3636   3640     if( MEMDB ) return;
  3637         -
  3638         -  pPg = pager_lookup(pPager, pgno);
  3639         -  assert( pPg!=0 );  /* We never call _dont_write unless the page is in mem */
  3640   3641     pPg->alwaysRollback = 1;
  3641   3642     if( pPg->dirty && !pPager->stmtInUse ){
  3642   3643       assert( pPager->state>=PAGER_SHARED );
  3643   3644       if( pPager->dbSize==(int)pPg->pgno && pPager->origDbSize<pPager->dbSize ){
  3644   3645         /* If this pages is the last page in the file and the file has grown
  3645   3646         ** during the current transaction, then do NOT mark the page as clean.
  3646   3647         ** When the database file grows, we must make sure that the last page
  3647   3648         ** gets written at least once so that the disk file will be the correct
  3648   3649         ** size. If you do not write this page and the size of the file
  3649   3650         ** on the disk ends up being too small, that can lead to database
  3650   3651         ** corruption during the next transaction.
  3651   3652         */
  3652   3653       }else{
  3653         -      PAGERTRACE3("DONT_WRITE page %d of %d\n", pgno, PAGERID(pPager));
  3654         -      IOTRACE(("CLEAN %p %d\n", pPager, pgno))
         3654  +      PAGERTRACE3("DONT_WRITE page %d of %d\n", pPg->pgno, PAGERID(pPager));
         3655  +      IOTRACE(("CLEAN %p %d\n", pPager, pPg->pgno))
  3655   3656         makeClean(pPg);
  3656   3657   #ifdef SQLITE_CHECK_PAGES
  3657   3658         pPg->pageHash = pager_pagehash(pPg);
  3658   3659   #endif
  3659   3660       }
  3660   3661     }
  3661   3662   }
  3662   3663   
  3663   3664   /*
  3664   3665   ** A call to this routine tells the pager that if a rollback occurs,
  3665   3666   ** it is not necessary to restore the data on the given page.  This
  3666   3667   ** means that the pager does not have to record the given page in the
  3667   3668   ** rollback journal.
         3669  +**
         3670  +** If we have not yet actually read the content of this page (if
         3671  +** the PgHdr.needRead flag is set) then this routine acts as a promise
         3672  +** that we will never need to read the page content in the future.
         3673  +** so the needRead flag can be cleared at this point.
  3668   3674   */
  3669   3675   void sqlite3PagerDontRollback(DbPage *pPg){
  3670   3676     Pager *pPager = pPg->pPager;
  3671   3677   
  3672   3678     assert( pPager->state>=PAGER_RESERVED );
  3673   3679     if( pPager->journalOpen==0 ) return;
  3674   3680     if( pPg->alwaysRollback || pPager->alwaysRollback || MEMDB ) return;
  3675   3681     if( !pPg->inJournal && (int)pPg->pgno <= pPager->origDbSize ){
  3676   3682       assert( pPager->aInJournal!=0 );
  3677   3683       pPager->aInJournal[pPg->pgno/8] |= 1<<(pPg->pgno&7);
  3678   3684       pPg->inJournal = 1;
         3685  +    pPg->needRead = 0;
  3679   3686       if( pPager->stmtInUse ){
  3680   3687         pPager->aInStmt[pPg->pgno/8] |= 1<<(pPg->pgno&7);
  3681   3688       }
  3682   3689       PAGERTRACE3("DONT_ROLLBACK page %d of %d\n", pPg->pgno, PAGERID(pPager));
  3683   3690       IOTRACE(("GARBAGE %p %d\n", pPager, pPg->pgno))
  3684   3691     }
  3685   3692     if( pPager->stmtInUse 

Changes to src/pager.h.

     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This header file defines the interface that the sqlite page cache
    13     13   ** subsystem.  The page cache subsystem reads and writes a file a page
    14     14   ** at a time and provides a journal for rollback.
    15     15   **
    16         -** @(#) $Id: pager.h,v 1.57 2007/03/30 14:06:34 drh Exp $
           16  +** @(#) $Id: pager.h,v 1.58 2007/04/13 02:14:30 drh Exp $
    17     17   */
    18     18   
    19     19   #ifndef _PAGER_H_
    20     20   #define _PAGER_H_
    21     21   
    22     22   /*
    23     23   ** The default size of a database page.
................................................................................
   105    105   int sqlite3PagerCommitPhaseTwo(Pager*);
   106    106   int sqlite3PagerRollback(Pager*);
   107    107   int sqlite3PagerIsreadonly(Pager*);
   108    108   int sqlite3PagerStmtBegin(Pager*);
   109    109   int sqlite3PagerStmtCommit(Pager*);
   110    110   int sqlite3PagerStmtRollback(Pager*);
   111    111   void sqlite3PagerDontRollback(DbPage*);
   112         -void sqlite3PagerDontWrite(Pager*, Pgno);
          112  +void sqlite3PagerDontWrite(DbPage*);
   113    113   int sqlite3PagerRefcount(Pager*);
   114    114   int *sqlite3PagerStats(Pager*);
   115    115   void sqlite3PagerSetSafetyLevel(Pager*,int,int);
   116    116   const char *sqlite3PagerFilename(Pager*);
   117    117   const char *sqlite3PagerDirname(Pager*);
   118    118   const char *sqlite3PagerJournalname(Pager*);
   119    119   int sqlite3PagerNosync(Pager*);

Changes to src/test1.c.

     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** Code for testing all sorts of SQLite interfaces.  This code
    13     13   ** is not included in the SQLite library.  It is used for automated
    14     14   ** testing of the SQLite library.
    15     15   **
    16         -** $Id: test1.c,v 1.236 2007/04/09 12:45:03 drh Exp $
           16  +** $Id: test1.c,v 1.237 2007/04/13 02:14:30 drh Exp $
    17     17   */
    18     18   #include "sqliteInt.h"
    19     19   #include "tcl.h"
    20     20   #include "os.h"
    21     21   #include <stdlib.h>
    22     22   #include <string.h>
    23     23   
................................................................................
   192    192       }
   193    193     }
   194    194     for(i=0; i<argc; i++){
   195    195       Tcl_DStringAppendElement(str, argv[i] ? argv[i] : "NULL");
   196    196     }
   197    197     return 0;
   198    198   }
          199  +
          200  +/*
          201  +** The I/O tracing callback.
          202  +*/
          203  +static FILE *iotrace_file = 0;
          204  +static void io_trace_callback(const char *zFormat, ...){
          205  +  va_list ap;
          206  +  va_start(ap, zFormat);
          207  +  vfprintf(iotrace_file, zFormat, ap);
          208  +  va_end(ap);
          209  +  fflush(iotrace_file);
          210  +}
          211  +
          212  +/*
          213  +** Usage:  io_trace FILENAME
          214  +**
          215  +** Turn I/O tracing on or off.  If FILENAME is not an empty string,
          216  +** I/O tracing begins going into FILENAME. If FILENAME is an empty
          217  +** string, I/O tracing is turned off.
          218  +*/
          219  +static int test_io_trace(
          220  +  void *NotUsed,
          221  +  Tcl_Interp *interp,    /* The TCL interpreter that invoked this command */
          222  +  int argc,              /* Number of arguments */
          223  +  char **argv            /* Text of each argument */
          224  +){
          225  +  if( argc!=2 ){
          226  +    Tcl_AppendResult(interp, "wrong # args: should be \"", argv[0],
          227  +          " FILENAME\"", 0);
          228  +    return TCL_ERROR;
          229  +  }
          230  +  if( iotrace_file ){
          231  +    if( iotrace_file!=stdout && iotrace_file!=stderr ){
          232  +      fclose(iotrace_file);
          233  +    }
          234  +    iotrace_file = 0;
          235  +    sqlite3_io_trace = 0;
          236  +  }
          237  +  if( argv[1][0] ){
          238  +    if( strcmp(argv[1],"stdout")==0 ){
          239  +      iotrace_file = stdout;
          240  +    }else if( strcmp(argv[1],"stderr")==0 ){
          241  +      iotrace_file = stderr;
          242  +    }else{
          243  +      iotrace_file = fopen(argv[1], "w");
          244  +    }
          245  +    sqlite3_io_trace = io_trace_callback;
          246  +  }
          247  +  return SQLITE_OK;
          248  +}
          249  +
   199    250   
   200    251   /*
   201    252   ** Usage:  sqlite3_exec_printf  DB  FORMAT  STRING
   202    253   **
   203    254   ** Invoke the sqlite3_exec_printf() interface using the open database
   204    255   ** DB.  The SQL is the string FORMAT.  The format string should contain
   205    256   ** one %s or %q.  STRING is the value inserted into %s or %q.
................................................................................
  4212   4263        { "sqlite3_interrupt",             (Tcl_CmdProc*)test_interrupt        },
  4213   4264        { "sqlite_delete_function",        (Tcl_CmdProc*)delete_function       },
  4214   4265        { "sqlite_delete_collation",       (Tcl_CmdProc*)delete_collation      },
  4215   4266        { "sqlite3_get_autocommit",        (Tcl_CmdProc*)get_autocommit        },
  4216   4267        { "sqlite3_stack_used",            (Tcl_CmdProc*)test_stack_used       },
  4217   4268        { "sqlite3_busy_timeout",          (Tcl_CmdProc*)test_busy_timeout     },
  4218   4269        { "printf",                        (Tcl_CmdProc*)test_printf           },
         4270  +     { "sqlite3_io_trace",              (Tcl_CmdProc*)test_io_trace         },
  4219   4271     };
  4220   4272     static struct {
  4221   4273        char *zName;
  4222   4274        Tcl_ObjCmdProc *xProc;
  4223   4275        void *clientData;
  4224   4276     } aObjCmd[] = {
  4225   4277        { "sqlite3_connection_pointer",    get_sqlite_pointer, 0 },
................................................................................
  4334   4386     extern int sqlite3_opentemp_count;
  4335   4387     extern int sqlite3_memUsed;
  4336   4388     extern char *sqlite3_malloc_id;
  4337   4389     extern int sqlite3_memMax;
  4338   4390     extern int sqlite3_like_count;
  4339   4391     extern int sqlite3_tsd_count;
  4340   4392     extern int sqlite3_xferopt_count;
         4393  +  extern int sqlite3_pager_readdb_count;
         4394  +  extern int sqlite3_pager_writedb_count;
         4395  +  extern int sqlite3_pager_writej_count;
         4396  +  extern int sqlite3_pager_pgfree_count;
  4341   4397   #if OS_UNIX && defined(SQLITE_TEST) && defined(THREADSAFE) && THREADSAFE
  4342   4398     extern int threadsOverrideEachOthersLocks;
  4343   4399   #endif
  4344   4400   #if OS_WIN
  4345   4401     extern int sqlite3_os_type;
  4346   4402   #endif
  4347   4403   #ifdef SQLITE_DEBUG
................................................................................
  4373   4429         (char*)&sqlite3_current_time, TCL_LINK_INT);
  4374   4430     Tcl_LinkVar(interp, "sqlite_os_trace",
  4375   4431         (char*)&sqlite3_os_trace, TCL_LINK_INT);
  4376   4432     Tcl_LinkVar(interp, "sqlite3_tsd_count",
  4377   4433         (char*)&sqlite3_tsd_count, TCL_LINK_INT);
  4378   4434     Tcl_LinkVar(interp, "sqlite3_xferopt_count",
  4379   4435         (char*)&sqlite3_xferopt_count, TCL_LINK_INT);
         4436  +  Tcl_LinkVar(interp, "sqlite3_pager_readdb_count",
         4437  +      (char*)&sqlite3_pager_readdb_count, TCL_LINK_INT);
         4438  +  Tcl_LinkVar(interp, "sqlite3_pager_writedb_count",
         4439  +      (char*)&sqlite3_pager_writedb_count, TCL_LINK_INT);
         4440  +  Tcl_LinkVar(interp, "sqlite3_pager_writej_count",
         4441  +      (char*)&sqlite3_pager_writej_count, TCL_LINK_INT);
         4442  +  Tcl_LinkVar(interp, "sqlite3_pager_pgfree_count",
         4443  +      (char*)&sqlite3_pager_pgfree_count, TCL_LINK_INT);
  4380   4444   #ifndef SQLITE_OMIT_UTF16
  4381   4445     Tcl_LinkVar(interp, "unaligned_string_counter",
  4382   4446         (char*)&unaligned_string_counter, TCL_LINK_INT);
  4383   4447   #endif
  4384   4448   #if OS_UNIX && defined(SQLITE_TEST) && defined(THREADSAFE) && THREADSAFE
  4385   4449     Tcl_LinkVar(interp, "threadsOverrideEachOthersLocks",
  4386   4450         (char*)&threadsOverrideEachOthersLocks, TCL_LINK_INT);

Changes to test/ioerr2.test.

    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing for correct handling of I/O errors
    13     13   # such as writes failing because the disk is full.
    14     14   # 
    15     15   # The tests in this file use special facilities that are only
    16     16   # available in the SQLite test fixture.
    17     17   #
    18         -# $Id: ioerr2.test,v 1.3 2007/04/09 12:45:03 drh Exp $
           18  +# $Id: ioerr2.test,v 1.4 2007/04/13 02:14:30 drh Exp $
    19     19   
    20     20   set testdir [file dirname $argv0]
    21     21   source $testdir/tester.tcl
    22     22   
    23     23   do_test ioerr2-1.1 {
    24     24     execsql {
    25     25       PRAGMA cache_size = 10;
................................................................................
    78     78     INSERT INTO t1 SELECT randstr(400,400), randstr(400,400) 
    79     79       WHERE (random()%7)==0;
    80     80     UPDATE t1 SET a = randstr(400,400), b = randstr(400,400) 
    81     81       WHERE (random()%7)==0;
    82     82     ROLLBACK;
    83     83   }
    84     84   
    85         -breakpoint
           85  +foreach bPersist [list 0 1] {
           86  +  set ::go 1
           87  +  for {set ::N 1} {$::go} {incr ::N} {
           88  +    db close
           89  +    sqlite3 db test.db
           90  +    set ::sqlite_io_error_hit 0
           91  +    set ::sqlite_io_error_persist $bPersist
           92  +    set ::sqlite_io_error_pending $::N
           93  +
           94  +    foreach {::go res} [catchsql $sql] {}
           95  +    check_db ioerr2-3.$bPersist.$::N
           96  +  }
           97  +}
    86     98   foreach bPersist [list 0 1] {
    87     99     set ::go 1
    88    100     for {set ::N 1} {$::go} {incr ::N} {
    89    101       set ::sqlite_io_error_hit 0
    90    102       set ::sqlite_io_error_persist $bPersist
    91    103       set ::sqlite_io_error_pending $::N
    92    104   
    93    105       foreach {::go res} [catchsql $sql] {}
    94         -    check_db ioerr2-3.$bPersist.$::N
          106  +    check_db ioerr2-3.[expr {$bPersist+2}].$::N
    95    107     }
    96    108   }
    97    109   
    98    110   finish_test

Added test/pageropt.test.

            1  +# 2007 April 12
            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  +# This file implements regression tests for SQLite library.
           12  +# The focus of the tests in this file are to verify that the
           13  +# pager optimizations implemented in version 3.3.14 work.
           14  +#
           15  +# $Id: pageropt.test,v 1.1 2007/04/13 02:14:30 drh Exp $
           16  +
           17  +set testdir [file dirname $argv0]
           18  +source $testdir/tester.tcl
           19  +
           20  +ifcapable {!pager_pragmas} {
           21  +  finish_test
           22  +  return
           23  +}
           24  +
           25  +# Run the SQL statement supplied by the argument and return
           26  +# the results.  Prepend four integers to the beginning of the
           27  +# result which are
           28  +#
           29  +#     (1)  The number of page reads from the database
           30  +#     (2)  The number of page writes to the database
           31  +#     (3)  The number of page writes to the journal
           32  +#     (4)  The number of cache pages freed
           33  +#
           34  +proc pagercount_sql {sql {db db}} {
           35  +  global sqlite3_pager_readdb_count
           36  +  global sqlite3_pager_writedb_count
           37  +  global sqlite3_pager_writej_count
           38  +  global sqlite3_pager_pgfree_count
           39  +  set sqlite3_pager_readdb_count 0
           40  +  set sqlite3_pager_writedb_count 0
           41  +  set sqlite3_pager_writej_count 0
           42  +  set sqlite3_pager_pgfree_count 0
           43  +  set r [$db eval $sql]
           44  +  set cnt [list $sqlite3_pager_readdb_count \
           45  +                $sqlite3_pager_writedb_count \
           46  +                $sqlite3_pager_writej_count \
           47  +                $sqlite3_pager_pgfree_count]
           48  +  return [concat $cnt $r]
           49  +}
           50  +
           51  +# Setup the test database
           52  +#
           53  +do_test pageropt-1.1 {
           54  +  execsql {
           55  +    PRAGMA auto_vacuum = OFF;
           56  +    PRAGMA page_size = 1024;
           57  +  }
           58  +  pagercount_sql {
           59  +    CREATE TABLE t1(x);
           60  +  }
           61  +} {0 2 0 0}
           62  +do_test pageropt-1.2 {
           63  +  pagercount_sql {
           64  +    INSERT INTO t1 VALUES(randomblob(5000));
           65  +  }
           66  +} {0 6 2 0}
           67  +
           68  +# Verify that values remain in cache on for subsequent reads.
           69  +# We should not have to go back to disk.
           70  +#
           71  +do_test pageropt-1.3 {
           72  +  pagercount_sql {
           73  +    SELECT length(x) FROM t1
           74  +  }
           75  +} {0 0 0 0 5000}
           76  +
           77  +# If another thread reads the database, the original cache
           78  +# remains valid.
           79  +#
           80  +sqlite3 db2 test.db
           81  +set blobcontent [db2 one {SELECT hex(x) FROM t1}]
           82  +do_test pageropt-1.4 {
           83  +  pagercount_sql {
           84  +    SELECT hex(x) FROM t1
           85  +  }
           86  +} [list 0 0 0 0 $blobcontent]
           87  +
           88  +# But if the other thread modifies the database, then the cache
           89  +# must refill.
           90  +#
           91  +do_test pageropt-1.5 {
           92  +  db2 eval {CREATE TABLE t2(y)}
           93  +  pagercount_sql {
           94  +    SELECT hex(x) FROM t1
           95  +  }
           96  +} [list 6 0 0 6 $blobcontent]
           97  +do_test pageropt-1.6 {
           98  +  pagercount_sql {
           99  +    SELECT hex(x) FROM t1
          100  +  }
          101  +} [list 0 0 0 0 $blobcontent]
          102  +
          103  +# Verify that the last page of an overflow chain is not read from
          104  +# disk when deleting a row.  The one row of t1(x) has four pages
          105  +# of overflow.  So deleting that row from t1 should involve reading
          106  +# the sqlite_master table (1 page) the main page of t1 (1 page) and
          107  +# the three overflow pages of t1 for a total of 5 pages.
          108  +#
          109  +# Pages written are page 1 (for the freelist pointer), the root page
          110  +# of the table, and one of the overflow chain pointers because it
          111  +# becomes the trunk of the freelist.  Total 3.
          112  +#
          113  +do_test pageropt-2.1 {
          114  +  db close
          115  +  sqlite3 db test.db
          116  +  pagercount_sql {
          117  +    DELETE FROM t1 WHERE rowid=1
          118  +  }
          119  +} {5 3 3 0}
          120  +
          121  +# When pulling pages off of the freelist, there is no reason
          122  +# to actually bring in the old content.
          123  +#
          124  +do_test pageropt-2.2 {
          125  +  db close
          126  +  sqlite3 db test.db
          127  +  pagercount_sql {
          128  +    INSERT INTO t1 VALUES(randomblob(1500));
          129  +  }
          130  +} {3 4 3 0}
          131  +do_test pageropt-2.3 {
          132  +  pagercount_sql {
          133  +    INSERT INTO t1 VALUES(randomblob(1500));
          134  +  }
          135  +} {0 4 3 0}
          136  +
          137  +catch {db2 close}
          138  +finish_test