/ Check-in [d07c4331]
Login
Home Timeline Branches Tags Tickets Wiki More...

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 Unified Diffs Ignore Whitespace Patch

Changes to src/attach.c. 

154
155
156
157
158
159
160

161
162
163
164
165
166
167

        "attached databases must use the same text encoding as main database");
      rc = SQLITE_ERROR;
    }
    pPager = sqlite3BtreePager(aNew->pBt);
    sqlite3PagerLockingMode(pPager, db->dfltLockMode);
    sqlite3BtreeSecureDelete(aNew->pBt,
                             sqlite3BtreeSecureDelete(db->aDb[0].pBt,-1) );

  }
  aNew->safety_level = 3;
  aNew->zName = sqlite3DbStrDup(db, zName);
  if( rc==SQLITE_OK && aNew->zName==0 ){
    rc = SQLITE_NOMEM;
  }









>








154
155
156
157
158
159
160
161
162
163
164
165
166
167
168

        "attached databases must use the same text encoding as main database");
      rc = SQLITE_ERROR;
    }
    pPager = sqlite3BtreePager(aNew->pBt);
    sqlite3PagerLockingMode(pPager, db->dfltLockMode);
    sqlite3BtreeSecureDelete(aNew->pBt,
                             sqlite3BtreeSecureDelete(db->aDb[0].pBt,-1) );
    sqlite3BtreeSetPagerFlags(aNew->pBt, 3 | (db->flags & PAGER_FLAGS_MASK));
  }
  aNew->safety_level = 3;
  aNew->zName = sqlite3DbStrDup(db, zName);
  if( rc==SQLITE_OK && aNew->zName==0 ){
    rc = SQLITE_NOMEM;
  }

Changes to src/pragma.c. 

153
154
155
156
157
158
159




























160
161
162
163
164
165
166
...
963
964
965
966
967
968
969

970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
....
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830

    memcpy(pI64, &value, sizeof(value));
  }
  sqlite3VdbeAddOp4(v, OP_Int64, 0, mem, 0, (char*)pI64, P4_INT64);
  sqlite3VdbeSetNumCols(v, 1);
  sqlite3VdbeSetColName(v, 0, COLNAME_NAME, zLabel, SQLITE_STATIC);
  sqlite3VdbeAddOp2(v, OP_ResultRow, mem, 1);
}





























#ifndef SQLITE_OMIT_FLAG_PRAGMAS
/*
** Check to see if zRight and zLeft refer to a pragma that queries
** or changes one of the flags in db->flags.  Return 1 if so and 0 if not.
** Also, implement the pragma.
*/
................................................................................
      returnSingleInt(pParse, "synchronous", pDb->safety_level-1);
    }else{
      if( !db->autoCommit ){
        sqlite3ErrorMsg(pParse, 
            "Safety level may not be changed inside a transaction");
      }else{
        pDb->safety_level = getSafetyLevel(zRight,0,1)+1;

      }
    }
  }else
#endif /* SQLITE_OMIT_PAGER_PRAGMAS */

#ifndef SQLITE_OMIT_FLAG_PRAGMAS
  if( flagPragma(pParse, zLeft, zRight) ){
    /* The flagPragma() subroutine also generates any necessary code
    ** there is nothing more to do here */
  }else
#endif /* SQLITE_OMIT_FLAG_PRAGMAS */

#ifndef SQLITE_OMIT_SCHEMA_PRAGMAS
  /*
  **   PRAGMA table_info(<table>)
  **
................................................................................
#endif
  }else
#endif

 
  {/* Empty ELSE clause */}

  /*
  ** Reset the safety level, in case the fullfsync flag or synchronous
  ** setting changed.
  */
#ifndef SQLITE_OMIT_PAGER_PRAGMAS
  if( db->autoCommit ){
    assert( (pDb->safety_level & PAGER_SYNCHRONOUS_MASK)==pDb->safety_level );
    assert( SQLITE_FullFSync==PAGER_FULLFSYNC );
    assert( SQLITE_CkptFullFSync==PAGER_CKPT_FULLFSYNC );
    assert( SQLITE_CacheSpill==PAGER_CACHESPILL );
    assert( (PAGER_FULLFSYNC | PAGER_CKPT_FULLFSYNC | PAGER_CACHESPILL)
             ==  PAGER_FLAGS_MASK );
    sqlite3BtreeSetPagerFlags(pDb->pBt,
                     pDb->safety_level | (db->flags & PAGER_FLAGS_MASK) );
  }
