SQLite

Check-in [84c557010c]
Login

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

Overview
Comment:For a pager backed by a temp file, store the main journal in memory until it is at least sqlite3_config.nStmtSpill bytes in size. Prevent the backup API from being used to change the page-size of a temp file.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | tempfiles-lazy-open
Files: files | file ages | folders
SHA1: 84c557010c211595d2ec80b62c63af1c7f4714bd
User & Date: dan 2016-04-06 18:20:51.565
Context
2016-04-11
09:39
Update this branch with the latest changes from the trunk. (check-in: 982b753d0b user: dan tags: tempfiles-lazy-open)
2016-04-06
18:20
For a pager backed by a temp file, store the main journal in memory until it is at least sqlite3_config.nStmtSpill bytes in size. Prevent the backup API from being used to change the page-size of a temp file. (check-in: 84c557010c user: dan tags: tempfiles-lazy-open)
16:27
Fix a problem in test script lock.test. Fix minor issues in pager.c. (check-in: 84de8813c3 user: dan tags: tempfiles-lazy-open)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/pager.c.
1166
1167
1168
1169
1170
1171
1172


1173
1174
1175
1176
1177
1178
1179
    if( 0==(dc&(SQLITE_IOCAP_ATOMIC|(szPage>>8)) || nSector>szPage) ){
      return 0;
    }
  }

  return JOURNAL_HDR_SZ(pPager) + JOURNAL_PG_SZ(pPager);
}


#endif

/*
** If SQLITE_CHECK_PAGES is defined then we do some sanity checking
** on the cache using a hash function.  This is used for testing
** and debugging only.
*/







>
>







1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
    if( 0==(dc&(SQLITE_IOCAP_ATOMIC|(szPage>>8)) || nSector>szPage) ){
      return 0;
    }
  }

  return JOURNAL_HDR_SZ(pPager) + JOURNAL_PG_SZ(pPager);
}
#else
# define jrnlBufferSize(x) 0
#endif

/*
** If SQLITE_CHECK_PAGES is defined then we do some sanity checking
** on the cache using a hash function.  This is used for testing
** and debugging only.
*/
5558
5559
5560
5561
5562
5563
5564
5565
5566


5567
5568


5569
5570
5571

5572
5573
5574
5575
5576
5577
5578
5579
5580
5581
5582
5583
5584
5585
5586
5587
5588
5589
    }
  
    /* Open the journal file if it is not already open. */
    if( !isOpen(pPager->jfd) ){
      if( pPager->journalMode==PAGER_JOURNALMODE_MEMORY ){
        sqlite3MemJournalOpen(pPager->jfd);
      }else{
        const int flags =                   /* VFS flags to open journal file */
          SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE|


          (pPager->tempFile ? 
            (SQLITE_OPEN_DELETEONCLOSE|SQLITE_OPEN_TEMP_JOURNAL):


            (SQLITE_OPEN_MAIN_JOURNAL)
          );


        /* Verify that the database still has the same name as it did when
        ** it was originally opened. */
        rc = databaseIsUnmoved(pPager);
        if( rc==SQLITE_OK ){
#ifdef SQLITE_ENABLE_ATOMIC_WRITE
          rc = sqlite3JournalOpen(
              pVfs, pPager->zJournal, pPager->jfd, flags, jrnlBufferSize(pPager)
          );
#else
          rc = sqlite3OsOpen(pVfs, pPager->zJournal, pPager->jfd, flags, 0);
#endif
        }
      }
      assert( rc!=SQLITE_OK || isOpen(pPager->jfd) );
    }
  
  
    /* Write the first journal header to the journal file and open 







<
|
>
>
|
|
>
>
|
|
|
>




<
|
|

<
<
<







5560
5561
5562
5563
5564
5565
5566

5567
5568
5569
5570
5571
5572
5573
5574
5575
5576
5577
5578
5579
5580
5581

5582
5583
5584



5585
5586
5587
5588
5589
5590
5591
    }
  
    /* Open the journal file if it is not already open. */
    if( !isOpen(pPager->jfd) ){
      if( pPager->journalMode==PAGER_JOURNALMODE_MEMORY ){
        sqlite3MemJournalOpen(pPager->jfd);
      }else{

        int flags = SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE;
        int nSpill;

        if( pPager->tempFile ){
          flags |= (SQLITE_OPEN_DELETEONCLOSE|SQLITE_OPEN_TEMP_JOURNAL);
          nSpill = sqlite3Config.nStmtSpill;
        }else{
          flags |= SQLITE_OPEN_MAIN_JOURNAL;
          nSpill = jrnlBufferSize(pPager);
        }
          
        /* Verify that the database still has the same name as it did when
        ** it was originally opened. */
        rc = databaseIsUnmoved(pPager);
        if( rc==SQLITE_OK ){

          rc = sqlite3JournalOpen (
              pVfs, pPager->zJournal, pPager->jfd, flags, nSpill
          );



        }
      }
      assert( rc!=SQLITE_OK || isOpen(pPager->jfd) );
    }
  
  
    /* Write the first journal header to the journal file and open 
6528
6529
6530
6531
6532
6533
6534
6535
6536
6537
6538
6539
6540
6541
6542
6543
6544
6545
  *pnVal += pPager->aStat[eStat - SQLITE_DBSTATUS_CACHE_HIT];
  if( reset ){
    pPager->aStat[eStat - SQLITE_DBSTATUS_CACHE_HIT] = 0;
  }
}

/*
** Return true if this is an in-memory pager.
*/
int sqlite3PagerIsMemdb(Pager *pPager){
  return MEMDB;
}

