/ Check-in [5c59f469]
Login

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

Overview
Comment:Add "pragma journal_size_limit", used to limit the space consumed by persistent journal files left in the file-system after a transaction has concluded in exclusive (or journal_mode=persist) mode. (CVS 5185)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 5c59f469d0321c6a2e702ca2c61db012f63aeecc
User & Date: danielk1977 2008-06-04 06:45:59
Context
2008-06-04
14:20
Fix a bug in the R-Tree documentation. (CVS 5186) check-in: bb445a4b user: drh tags: trunk
06:45
Add "pragma journal_size_limit", used to limit the space consumed by persistent journal files left in the file-system after a transaction has concluded in exclusive (or journal_mode=persist) mode. (CVS 5185) check-in: 5c59f469 user: danielk1977 tags: trunk
2008-06-03
07:34
Ensure that vacuum3.test closes all opened database connections. Fix for #3157. (CVS 5184) check-in: 654e3b3d user: danielk1977 tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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.450 2008/05/21 15:38:15 drh Exp $
           21  +** @(#) $Id: pager.c,v 1.451 2008/06/04 06:45:59 danielk1977 Exp $
    22     22   */
    23     23   #ifndef SQLITE_OMIT_DISKIO
    24     24   #include "sqliteInt.h"
    25     25   #include <assert.h>
    26     26   #include <string.h>
    27     27   
    28     28   /*
................................................................................
   402    402     Pager *pNext;               /* Doubly linked list of pagers on which */
   403    403     Pager *pPrev;               /* sqlite3_release_memory() will work */
   404    404     volatile int iInUseMM;      /* Non-zero if unavailable to MM */
   405    405     volatile int iInUseDB;      /* Non-zero if in sqlite3_release_memory() */
   406    406   #endif
   407    407     char *pTmpSpace;            /* Pager.pageSize bytes of space for tmp use */
   408    408     char dbFileVers[16];        /* Changes whenever database file changes */
          409  +  i64 journalSizeLimit;       /* Size limit for persistent journal files */
   409    410   };
   410    411   
   411    412   /*
   412    413   ** The following global variables hold counters used for
   413    414   ** testing purposes only.  These variables do not exist in
   414    415   ** a non-testing build.  These variables are not thread-safe.
   415    416   */