#endif
pragma_out:
  sqlite3DbFree(db, zLeft);
  sqlite3DbFree(db, zRight);
}

#endif /* SQLITE_OMIT_PRAGMA */








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







 







>







|
<







 







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
...
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006

1007
1008
1009
1010
1011
1012
1013
....
1830
1831
1832
1833
1834
1835
1836
















1837
1838
1839
1840
1841
1842

    memcpy(pI64, &value, sizeof(value));
  }
  sqlite3VdbeAddOp4(v, OP_Int64, 0, mem, 0, (char*)pI64, P4_INT64);
  sqlite3VdbeSetNumCols(v, 1);
  sqlite3VdbeSetColName(v, 0, COLNAME_NAME, zLabel, SQLITE_STATIC);
  sqlite3VdbeAddOp2(v, OP_ResultRow, mem, 1);
}


/*
** Set the safety_level and pager flags for pager iDb.  Or if iDb<0
** set these values for all pagers.
*/
#ifndef SQLITE_OMIT_PAGER_PRAGMAS
static void setAllPagerFlags(sqlite3 *db){
  if( db->autoCommit ){
    Db *pDb = db->aDb;
    int n = db->nDb;
    assert( SQLITE_FullFSync==PAGER_FULLFSYNC );
    assert( SQLITE_CkptFullFSync==PAGER_CKPT_FULLFSYNC );
    assert( SQLITE_CacheSpill==PAGER_CACHESPILL );
    assert( (PAGER_FULLFSYNC | PAGER_CKPT_FULLFSYNC | PAGER_CACHESPILL)
             ==  PAGER_FLAGS_MASK );
    assert( (pDb->safety_level & PAGER_SYNCHRONOUS_MASK)==pDb->safety_level );
    while( (n--) > 0 ){
      if( pDb->pBt ){
        sqlite3BtreeSetPagerFlags(pDb->pBt,
                 pDb->safety_level | (db->flags & PAGER_FLAGS_MASK) );
      }
      pDb++;
    }
  }
}
#endif


#ifndef SQLITE_OMIT_FLAG_PRAGMAS
/*
** Check to see if zRight and zLeft refer to a pragma that queries
** or changes one of the flags in db->flags.  Return 1 if so and 0 if not.
** Also, implement the pragma.
*/
................................................................................
      returnSingleInt(pParse, "synchronous", pDb->safety_level-1);
    }else{
      if( !db->autoCommit ){
        sqlite3ErrorMsg(pParse, 
            "Safety level may not be changed inside a transaction");
      }else{
        pDb->safety_level = getSafetyLevel(zRight,0,1)+1;
        setAllPagerFlags(db);
      }
    }
  }else
#endif /* SQLITE_OMIT_PAGER_PRAGMAS */

#ifndef SQLITE_OMIT_FLAG_PRAGMAS
  if( flagPragma(pParse, zLeft, zRight) ){
    setAllPagerFlags(db);

  }else
#endif /* SQLITE_OMIT_FLAG_PRAGMAS */

#ifndef SQLITE_OMIT_SCHEMA_PRAGMAS
  /*
  **   PRAGMA table_info(<table>)
  **
................................................................................
#endif
  }else
#endif

 
  {/* Empty ELSE clause */}

















pragma_out:
  sqlite3DbFree(db, zLeft);
  sqlite3DbFree(db, zRight);
}

#endif /* SQLITE_OMIT_PRAGMA */

Changes to test/pragma2.test. 

117
118
119
120
121
122
123

124
125
126
127
128
129
130
...
143
144
145
146
147
148
149




150
151
152
153
154
155
156
...
161
162
163
164
165
166
167
168




















169

  } {9 9}
}

# Default setting of PRAGMA cache_spill is always ON
#
db close
delete_file test.db test.db-journal