/*
** Check that there are at least nSavepoint savepoints open. If there are
** currently less than nSavepoints open, then open one or more savepoints
** to make up the difference. If the number of savepoints is already
** equal to nSavepoint, then this function is a no-op.







|


|







6530
6531
6532
6533
6534
6535
6536
6537
6538
6539
6540
6541
6542
6543
6544
6545
6546
6547
  *pnVal += pPager->aStat[eStat - SQLITE_DBSTATUS_CACHE_HIT];
  if( reset ){
    pPager->aStat[eStat - SQLITE_DBSTATUS_CACHE_HIT] = 0;
  }
}

/*
** Return true if this is an in-memory or temp-file backed pager.
*/
int sqlite3PagerIsMemdb(Pager *pPager){
  return pPager->tempFile;
}

/*
** Check that there are at least nSavepoint savepoints open. If there are
** currently less than nSavepoints open, then open one or more savepoints
** to make up the difference. If the number of savepoints is already
** equal to nSavepoint, then this function is a no-op.
Changes to src/test3.c.
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559

560
561
562
563
564
565
566
567
568
569

570
571
572
573
574
575
576
  Tcl_SetResult(interp, zBuf, TCL_VOLATILE);
  return TCL_OK;
}

/*
** Usage:   btree_ismemdb ID
**
** Return true if the B-Tree is in-memory.
*/
static int btree_ismemdb(
  void *NotUsed,
  Tcl_Interp *interp,    /* The TCL interpreter that invoked this command */
  int argc,              /* Number of arguments */
  const char **argv      /* Text of each argument */
){
  Btree *pBt;
  int res;


  if( argc!=2 ){
    Tcl_AppendResult(interp, "wrong # args: should be \"", argv[0],
       " ID\"", 0);
    return TCL_ERROR;
  }
  pBt = sqlite3TestTextToPtr(argv[1]);
  sqlite3_mutex_enter(pBt->db->mutex);
  sqlite3BtreeEnter(pBt);
  res = sqlite3PagerIsMemdb(sqlite3BtreePager(pBt));

  sqlite3BtreeLeave(pBt);
  sqlite3_mutex_leave(pBt->db->mutex);
  Tcl_SetObjResult(interp, Tcl_NewBooleanObj(res));
  return SQLITE_OK;
}

/*







|









>









|
>







543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
  Tcl_SetResult(interp, zBuf, TCL_VOLATILE);
  return TCL_OK;
}

/*
** Usage:   btree_ismemdb ID
**
** Return true if the B-Tree is currently stored entirely in memory.
*/
static int btree_ismemdb(
  void *NotUsed,
  Tcl_Interp *interp,    /* The TCL interpreter that invoked this command */
  int argc,              /* Number of arguments */
  const char **argv      /* Text of each argument */
){
  Btree *pBt;
  int res;
  sqlite3_file *pFile;

  if( argc!=2 ){
    Tcl_AppendResult(interp, "wrong # args: should be \"", argv[0],
       " ID\"", 0);
    return TCL_ERROR;
  }
  pBt = sqlite3TestTextToPtr(argv[1]);
  sqlite3_mutex_enter(pBt->db->mutex);
  sqlite3BtreeEnter(pBt);
  pFile = sqlite3PagerFile(sqlite3BtreePager(pBt));
  res = (pFile->pMethods==0);
  sqlite3BtreeLeave(pBt);
  sqlite3_mutex_leave(pBt->db->mutex);
  Tcl_SetObjResult(interp, Tcl_NewBooleanObj(res));
  return SQLITE_OK;
}

