/ Check-in [d07c4331]
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:The fullfsync, checkpoint_fullfsync, and cache_spill pragmas apply to all files of a database connection, including those opened by future ATTACH statements.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | cache_spill
Files: files | file ages | folders
SHA1: d07c4331a28d44deca1ece8a34118f5b121b3ee2
User & Date: drh 2013-08-17 15:42:29
Context
2013-08-17
16:37
Add the cache_spill pragma. Change the fullfsync and checkpoint_fullfsync pragmas to apply to all attached databases. check-in: 65a85a15 user: drh tags: trunk
15:42
The fullfsync, checkpoint_fullfsync, and cache_spill pragmas apply to all files of a database connection, including those opened by future ATTACH statements. Closed-Leaf check-in: d07c4331 user: drh tags: cache_spill
00:25
Test cases for the cache_spill pragma. check-in: b85c9ec5 user: drh tags: cache_spill
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/attach.c.

   154    154           "attached databases must use the same text encoding as main database");
   155    155         rc = SQLITE_ERROR;
   156    156       }
   157    157       pPager = sqlite3BtreePager(aNew->pBt);
   158    158       sqlite3PagerLockingMode(pPager, db->dfltLockMode);
   159    159       sqlite3BtreeSecureDelete(aNew->pBt,
   160    160                                sqlite3BtreeSecureDelete(db->aDb[0].pBt,-1) );
          161  +    sqlite3BtreeSetPagerFlags(aNew->pBt, 3 | (db->flags & PAGER_FLAGS_MASK));
   161    162     }
   162    163     aNew->safety_level = 3;
   163    164     aNew->zName = sqlite3DbStrDup(db, zName);
   164    165     if( rc==SQLITE_OK && aNew->zName==0 ){
   165    166       rc = SQLITE_NOMEM;
   166    167     }
   167    168   

Changes to src/pragma.c.

   153    153       memcpy(pI64, &value, sizeof(value));
   154    154     }
   155    155     sqlite3VdbeAddOp4(v, OP_Int64, 0, mem, 0, (char*)pI64, P4_INT64);
   156    156     sqlite3VdbeSetNumCols(v, 1);
   157    157     sqlite3VdbeSetColName(v, 0, COLNAME_NAME, zLabel, SQLITE_STATIC);
   158    158     sqlite3VdbeAddOp2(v, OP_ResultRow, mem, 1);
   159    159   }
          160  +
          161  +
          162  +/*
          163  +** Set the safety_level and pager flags for pager iDb.  Or if iDb<0
          164  +** set these values for all pagers.
          165  +*/
          166  +#ifndef SQLITE_OMIT_PAGER_PRAGMAS
          167  +static void setAllPagerFlags(sqlite3 *db){
          168  +  if( db->autoCommit ){
          169  +    Db *pDb = db->aDb;
          170  +    int n = db->nDb;
          171  +    assert( SQLITE_FullFSync==PAGER_FULLFSYNC );
          172  +    assert( SQLITE_CkptFullFSync==PAGER_CKPT_FULLFSYNC );
          173  +    assert( SQLITE_CacheSpill==PAGER_CACHESPILL );
          174  +    assert( (PAGER_FULLFSYNC | PAGER_CKPT_FULLFSYNC | PAGER_CACHESPILL)
          175  +             ==  PAGER_FLAGS_MASK );
          176  +    assert( (pDb->safety_level & PAGER_SYNCHRONOUS_MASK)==pDb->safety_level );
          177  +    while( (n--) > 0 ){
          178  +      if( pDb->pBt ){
          179  +        sqlite3BtreeSetPagerFlags(pDb->pBt,
          180  +                 pDb->safety_level | (db->flags & PAGER_FLAGS_MASK) );
          181  +      }
          182  +      pDb++;
          183  +    }
          184  +  }
          185  +}
          186  +#endif
          187  +
   160    188   
   161    189   #ifndef SQLITE_OMIT_FLAG_PRAGMAS
   162    190   /*
   163    191   ** Check to see if zRight and zLeft refer to a pragma that queries
   164    192   ** or changes one of the flags in db->flags.  Return 1 if so and 0 if not.
   165    193   ** Also, implement the pragma.
   166    194   */
