/ Check-in [be5a549e]
Login

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

Overview
Comment:Defer opening the file used for the temp database (where CREATE TEMP TABLE tables are stored) until the database is too large to reside entirely within the cache. There are likely still problems on this branch.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | tempfiles-lazy-open
Files: files | file ages | folders
SHA1: be5a549eba6cf8e29cb6b9824fd6d0db9d03ca7f
User & Date: dan 2016-04-05 21:07:58
Context
2016-04-06
15:39
Add tests to this branch. Fix a problem with temporary databases in auto-vacuum mode. check-in: afe9bd9b user: dan tags: tempfiles-lazy-open
2016-04-05
21:07
Defer opening the file used for the temp database (where CREATE TEMP TABLE tables are stored) until the database is too large to reside entirely within the cache. There are likely still problems on this branch. check-in: be5a549e user: dan tags: tempfiles-lazy-open
20:59
Carry table column types through into VIEW definitions, where possible. check-in: fb555c3c user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/pager.c.

  2002   2002       }
  2003   2003     }
  2004   2004   #endif
  2005   2005   
  2006   2006     sqlite3BitvecDestroy(pPager->pInJournal);
  2007   2007     pPager->pInJournal = 0;
  2008   2008     pPager->nRec = 0;
  2009         -  sqlite3PcacheCleanAll(pPager->pPCache);
         2009  +  if( pPager->tempFile==0 || MEMDB ){
         2010  +    sqlite3PcacheCleanAll(pPager->pPCache);
         2011  +  }else{
         2012  +    sqlite3PcacheClearWritable(pPager->pPCache);
         2013  +  }
  2010   2014     sqlite3PcacheTruncate(pPager->pPCache, pPager->dbSize);
  2011   2015   
  2012   2016     if( pagerUseWal(pPager) ){
  2013   2017       /* Drop the WAL write-lock, if any. Also, if the connection was in 
  2014   2018       ** locking_mode=exclusive mode but is no longer, drop the EXCLUSIVE 
  2015   2019       ** lock held on the database file.
  2016   2020       */
................................................................................
  2370   2374         ** already in the journal file (recorded in Pager.pInJournal) and
  2371   2375         ** the PGHDR_NEED_SYNC flag is cleared, if the page is written to
  2372   2376         ** again within this transaction, it will be marked as dirty but
  2373   2377         ** the PGHDR_NEED_SYNC flag will not be set. It could then potentially
  2374   2378         ** be written out into the database file before its journal file
  2375   2379         ** segment is synced. If a crash occurs during or following this,
  2376   2380         ** database corruption may ensue.
         2381  +      **
         2382  +      ** Update: Another exception is for temp files that are not 
         2383  +      ** in-memory databases. In this case the page may have been dirty
         2384  +      ** at the start of the transaction.
  2377   2385         */
  2378   2386         assert( !pagerUseWal(pPager) );
  2379         -      sqlite3PcacheMakeClean(pPg);
         2387  +      if( pPager->tempFile==0 ) sqlite3PcacheMakeClean(pPg);
  2380   2388       }
  2381   2389       pager_set_pagehash(pPg);
  2382   2390   
  2383   2391       /* If this was page 1, then restore the value of Pager.dbFileVers.
  2384   2392       ** Do this before any decoding. */
  2385   2393       if( pgno==1 ){
  2386   2394         memcpy(&pPager->dbFileVers, &((u8*)pData)[24],sizeof(pPager->dbFileVers));
................................................................................
  4254   4262   ** be obtained, SQLITE_BUSY is returned.
  4255   4263   */
  4256   4264   static int pager_write_pagelist(Pager *pPager, PgHdr *pList){
  4257   4265     int rc = SQLITE_OK;                  /* Return code */
  4258   4266   
  4259   4267     /* This function is only called for rollback pagers in WRITER_DBMOD state. */
  4260   4268     assert( !pagerUseWal(pPager) );
  4261         -  assert( pPager->eState==PAGER_WRITER_DBMOD );
         4269  +  assert( pPager->tempFile || pPager->eState==PAGER_WRITER_DBMOD );
  4262   4270     assert( pPager->eLock==EXCLUSIVE_LOCK );
         4271  +  assert( pPager->tempFile==0 || pList->pDirty==0 );
  4263   4272   
  4264   4273     /* If the file is a temp-file has not yet been opened, open it now. It
  4265   4274     ** is not possible for rc to be other than SQLITE_OK if this branch
  4266   4275     ** is taken, as pager_wait_on_lock() is a no-op for temp-files.
  4267   4276     */
  4268   4277     if( !isOpen(pPager->fd) ){
  4269   4278       assert( pPager->tempFile && rc==SQLITE_OK );
................................................................................
  5937   5946     assert( assert_pager_state(pPager) );
  5938   5947     if( pPager->errCode ){
  5939   5948       return pPager->errCode;
  5940   5949     }else if( (pPg->flags & PGHDR_WRITEABLE)!=0 && pPager->dbSize>=pPg->pgno ){
  5941   5950       if( pPager->nSavepoint ) return subjournalPageIfRequired(pPg);
  5942   5951       return SQLITE_OK;
  5943   5952     }else if( pPager->sectorSize > (u32)pPager->pageSize ){
         5953  +    assert( pPager->tempFile==0 );
  5944   5954       return pagerWriteLargeSector(pPg);
  5945   5955     }else{
  5946   5956       return pager_write(pPg);
  5947   5957     }
  5948   5958   }
  5949   5959   
  5950   5960   /*
................................................................................
  6175   6185   
  6176   6186     PAGERTRACE(("DATABASE SYNC: File=%s zMaster=%s nSize=%d\n", 
  6177   6187         pPager->zFilename, zMaster, pPager->dbSize));
  6178   6188   
  6179   6189     /* If no database changes have been made, return early. */
  6180   6190     if( pPager->eState<PAGER_WRITER_CACHEMOD ) return SQLITE_OK;
  6181   6191   
  6182         -  if( MEMDB ){
         6192  +  assert( MEMDB==0 || pPager->tempFile );
         6193  +  if( pPager->tempFile ){
  6183   6194       /* If this is an in-memory db, or no pages have been written to, or this
  6184   6195       ** function has already been called, it is mostly a no-op.  However, any
  6185         -    ** backup in progress needs to be restarted.
  6186         -    */
         6196  +    ** backup in progress needs to be restarted.  */
  6187   6197       sqlite3BackupRestart(pPager->pBackup);
  6188   6198     }else{
  6189   6199       if( pagerUseWal(pPager) ){
  6190   6200         PgHdr *pList = sqlite3PcacheDirtyList(pPager->pPCache);
  6191   6201         PgHdr *pPageOne = 0;
  6192   6202         if( pList==0 ){
  6193   6203           /* Must have at least one page for the WAL commit flag.
................................................................................
  6841   6851     /* If the journal needs to be sync()ed before page pPg->pgno can
  6842   6852     ** be written to, store pPg->pgno in local variable needSyncPgno.
  6843   6853     **
  6844   6854     ** If the isCommit flag is set, there is no need to remember that
  6845   6855     ** the journal needs to be sync()ed before database page pPg->pgno 
  6846   6856     ** can be written to. The caller has already promised not to write to it.
  6847   6857     */
  6848         -  if( (pPg->flags&PGHDR_NEED_SYNC) && !isCommit ){
         6858  +  if( (pPg->flags&PGHDR_NEED_SYNC) && !isCommit && pPager->tempFile==0 ){
  6849   6859       needSyncPgno = pPg->pgno;
  6850   6860       assert( pPager->journalMode==PAGER_JOURNALMODE_OFF ||
  6851   6861               pageInJournal(pPager, pPg) || pPg->pgno>pPager->dbOrigSize );
  6852   6862       assert( pPg->flags&PGHDR_DIRTY );
  6853   6863     }
  6854   6864   
  6855   6865     /* If the cache contains a page with page-number pgno, remove it

Changes to src/pcache.c.

   250    250     assert( createFlag==0 || pCache->eCreate==eCreate );
   251    251     assert( createFlag==0 || eCreate==1+(!pCache->bPurgeable||!pCache->pDirty) );
   252    252     return sqlite3GlobalConfig.pcache2.xFetch(pCache->pCache, pgno, eCreate);
   253    253   }
   254    254   
   255    255   /*
   256    256   ** If the sqlite3PcacheFetch() routine is unable to allocate a new
   257         -** page because new clean pages are available for reuse and the cache
          257  +** page because no clean pages are available for reuse and the cache
   258    258   ** size limit has been reached, then this routine can be invoked to 
   259    259   ** try harder to allocate a page.  This routine might invoke the stress
   260    260   ** callback to spill dirty pages to the journal.  It will then try to
   261    261   ** allocate the new page and will only fail to allocate a new page on
   262    262   ** an OOM error.
   263    263   **
   264    264   ** This routine should be invoked only after sqlite3PcacheFetch() fails.
................................................................................
   434    434   */
   435    435   void sqlite3PcacheCleanAll(PCache *pCache){
   436    436     PgHdr *p;
   437    437     while( (p = pCache->pDirty)!=0 ){
   438    438       sqlite3PcacheMakeClean(p);
   439    439     }
   440    440   }
          441  +
          442  +/*
          443  +** Clear the PGHDR_NEED_SYNC and PGHDR_WRITEABLE flag from all dirty pages.
          444  +*/
          445  +void sqlite3PcacheClearWritable(PCache *pCache){
          446  +  PgHdr *p;
          447  +  for(p=pCache->pDirty; p; p=p->pDirtyNext){
          448  +    p->flags &= ~(PGHDR_NEED_SYNC|PGHDR_WRITEABLE);
          449  +  }
          450  +  pCache->pSynced = pCache->pDirtyTail;
          451  +}
   441    452   
   442    453   /*
   443    454   ** Clear the PGHDR_NEED_SYNC flag from all dirty pages.
   444    455   */
   445    456   void sqlite3PcacheClearSyncFlags(PCache *pCache){
   446    457     PgHdr *p;
   447    458     for(p=pCache->pDirty; p; p=p->pDirtyNext){
................................................................................
   480    491       for(p=pCache->pDirty; p; p=pNext){
   481    492         pNext = p->pDirtyNext;
   482    493         /* This routine never gets call with a positive pgno except right
   483    494         ** after sqlite3PcacheCleanAll().  So if there are dirty pages,
   484    495         ** it must be that pgno==0.
   485    496         */
   486    497         assert( p->pgno>0 );
   487         -      if( ALWAYS(p->pgno>pgno) ){
          498  +      if( p->pgno>pgno ){
   488    499           assert( p->flags&PGHDR_DIRTY );
   489    500           sqlite3PcacheMakeClean(p);
   490    501         }
   491    502       }
   492    503       if( pgno==0 && pCache->nRefSum ){
   493    504         sqlite3_pcache_page *pPage1;
   494    505         pPage1 = sqlite3GlobalConfig.pcache2.xFetch(pCache->pCache,1,0);

Added test/temptable2.test.

            1  +# 2016 March 3
            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  +set testdir [file dirname $argv0]
           13  +source $testdir/tester.tcl
           14  +set testprefix temptable2
           15  +
           16  +do_execsql_test 1.1 {
           17  +  CREATE TEMP TABLE t1(a, b);
           18  +  CREATE INDEX i1 ON t1(a, b);
           19  +}
           20  +
           21  +do_execsql_test 1.2 {
           22  +  WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<100000 )
           23  +  INSERT INTO t1 SELECT randomblob(100), randomblob(100) FROM X;
           24  +} {}
           25  +
           26  +do_execsql_test 1.3 {
           27  +  PRAGMA temp.integrity_check;
           28  +} {ok}
           29  +
           30  +#-------------------------------------------------------------------------
           31  +#
           32  +reset_db
           33  +do_execsql_test 2.1 {
           34  +  CREATE TEMP TABLE t2(a, b);
           35  +  INSERT INTO t2 VALUES(1, 2);
           36  +} {}
           37  +
           38  +do_execsql_test 2.2 {
           39  +  BEGIN;
           40  +    INSERT INTO t2 VALUES(3, 4);
           41  +    SELECT * FROM t2;
           42  +} {1 2 3 4}
           43  +
           44  +do_execsql_test 2.3 {
           45  +  ROLLBACK;
           46  +  SELECT * FROM t2;
           47  +} {1 2}
           48  +
           49  +#-------------------------------------------------------------------------
           50  +#
           51  +reset_db
           52  +do_execsql_test 3.1.1 {
           53  +  PRAGMA main.cache_size = 10;
           54  +  PRAGMA temp.cache_size = 10;
           55  +
           56  +  CREATE TEMP TABLE t1(a, b);
           57  +  CREATE INDEX i1 ON t1(a, b);
           58  +
           59  +  WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<1000 )
           60  +  INSERT INTO t1 SELECT randomblob(100), randomblob(100) FROM x;
           61  +
           62  +  SELECT count(*) FROM t1;
           63  +} {1000}
           64  +do_execsql_test 3.1.2 {
           65  +  BEGIN;
           66  +    UPDATE t1 SET b=randomblob(100) WHERE (rowid%10)==0;
           67  +  ROLLBACK;
           68  +}
           69  +do_execsql_test 3.1.3 {
           70  +  SELECT count(*) FROM t1;
           71  +} {1000}
           72  +do_execsql_test 3.1.4 { PRAGMA temp.integrity_check } {ok}
           73  +
           74  +do_execsql_test 3.2.1 {
           75  +  BEGIN;
           76  +    UPDATE t1 SET b=randomblob(100) WHERE (rowid%10)==0;
           77  +    SAVEPOINT abc;
           78  +      UPDATE t1 SET b=randomblob(100) WHERE (rowid%10)==1;
           79  +    ROLLBACK TO abc;
           80  +    UPDATE t1 SET b=randomblob(100) WHERE (rowid%10)==2;
           81  +  COMMIT;
           82  +}
           83  +do_execsql_test 3.2.2 { PRAGMA temp.integrity_check } {ok}
           84  +
           85  +#-------------------------------------------------------------------------
           86  +#
           87  +reset_db
           88  +do_execsql_test 4.1.1 {
           89  +  PRAGMA main.cache_size = 10;
           90  +  PRAGMA temp.cache_size = 10;
           91  +
           92  +  CREATE TEMP TABLE t1(a, b);
           93  +  CREATE INDEX i1 ON t1(a, b);
           94  +
           95  +  WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<10 )
           96  +  INSERT INTO t1 SELECT randomblob(100), randomblob(100) FROM x;
           97  +
           98  +  SELECT count(*) FROM t1;
           99  +  PRAGMA temp.page_count;
          100  +} {10 9}
          101  +
          102  +do_execsql_test 4.1.2 {
          103  +  BEGIN;
          104  +    UPDATE t1 SET b=randomblob(100);
          105  +  ROLLBACK;
          106  +}
          107  +
          108  +do_execsql_test 4.1.3 {
          109  +  CREATE TEMP TABLE t2(a, b);
          110  +  CREATE INDEX i2 ON t2(a, b);
          111  +  WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<500 )
          112  +  INSERT INTO t2 SELECT randomblob(100), randomblob(100) FROM x;
          113  +
          114  +  SELECT count(*) FROM t2;
          115  +  SELECT count(*) FROM t1;
          116  +  PRAGMA temp.page_count;
          117  +} {500 10 292}
          118  +
          119  +do_execsql_test 4.1.4 { PRAGMA temp.integrity_check } {ok}
          120  +
          121  +#-------------------------------------------------------------------------
          122  +#
          123  +reset_db
          124  +do_execsql_test 5.1.1 {
          125  +  PRAGMA main.cache_size = 10;
          126  +  PRAGMA temp.cache_size = 10;
          127  +
          128  +  CREATE TEMP TABLE t2(a, b);
          129  +  CREATE INDEX i2 ON t2(a, b);
          130  +  WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<500 )
          131  +  INSERT INTO t2 SELECT randomblob(100), randomblob(100) FROM x;
          132  +
          133  +  CREATE TEMP TABLE t1(a, b);
          134  +  CREATE INDEX i1 ON t1(a, b);
          135  +  INSERT INTO t1 VALUES(1, 2);
          136  +
          137  +  PRAGMA temp.page_count;
          138  +} {286}
          139  +
          140  +do_execsql_test 5.1.2 {
          141  +  BEGIN;
          142  +    UPDATE t1 SET a=2;
          143  +    UPDATE t2 SET a=randomblob(100);
          144  +    SELECT count(*) FROM t1;
          145  +  ROLLBACK;
          146  +} {1}
          147  +
          148  +do_execsql_test 5.1.3 {
          149  +  UPDATE t2 SET a=randomblob(100);
          150  +
          151  +  SELECT * FROM t1;
          152  +} {1 2}
          153  +
          154  +do_execsql_test 5.1.4 { PRAGMA temp.integrity_check } {ok}
          155  +
          156  +finish_test
          157  +