/*
Changes to test/pragma.test.
1079
1080
1081
1082
1083
1084
1085


1086











1087
1088
1089
1090
1091
1092
1093
} {-450}
} ; # ifcapable schema_version

# Check to see if TEMP_STORE is memory or disk.  Return strings
# "memory" or "disk" as appropriate.
#
proc check_temp_store {} {


  db eval {CREATE TEMP TABLE IF NOT EXISTS a(b)}











  db eval {PRAGMA database_list} {
    if {$name=="temp"} {
      set bt [btree_from_db db 1]
      if {[btree_ismemdb $bt]} {
        return "memory"
      }
      return "disk"







>
>
|
>
>
>
>
>
>
>
>
>
>
>







1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
} {-450}
} ; # ifcapable schema_version

# Check to see if TEMP_STORE is memory or disk.  Return strings
# "memory" or "disk" as appropriate.
#
proc check_temp_store {} {
  db eval {
    PRAGMA temp.cache_size = 1;
    CREATE TEMP TABLE IF NOT EXISTS a(b);
    DELETE FROM a;
    INSERT INTO a VALUES(randomblob(1000));
    INSERT INTO a SELECT * FROM a;
    INSERT INTO a SELECT * FROM a;
    INSERT INTO a SELECT * FROM a;
    INSERT INTO a SELECT * FROM a;
    INSERT INTO a SELECT * FROM a;
    INSERT INTO a SELECT * FROM a;
    INSERT INTO a SELECT * FROM a;
    INSERT INTO a SELECT * FROM a;
  }
  db eval {PRAGMA database_list} {
    if {$name=="temp"} {
      set bt [btree_from_db db 1]
      if {[btree_ismemdb $bt]} {
        return "memory"
      }
      return "disk"
Changes to test/temptable2.test.
109
110
111
112
113
114
115



116

117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136


137

138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
  CREATE TEMP TABLE t2(a, b);
  CREATE INDEX i2 ON t2(a, b);
  WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<500 )
  INSERT INTO t2 SELECT randomblob(100), randomblob(100) FROM x;

  SELECT count(*) FROM t2;
  SELECT count(*) FROM t1;



  PRAGMA temp.page_count;

} {500 10 292}

do_execsql_test 4.1.4 { PRAGMA temp.integrity_check } {ok}

#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 5.1.1 {
  PRAGMA main.cache_size = 10;
  PRAGMA temp.cache_size = 10;

  CREATE TEMP TABLE t2(a, b);
  CREATE INDEX i2 ON t2(a, b);
  WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<500 )
  INSERT INTO t2 SELECT randomblob(100), randomblob(100) FROM x;

  CREATE TEMP TABLE t1(a, b);
  CREATE INDEX i1 ON t1(a, b);
  INSERT INTO t1 VALUES(1, 2);



  PRAGMA temp.page_count;

} {286}

do_execsql_test 5.1.2 {
  BEGIN;
    UPDATE t1 SET a=2;
    UPDATE t2 SET a=randomblob(100);
    SELECT count(*) FROM t1;
  ROLLBACK;
} {1}

do_execsql_test 5.1.3 {
  UPDATE t2 SET a=randomblob(100);

  SELECT * FROM t1;
} {1 2}

do_execsql_test 5.1.4 { PRAGMA temp.integrity_check } {ok}

#-------------------------------------------------------------------------
# Test this:
#
#   1. Page is DIRTY at the start of a transaction.
#   2. Page is written out as part of the transaction.
#   3. Page is then read back in.







>
>
>
|
>
|


















|
>
>
|
>
|

|







|





|







109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
  CREATE TEMP TABLE t2(a, b);
  CREATE INDEX i2 ON t2(a, b);
  WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<500 )
  INSERT INTO t2 SELECT randomblob(100), randomblob(100) FROM x;

  SELECT count(*) FROM t2;
  SELECT count(*) FROM t1;
} {500 10}

do_test 4.1.4 {
  set n [db one { PRAGMA temp.page_count }]
  expr ($n >280 && $n < 300) 
} 1

do_execsql_test 4.1.4 { PRAGMA temp.integrity_check } {ok}

#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 5.1.1 {
  PRAGMA main.cache_size = 10;
  PRAGMA temp.cache_size = 10;

  CREATE TEMP TABLE t2(a, b);
  CREATE INDEX i2 ON t2(a, b);
  WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<500 )
  INSERT INTO t2 SELECT randomblob(100), randomblob(100) FROM x;

  CREATE TEMP TABLE t1(a, b);
  CREATE INDEX i1 ON t1(a, b);
  INSERT INTO t1 VALUES(1, 2);
}

do_test 5.1.2 {
  set n [db one { PRAGMA temp.page_count }]
  expr ($n > 280 && $n < 290)
} {1}

do_execsql_test 5.1.3 {
  BEGIN;
    UPDATE t1 SET a=2;
    UPDATE t2 SET a=randomblob(100);
    SELECT count(*) FROM t1;
  ROLLBACK;
} {1}

do_execsql_test 5.1.4 {
  UPDATE t2 SET a=randomblob(100);

  SELECT * FROM t1;
} {1 2}

do_execsql_test 5.1.5 { PRAGMA temp.integrity_check } {ok}

#-------------------------------------------------------------------------
# Test this:
#
#   1. Page is DIRTY at the start of a transaction.
#   2. Page is written out as part of the transaction.
#   3. Page is then read back in.
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213



















































214
215
  ROLLBACK;                          -- step 4

  SELECT count(*) FROM t2;
  SELECT * FROM t1;
} {two 500 two}

#-------------------------------------------------------------------------

reset_db
sqlite3 db ""
do_execsql_test 7.1 {
  PRAGMA auto_vacuum=INCREMENTAL;
  CREATE TABLE t1(x);
  INSERT INTO t1 VALUES(zeroblob(900));
  INSERT INTO t1 VALUES(zeroblob(900));
  INSERT INTO t1 SELECT x FROM t1;
  INSERT INTO t1 SELECT x FROM t1;
  INSERT INTO t1 SELECT x FROM t1;
  INSERT INTO t1 SELECT x FROM t1;
  BEGIN;
  DELETE FROM t1 WHERE rowid%2;
  PRAGMA incremental_vacuum(4);
  ROLLBACK;
  PRAGMA integrity_check;
} {ok}




















































finish_test








|


















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


195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
  ROLLBACK;                          -- step 4

  SELECT count(*) FROM t2;
  SELECT * FROM t1;
} {two 500 two}

#-------------------------------------------------------------------------
#
reset_db
sqlite3 db ""
do_execsql_test 7.1 {
  PRAGMA auto_vacuum=INCREMENTAL;
  CREATE TABLE t1(x);
  INSERT INTO t1 VALUES(zeroblob(900));
  INSERT INTO t1 VALUES(zeroblob(900));
  INSERT INTO t1 SELECT x FROM t1;
  INSERT INTO t1 SELECT x FROM t1;
  INSERT INTO t1 SELECT x FROM t1;
  INSERT INTO t1 SELECT x FROM t1;
  BEGIN;
  DELETE FROM t1 WHERE rowid%2;
  PRAGMA incremental_vacuum(4);
  ROLLBACK;
  PRAGMA integrity_check;
} {ok}

#-------------------------------------------------------------------------
# Try changing the page size using a backup operation when pages are
# stored in main-memory only.
#
reset_db
do_execsql_test 8.1 {
  CREATE TABLE t2(a, b);
  CREATE INDEX i2 ON t2(a, b);
  WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<20 )
  INSERT INTO t2 SELECT randomblob(100), randomblob(100) FROM x;
  PRAGMA page_count;
} {13}

do_test 8.2 {
  sqlite3 tmp ""
  execsql {
    PRAGMA page_size = 8192;
    CREATE TABLE t1(a, b);
    CREATE INDEX i1 ON t1(a, b);
    WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<100 )
    INSERT INTO t1 SELECT randomblob(100), randomblob(100) FROM x;
    PRAGMA page_count;
  } tmp
} {10}

do_test 8.3 {
  sqlite3_backup B tmp main db main
  B step 5
  B finish
} {SQLITE_READONLY}

do_test 8.4 {
  execsql {
    SELECT count(*) FROM t1;
    PRAGMA integrity_check;
    PRAGMA page_size;
  } tmp
} {100 ok 8192}

do_test 8.5 { 
  tmp eval { UPDATE t1 SET a=randomblob(100) }
} {}

do_test 8.6 {
  sqlite3_backup B tmp main db main
  B step 1000
  B finish
} {SQLITE_READONLY}

tmp close

finish_test