sqlite3 db test.db
do_execsql_test pragma2-4.1 {
  PRAGMA cache_spill;
  PRAGMA main.cache_spill;
  PRAGMA temp.cache_spill;
} {1 1 1}
do_execsql_test pragma2-4.2 {
................................................................................
  INSERT INTO t1 SELECT a+2, randomblob(400), a+2, randomblob(400) FROM t1;
  INSERT INTO t1 SELECT a+4, randomblob(400), a+4, randomblob(400) FROM t1;
  INSERT INTO t1 SELECT a+8, randomblob(400), a+8, randomblob(400) FROM t1;
  INSERT INTO t1 SELECT a+16, randomblob(400), a+16, randomblob(400) FROM t1;
  INSERT INTO t1 SELECT a+32, randomblob(400), a+32, randomblob(400) FROM t1;
  INSERT INTO t1 SELECT a+64, randomblob(400), a+64, randomblob(400) FROM t1;
  COMMIT;




  PRAGMA cache_spill=ON;
} {}
do_test pragma2-4.4 {
  db eval {
    BEGIN;
    UPDATE t1 SET c=c+1;
    PRAGMA lock_status;
................................................................................
    COMMIT;
    PRAGMA cache_spill=OFF;
    BEGIN;
    UPDATE t1 SET c=c-1;
    PRAGMA lock_status;
  }
} {main reserved temp unknown}   ;# No cache spill, so no exclusive lock





















finish_test








>







 







>
>
>
>







 








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


117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
...
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
...
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194

  } {9 9}
}

# Default setting of PRAGMA cache_spill is always ON
#
db close
delete_file test.db test.db-journal
delete_file test2.db test2.db-journal
sqlite3 db test.db
do_execsql_test pragma2-4.1 {
  PRAGMA cache_spill;
  PRAGMA main.cache_spill;
  PRAGMA temp.cache_spill;
} {1 1 1}
do_execsql_test pragma2-4.2 {
................................................................................
  INSERT INTO t1 SELECT a+2, randomblob(400), a+2, randomblob(400) FROM t1;
  INSERT INTO t1 SELECT a+4, randomblob(400), a+4, randomblob(400) FROM t1;
  INSERT INTO t1 SELECT a+8, randomblob(400), a+8, randomblob(400) FROM t1;
  INSERT INTO t1 SELECT a+16, randomblob(400), a+16, randomblob(400) FROM t1;
  INSERT INTO t1 SELECT a+32, randomblob(400), a+32, randomblob(400) FROM t1;
  INSERT INTO t1 SELECT a+64, randomblob(400), a+64, randomblob(400) FROM t1;
  COMMIT;
  ATTACH 'test2.db' AS aux1;
  CREATE TABLE aux1.t2(a INTEGER PRIMARY KEY, b, c, d);
  INSERT INTO t2 SELECT * FROM t1;
  DETACH aux1;
  PRAGMA cache_spill=ON;
} {}
do_test pragma2-4.4 {
  db eval {
    BEGIN;
    UPDATE t1 SET c=c+1;
    PRAGMA lock_status;
................................................................................
    COMMIT;
    PRAGMA cache_spill=OFF;
    BEGIN;
    UPDATE t1 SET c=c-1;
    PRAGMA lock_status;
  }
} {main reserved temp unknown}   ;# No cache spill, so no exclusive lock

# Verify that newly attached databases inherit the cache_spill=OFF
# setting.
#
do_execsql_test pragma2-4.6 {
  COMMIT;
  ATTACH 'test2.db' AS aux1;
  PRAGMA aux1.cache_size=50;
  BEGIN;
  UPDATE t2 SET c=c+1;
  PRAGMA lock_status;
} {main unlocked temp unknown aux1 reserved}
do_execsql_test pragma2-4.7 {
  COMMIT;
  PRAGMA cache_spill=ON; -- Applies to all databases
  BEGIN;
  UPDATE t2 SET c=c-1;
  PRAGMA lock_status;
} {main unlocked temp unknown aux1 exclusive}
   

finish_test

This page was generated in about 0.012s by Fossil 2.9 [cbca374c37] 2019-06-20 11:30:16