................................................................................
   963    991         returnSingleInt(pParse, "synchronous", pDb->safety_level-1);
   964    992       }else{
   965    993         if( !db->autoCommit ){
   966    994           sqlite3ErrorMsg(pParse, 
   967    995               "Safety level may not be changed inside a transaction");
   968    996         }else{
   969    997           pDb->safety_level = getSafetyLevel(zRight,0,1)+1;
          998  +        setAllPagerFlags(db);
   970    999         }
   971   1000       }
   972   1001     }else
   973   1002   #endif /* SQLITE_OMIT_PAGER_PRAGMAS */
   974   1003   
   975   1004   #ifndef SQLITE_OMIT_FLAG_PRAGMAS
   976   1005     if( flagPragma(pParse, zLeft, zRight) ){
   977         -    /* The flagPragma() subroutine also generates any necessary code
   978         -    ** there is nothing more to do here */
         1006  +    setAllPagerFlags(db);
   979   1007     }else
   980   1008   #endif /* SQLITE_OMIT_FLAG_PRAGMAS */
   981   1009   
   982   1010   #ifndef SQLITE_OMIT_SCHEMA_PRAGMAS
   983   1011     /*
   984   1012     **   PRAGMA table_info(<table>)
   985   1013     **
................................................................................
  1802   1830   #endif
  1803   1831     }else
  1804   1832   #endif
  1805   1833   
  1806   1834    
  1807   1835     {/* Empty ELSE clause */}
  1808   1836   
  1809         -  /*
  1810         -  ** Reset the safety level, in case the fullfsync flag or synchronous
  1811         -  ** setting changed.
  1812         -  */
  1813         -#ifndef SQLITE_OMIT_PAGER_PRAGMAS
  1814         -  if( db->autoCommit ){
  1815         -    assert( (pDb->safety_level & PAGER_SYNCHRONOUS_MASK)==pDb->safety_level );
  1816         -    assert( SQLITE_FullFSync==PAGER_FULLFSYNC );
  1817         -    assert( SQLITE_CkptFullFSync==PAGER_CKPT_FULLFSYNC );
  1818         -    assert( SQLITE_CacheSpill==PAGER_CACHESPILL );
  1819         -    assert( (PAGER_FULLFSYNC | PAGER_CKPT_FULLFSYNC | PAGER_CACHESPILL)
  1820         -             ==  PAGER_FLAGS_MASK );
  1821         -    sqlite3BtreeSetPagerFlags(pDb->pBt,
  1822         -                     pDb->safety_level | (db->flags & PAGER_FLAGS_MASK) );
  1823         -  }
  1824         -#endif
  1825   1837   pragma_out:
  1826   1838     sqlite3DbFree(db, zLeft);
  1827   1839     sqlite3DbFree(db, zRight);
  1828   1840   }
  1829   1841   
  1830   1842   #endif /* SQLITE_OMIT_PRAGMA */

Changes to test/pragma2.test.

   117    117     } {9 9}
   118    118   }
   119    119   
   120    120   # Default setting of PRAGMA cache_spill is always ON
   121    121   #
   122    122   db close
   123    123   delete_file test.db test.db-journal
          124  +delete_file test2.db test2.db-journal
   124    125   sqlite3 db test.db
   125    126   do_execsql_test pragma2-4.1 {
   126    127     PRAGMA cache_spill;
   127    128     PRAGMA main.cache_spill;
   128    129     PRAGMA temp.cache_spill;
   129    130   } {1 1 1}
   130    131   do_execsql_test pragma2-4.2 {
................................................................................
   143    144     INSERT INTO t1 SELECT a+2, randomblob(400), a+2, randomblob(400) FROM t1;
   144    145     INSERT INTO t1 SELECT a+4, randomblob(400), a+4, randomblob(400) FROM t1;
   145    146     INSERT INTO t1 SELECT a+8, randomblob(400), a+8, randomblob(400) FROM t1;
   146    147     INSERT INTO t1 SELECT a+16, randomblob(400), a+16, randomblob(400) FROM t1;
   147    148     INSERT INTO t1 SELECT a+32, randomblob(400), a+32, randomblob(400) FROM t1;
   148    149     INSERT INTO t1 SELECT a+64, randomblob(400), a+64, randomblob(400) FROM t1;
   149    150     COMMIT;
          151  +  ATTACH 'test2.db' AS aux1;
          152  +  CREATE TABLE aux1.t2(a INTEGER PRIMARY KEY, b, c, d);
          153  +  INSERT INTO t2 SELECT * FROM t1;
          154  +  DETACH aux1;
   150    155     PRAGMA cache_spill=ON;
   151    156   } {}
   152    157   do_test pragma2-4.4 {
   153    158     db eval {
   154    159       BEGIN;
   155    160       UPDATE t1 SET c=c+1;
   156    161       PRAGMA lock_status;
................................................................................
   161    166       COMMIT;
   162    167       PRAGMA cache_spill=OFF;
   163    168       BEGIN;
   164    169       UPDATE t1 SET c=c-1;
   165    170       PRAGMA lock_status;
   166    171     }
   167    172   } {main reserved temp unknown}   ;# No cache spill, so no exclusive lock
          173  +
          174  +# Verify that newly attached databases inherit the cache_spill=OFF
          175  +# setting.
          176  +#
          177  +do_execsql_test pragma2-4.6 {
          178  +  COMMIT;
          179  +  ATTACH 'test2.db' AS aux1;
          180  +  PRAGMA aux1.cache_size=50;
          181  +  BEGIN;
          182  +  UPDATE t2 SET c=c+1;
          183  +  PRAGMA lock_status;
          184  +} {main unlocked temp unknown aux1 reserved}
          185  +do_execsql_test pragma2-4.7 {
          186  +  COMMIT;
          187  +  PRAGMA cache_spill=ON; -- Applies to all databases
          188  +  BEGIN;
          189  +  UPDATE t2 SET c=c-1;
          190  +  PRAGMA lock_status;
          191  +} {main unlocked temp unknown aux1 exclusive}
          192  +   
   168    193   
   169    194   finish_test