................................................................................
   970    971   ** transaction.
   971    972   */
   972    973   static int zeroJournalHdr(Pager *pPager, int doTruncate){
   973    974     int rc = SQLITE_OK;
   974    975     static const char zeroHdr[28];
   975    976   
   976    977     if( pPager->journalOff ){
          978  +    i64 iLimit = pPager->journalSizeLimit;
          979  +
   977    980       IOTRACE(("JZEROHDR %p\n", pPager))
   978         -    if( doTruncate ){
          981  +    if( doTruncate || iLimit==0 ){
   979    982         rc = sqlite3OsTruncate(pPager->jfd, 0);
   980    983       }else{
   981    984         rc = sqlite3OsWrite(pPager->jfd, zeroHdr, sizeof(zeroHdr), 0);
   982    985       }
   983    986       if( rc==SQLITE_OK ){
   984    987         rc = sqlite3OsSync(pPager->jfd, SQLITE_SYNC_DATAONLY|pPager->sync_flags);
   985    988       }
          989  +
          990  +    /* At this point the transaction is committed but the write lock 
          991  +    ** is still held on the file. If there is a size limit configured for 
          992  +    ** the persistent journal and the journal file currently consumes more
          993  +    ** space than that limit allows for, truncate it now. There is no need
          994  +    ** to sync the file following this operation.
          995  +    */
          996  +    if( rc==SQLITE_OK && iLimit>0 ){
          997  +      i64 sz;
          998  +      rc = sqlite3OsFileSize(pPager->jfd, &sz);
          999  +      if( rc==SQLITE_OK && sz>iLimit ){
         1000  +        rc = sqlite3OsTruncate(pPager->jfd, iLimit);
         1001  +      }
         1002  +    }
   986   1003     }
   987   1004     return rc;
   988   1005   }
   989   1006   
   990   1007   /*
   991   1008   ** The journal file must be open when this routine is called. A journal
   992   1009   ** header (JOURNAL_HDR_SZ bytes) is written into the journal file at the
................................................................................
  2347   2364     pPager->noSync = pPager->tempFile || !useJournal;
  2348   2365     pPager->fullSync = (pPager->noSync?0:1);
  2349   2366     pPager->sync_flags = SQLITE_SYNC_NORMAL;
  2350   2367     /* pPager->pFirst = 0; */
  2351   2368     /* pPager->pFirstSynced = 0; */
  2352   2369     /* pPager->pLast = 0; */
  2353   2370     pPager->nExtra = FORCE_ALIGNMENT(nExtra);
         2371  +  pPager->journalSizeLimit = SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT;
  2354   2372     assert(pPager->fd->pMethods||memDb||tempFile);
  2355   2373     if( !memDb ){
  2356   2374       setSectorSize(pPager);
  2357   2375     }
  2358   2376     /* pPager->pBusyHandler = 0; */
  2359   2377     /* memset(pPager->aHash, 0, sizeof(pPager->aHash)); */
  2360   2378     *ppPager = pPager;
................................................................................
  5301   5319     assert( PAGER_JOURNALMODE_QUERY<0 );
  5302   5320     assert( PAGER_JOURNALMODE_DELETE>=0 && PAGER_JOURNALMODE_PERSIST>=0 );
  5303   5321     if( eMode>=0 ){
  5304   5322       pPager->journalMode = eMode;
  5305   5323     }
  5306   5324     return (int)pPager->journalMode;
  5307   5325   }
         5326  +
         5327  +/*
         5328  +** Get/set the size-limit used for persistent journal files.
         5329  +*/
         5330  +i64 sqlite3PagerJournalSizeLimit(Pager *pPager, i64 iLimit){
         5331  +  if( iLimit>=-1 ){
         5332  +    pPager->journalSizeLimit = iLimit;
         5333  +  }
         5334  +  return pPager->journalSizeLimit;
         5335  +}
  5308   5336   
  5309   5337   #ifdef SQLITE_TEST
  5310   5338   /*
  5311   5339   ** Print a listing of all referenced pages and their ref count.
  5312   5340   */
  5313   5341   void sqlite3PagerRefdump(Pager *pPager){
  5314   5342     PgHdr *pPg;

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.73 2008/05/27 18:11:45 shane Exp $
           16  +** @(#) $Id: pager.h,v 1.74 2008/06/04 06:45:59 danielk1977 Exp $
    17     17   */
    18     18   
    19     19   #ifndef _PAGER_H_
    20     20   #define _PAGER_H_
    21     21   
           22  +/*
           23  +** If defined as non-zero, auto-vacuum is enabled by default. Otherwise
           24  +** it must be turned on for each database using "PRAGMA auto_vacuum = 1".
           25  +*/
           26  +#ifndef SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT
           27  +  #define SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT -1
           28  +#endif
           29  +
    22     30   /*
    23     31   ** The type used to represent a page number.  The first page in a file
    24     32   ** is called page 1.  0 is used to represent "not a page".
    25     33   */
    26     34   typedef u32 Pgno;
    27     35   
    28     36   /*
................................................................................
    98    106   const char *sqlite3PagerJournalname(Pager*);
    99    107   int sqlite3PagerNosync(Pager*);
   100    108   int sqlite3PagerMovepage(Pager*,DbPage*,Pgno);
   101    109   void *sqlite3PagerGetData(DbPage *); 
   102    110   void *sqlite3PagerGetExtra(DbPage *); 
   103    111   int sqlite3PagerLockingMode(Pager *, int);
   104    112   int sqlite3PagerJournalMode(Pager *, int);
          113  +i64 sqlite3PagerJournalSizeLimit(Pager *, i64);
   105    114   void *sqlite3PagerTempSpace(Pager*);
   106    115   int sqlite3PagerSync(Pager *pPager);
   107    116   
   108    117   #if defined(SQLITE_ENABLE_MEMORY_MANAGEMENT) && !defined(SQLITE_OMIT_DISKIO)
   109    118     int sqlite3PagerReleaseMemory(int);
   110    119   #endif
   111    120   

Changes to src/pragma.c.

     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     12   ** This file contains code used to implement the PRAGMA command.
    13     13   **
    14         -** $Id: pragma.c,v 1.177 2008/05/15 17:48:20 danielk1977 Exp $
           14  +** $Id: pragma.c,v 1.178 2008/06/04 06:45:59 danielk1977 Exp $
    15     15   */
    16     16   #include "sqliteInt.h"
    17     17   #include <ctype.h>
    18     18   
    19     19   /* Ignore this whole file if pragmas are disabled
    20     20   */
    21     21   #if !defined(SQLITE_OMIT_PRAGMA) && !defined(SQLITE_OMIT_PARSER)
................................................................................
   457    457         int n = strlen(zRight);
   458    458         eMode = 2;
   459    459         while( eMode>=0 && sqlite3StrNICmp(zRight, azModeName[eMode], n)!=0 ){
   460    460           eMode--;
   461    461         }
   462    462       }
   463    463       if( pId2->n==0 && eMode==PAGER_JOURNALMODE_QUERY ){
   464         -      /* Simple "PRAGMA persistent_journal;" statement. This is a query for
          464  +      /* Simple "PRAGMA journal_mode;" statement. This is a query for
   465    465         ** the current default journal mode (which may be different to
   466    466         ** the journal-mode of the main database).
   467    467         */
   468    468         eMode = db->dfltJournalMode;
   469    469       }else{
   470    470         Pager *pPager;
   471    471         if( pId2->n==0 ){
................................................................................
   495    495                 || eMode==PAGER_JOURNALMODE_OFF );
   496    496       sqlite3VdbeSetNumCols(v, 1);
   497    497       sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "journal_mode", P4_STATIC);
   498    498       sqlite3VdbeAddOp4(v, OP_String8, 0, 1, 0, 
   499    499              azModeName[eMode], P4_STATIC);
   500    500       sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 1);
   501    501     }else
          502  +
          503  +  /*
          504  +  **  PRAGMA [database.]journal_size_limit
          505  +  **  PRAGMA [database.]journal_size_limit=N
          506  +  **
          507  +  ** Get or set the (boolean) value of the database 'auto-vacuum' parameter.
          508  +  */
          509  +  if( sqlite3StrICmp(zLeft,"journal_size_limit")==0 ){
          510  +    Pager *pPager = sqlite3BtreePager(pDb->pBt);
          511  +    i64 iLimit = -2;
          512  +    if( zRight ){
          513  +      int iLimit32 = atoi(zRight);
          514  +      if( iLimit32<-1 ){
          515  +        iLimit32 = -1;
          516  +      }
          517  +      iLimit = iLimit32;
          518  +    }
          519  +    iLimit = sqlite3PagerJournalSizeLimit(pPager, iLimit);
          520  +    returnSingleInt(pParse, "journal_size_limit", (int)iLimit);
          521  +  }else
          522  +
   502    523   #endif /* SQLITE_OMIT_PAGER_PRAGMAS */
   503    524   
   504    525     /*
   505    526     **  PRAGMA [database.]auto_vacuum
   506    527     **  PRAGMA [database.]auto_vacuum=N
   507    528     **
   508    529     ** Get or set the (boolean) value of the database 'auto-vacuum' parameter.

Changes to test/jrnlmode.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library. The focus
    12     12   # of these tests is the journal mode pragma.
    13     13   #
    14         -# $Id: jrnlmode.test,v 1.3 2008/05/20 07:05:09 danielk1977 Exp $
           14  +# $Id: jrnlmode.test,v 1.4 2008/06/04 06:46:00 danielk1977 Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19     19   ifcapable {!pager_pragmas} {
    20     20     finish_test
    21     21     return
................................................................................
   246    246     # is not a problem when journal_mode=off.
   247    247     do_test jrnlmode-4.4 {
   248    248       execsql { DELETE FROM abc }
   249    249     } {}
   250    250   
   251    251     integrity_check jrnlmode-4.5
   252    252   }
          253  +
          254  +#------------------------------------------------------------------------
          255  +# The following test caes, jrnlmode-5.*, test the journal_size_limit
          256  +# pragma.
          257  +ifcapable pragma {
          258  +  db close
          259  +  file delete -force test.db test2.db test3.db
          260  +  sqlite3 db test.db
          261  +
          262  +  do_test jrnlmode-5.1 {
          263  +    execsql {pragma page_size=1024}
          264  +    execsql {pragma journal_mode=persist}
          265  +  } {persist}
          266  +
          267  +  do_test jrnlmode-5.2 {
          268  +    execsql { PRAGMA journal_size_limit }
          269  +  } {-1}
          270  +  do_test jrnlmode-5.3 {
          271  +    execsql { 
          272  +      ATTACH 'test2.db' AS aux;
          273  +      PRAGMA aux.journal_size_limit;
          274  +    }
          275  +  } {-1}
          276  +  do_test jrnlmode-5.4 {
          277  +    execsql { PRAGMA aux.journal_size_limit = 10240 }
          278  +  } {10240}
          279  +  do_test jrnlmode-5.5 {
          280  +    execsql { PRAGMA main.journal_size_limit = 20480 }
          281  +  } {20480}
          282  +  do_test jrnlmode-5.6 {
          283  +    execsql { PRAGMA journal_size_limit }
          284  +  } {20480}
          285  +  do_test jrnlmode-5.7 {
          286  +    execsql { PRAGMA aux.journal_size_limit }
          287  +  } {10240}
          288  +
          289  +  do_test jrnlmode-5.8 {
          290  +    execsql { ATTACH 'test3.db' AS aux2 }
          291  +  } {}
          292  +
          293  +  do_test jrnlmode-5.9 {
          294  +    execsql {
          295  +      CREATE TABLE main.t1(a, b, c);
          296  +      CREATE TABLE aux.t2(a, b, c);
          297  +      CREATE TABLE aux2.t3(a, b, c);
          298  +    }
          299  +  } {}
          300  +  do_test jrnlmode-5.10 {
          301  +    list \
          302  +      [file exists test.db-journal]  \
          303  +      [file exists test2.db-journal] \
          304  +      [file exists test3.db-journal]
          305  +  } {1 1 1}
          306  +  do_test jrnlmode-5.11 {
          307  +    execsql {
          308  +      BEGIN;
          309  +      INSERT INTO t3 VALUES(randomblob(1000),randomblob(1000),randomblob(1000));
          310  +      INSERT INTO t3 
          311  +          SELECT randomblob(1000),randomblob(1000),randomblob(1000) FROM t3;
          312  +      INSERT INTO t3 
          313  +          SELECT randomblob(1000),randomblob(1000),randomblob(1000) FROM t3;
          314  +      INSERT INTO t3 
          315  +          SELECT randomblob(1000),randomblob(1000),randomblob(1000) FROM t3;
          316  +      INSERT INTO t3 
          317  +          SELECT randomblob(1000),randomblob(1000),randomblob(1000) FROM t3;
          318  +      INSERT INTO t3 
          319  +          SELECT randomblob(1000),randomblob(1000),randomblob(1000) FROM t3;
          320  +      INSERT INTO t2 SELECT * FROM t3;
          321  +      INSERT INTO t1 SELECT * FROM t2;
          322  +      COMMIT;
          323  +    }
          324  +    list \
          325  +      [file exists test.db-journal]  \
          326  +      [file exists test2.db-journal] \
          327  +      [file exists test3.db-journal] \
          328  +      [file size test.db-journal]    \
          329  +      [file size test2.db-journal]   \
          330  +      [file size test3.db-journal]
          331  +  } {1 1 1 0 0 0}
          332  +
          333  +  do_test jrnlmode-5.12 {
          334  +    execsql {
          335  +      BEGIN;
          336  +      UPDATE t1 SET a = randomblob(1000);
          337  +    }
          338  +    expr {[file size test.db-journal]>30000}
          339  +  } {1}
          340  +  do_test jrnlmode-5.13 {
          341  +    execsql COMMIT
          342  +    file size test.db-journal
          343  +  } {20480}
          344  +
          345  +  do_test jrnlmode-5.14 {
          346  +    execsql {
          347  +      BEGIN;
          348  +      UPDATE t2 SET a = randomblob(1000);
          349  +    }
          350  +    expr {[file size test2.db-journal]>30000}
          351  +  } {1}
          352  +  do_test jrnlmode-5.15 {
          353  +    execsql COMMIT
          354  +    file size test2.db-journal
          355  +  } {10240}
          356  +
          357  +  do_test jrnlmode-5.16 {
          358  +    execsql {
          359  +      BEGIN;
          360  +      UPDATE t3 SET a = randomblob(1000);
          361  +    }
          362  +    set journalsize [file size test3.db-journal]
          363  +    expr {$journalsize>30000}
          364  +  } {1}
          365  +  do_test jrnlmode-5.17 {
          366  +    execsql COMMIT
          367  +    file size test3.db-journal
          368  +  } $journalsize
          369  +
          370  +  do_test jrnlmode-5.18 {
          371  +    execsql {
          372  +      PRAGMA journal_size_limit = -4;
          373  +      BEGIN;
          374  +      UPDATE t1 SET a = randomblob(1000);
          375  +    }
          376  +    set journalsize [file size test.db-journal]
          377  +    expr {$journalsize>30000}
          378  +  } {1}
          379  +  do_test jrnlmode-5.19 {
          380  +    execsql COMMIT
          381  +    file size test.db-journal
          382  +  } $journalsize
          383  +}
   253    384   
   254    385   finish_test