/ Check-in [edda2b9e]
Login

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

Overview
Comment:Add support for SQLITE_CHECKPOINT_TRUNCATE.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: edda2b9e7a15ed486de81b10dd9bacd39c571d3f
User & Date: drh 2014-12-02 20:51:52
Context
2014-12-03
15:50
Updates to the documentation for sqlite3_wal_checkpoint_v2() and related interfaces, including adding many requirements marks. check-in: 1e212d98 user: drh tags: trunk
2014-12-02
20:51
Add support for SQLITE_CHECKPOINT_TRUNCATE. check-in: edda2b9e user: drh tags: trunk
19:35
When attempting to restart a wal file, make any required calls to sqlite3_randomness() before waiting on or checking for wal file readers. This restores the behaviour exhibited by the trunk. Closed-Leaf check-in: 6ee08769 user: dan tags: checkpoint-truncate
16:16
Convert two unreachable branches into assert() statements. check-in: 61b31e77 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/main.c.

  1932   1932     if( !sqlite3SafetyCheckOk(db) ) return SQLITE_MISUSE_BKPT;
  1933   1933   #endif
  1934   1934   
  1935   1935     /* Initialize the output variables to -1 in case an error occurs. */
  1936   1936     if( pnLog ) *pnLog = -1;
  1937   1937     if( pnCkpt ) *pnCkpt = -1;
  1938   1938   
  1939         -  assert( SQLITE_CHECKPOINT_FULL>SQLITE_CHECKPOINT_PASSIVE );
  1940         -  assert( SQLITE_CHECKPOINT_FULL<SQLITE_CHECKPOINT_RESTART );
  1941         -  assert( SQLITE_CHECKPOINT_PASSIVE+2==SQLITE_CHECKPOINT_RESTART );
  1942         -  if( eMode<SQLITE_CHECKPOINT_PASSIVE || eMode>SQLITE_CHECKPOINT_RESTART ){
         1939  +  assert( SQLITE_CHECKPOINT_PASSIVE==0 );
         1940  +  assert( SQLITE_CHECKPOINT_FULL==1 );
         1941  +  assert( SQLITE_CHECKPOINT_RESTART==2 );
         1942  +  assert( SQLITE_CHECKPOINT_TRUNCATE==3 );
         1943  +  if( eMode<SQLITE_CHECKPOINT_PASSIVE || eMode>SQLITE_CHECKPOINT_TRUNCATE ){
  1943   1944       return SQLITE_MISUSE;
  1944   1945     }
  1945   1946   
  1946   1947     sqlite3_mutex_enter(db->mutex);
  1947   1948     if( zDb && zDb[0] ){
  1948   1949       iDb = sqlite3FindDbName(db, zDb);
  1949   1950     }

Changes to src/pragma.c.

  2191   2191       }
  2192   2192     }
  2193   2193     break;
  2194   2194   #endif /* SQLITE_OMIT_COMPILEOPTION_DIAGS */
  2195   2195   
  2196   2196   #ifndef SQLITE_OMIT_WAL
  2197   2197     /*
  2198         -  **   PRAGMA [database.]wal_checkpoint = passive|full|restart
         2198  +  **   PRAGMA [database.]wal_checkpoint = passive|full|restart|truncate
  2199   2199     **
  2200   2200     ** Checkpoint the database.
  2201   2201     */
  2202   2202     case PragTyp_WAL_CHECKPOINT: {
  2203   2203       int iBt = (pId2->z?iDb:SQLITE_MAX_ATTACHED);
  2204   2204       int eMode = SQLITE_CHECKPOINT_PASSIVE;
  2205   2205       if( zRight ){
  2206   2206         if( sqlite3StrICmp(zRight, "full")==0 ){
  2207   2207           eMode = SQLITE_CHECKPOINT_FULL;
  2208   2208         }else if( sqlite3StrICmp(zRight, "restart")==0 ){
  2209   2209           eMode = SQLITE_CHECKPOINT_RESTART;
         2210  +      }else if( sqlite3StrICmp(zRight, "truncate")==0 ){
         2211  +        eMode = SQLITE_CHECKPOINT_TRUNCATE;
  2210   2212         }
  2211   2213       }
  2212   2214       sqlite3VdbeSetNumCols(v, 3);
  2213   2215       pParse->nMem = 3;
  2214   2216       sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "busy", SQLITE_STATIC);
  2215   2217       sqlite3VdbeSetColName(v, 1, COLNAME_NAME, "log", SQLITE_STATIC);
  2216   2218       sqlite3VdbeSetColName(v, 2, COLNAME_NAME, "checkpointed", SQLITE_STATIC);

Changes to src/sqlite.h.in.

  7287   7287   **   This mode works the same way as SQLITE_CHECKPOINT_FULL, except after 
  7288   7288   **   checkpointing the log file it blocks (calls the 
  7289   7289   **   [sqlite3_busy_handler|busy-handler callback])
  7290   7290   **   until all readers are reading from the database file only. This ensures 
  7291   7291   **   that the next client to write to the database file restarts the log file 
  7292   7292   **   from the beginning. This call blocks database writers while it is running,
  7293   7293   **   but not database readers.
         7294  +**
         7295  +** <dt>SQLITE_CHECKPOINT_TRUNCATE<dd>
         7296  +**   This mode works the same way as SQLITE_CHECKPOINT_RESTART except that,
         7297  +**   if successful, it also truncates the log file to zero bytes in size.
  7294   7298   ** </dl>
  7295   7299   **
  7296   7300   ** If pnLog is not NULL, then *pnLog is set to the total number of frames in
  7297   7301   ** the log file before returning. If pnCkpt is not NULL, then *pnCkpt is set to
  7298   7302   ** the total number of checkpointed frames (including any that were already
  7299   7303   ** checkpointed when this function is called). *pnLog and *pnCkpt may be
  7300   7304   ** populated even if sqlite3_wal_checkpoint_v2() returns other than SQLITE_OK.
................................................................................
  7302   7306   ** before returning to communicate this to the caller.
  7303   7307   **
  7304   7308   ** All calls obtain an exclusive "checkpoint" lock on the database file. If
  7305   7309   ** any other process is running a checkpoint operation at the same time, the 
  7306   7310   ** lock cannot be obtained and SQLITE_BUSY is returned. Even if there is a 
  7307   7311   ** busy-handler configured, it will not be invoked in this case.
  7308   7312   **
  7309         -** The SQLITE_CHECKPOINT_FULL and RESTART modes also obtain the exclusive 
  7310         -** "writer" lock on the database file. If the writer lock cannot be obtained
  7311         -** immediately, and a busy-handler is configured, it is invoked and the writer
  7312         -** lock retried until either the busy-handler returns 0 or the lock is
  7313         -** successfully obtained. The busy-handler is also invoked while waiting for
         7313  +** The SQLITE_CHECKPOINT_FULL, RESTART and TRUNCATE modes also obtain the 
         7314  +** exclusive "writer" lock on the database file. If the writer lock cannot be
         7315  +** obtained immediately, and a busy-handler is configured, it is invoked and
         7316  +** the writer lock retried until either the busy-handler returns 0 or the lock
         7317  +** is successfully obtained. The busy-handler is also invoked while waiting for
  7314   7318   ** database readers as described above. If the busy-handler returns 0 before
  7315   7319   ** the writer lock is obtained or while waiting for database readers, the
  7316   7320   ** checkpoint operation proceeds from that point in the same way as 
  7317   7321   ** SQLITE_CHECKPOINT_PASSIVE - checkpointing as many frames as possible 
  7318   7322   ** without blocking any further. SQLITE_BUSY is returned in this case.
  7319   7323   **
  7320   7324   ** If parameter zDb is NULL or points to a zero length string, then the
................................................................................
  7345   7349   ** CAPI3REF: Checkpoint operation parameters
  7346   7350   **
  7347   7351   ** These constants can be used as the 3rd parameter to
  7348   7352   ** [sqlite3_wal_checkpoint_v2()].  See the [sqlite3_wal_checkpoint_v2()]
  7349   7353   ** documentation for additional information about the meaning and use of
  7350   7354   ** each of these values.
  7351   7355   */
  7352         -#define SQLITE_CHECKPOINT_PASSIVE 0
  7353         -#define SQLITE_CHECKPOINT_FULL    1
  7354         -#define SQLITE_CHECKPOINT_RESTART 2
         7356  +#define SQLITE_CHECKPOINT_PASSIVE  0
         7357  +#define SQLITE_CHECKPOINT_FULL     1
         7358  +#define SQLITE_CHECKPOINT_RESTART  2
         7359  +#define SQLITE_CHECKPOINT_TRUNCATE 3
  7355   7360   
  7356   7361   /*
  7357   7362   ** CAPI3REF: Virtual Table Interface Configuration
  7358   7363   **
  7359   7364   ** This function may be called by either the [xConnect] or [xCreate] method
  7360   7365   ** of a [virtual table] implementation to configure
  7361   7366   ** various facets of the virtual table interface.

Changes to src/test1.c.

  5686   5686     int rc;
  5687   5687   
  5688   5688     int eMode;
  5689   5689     int nLog = -555;
  5690   5690     int nCkpt = -555;
  5691   5691     Tcl_Obj *pRet;
  5692   5692   
  5693         -  const char * aMode[] = { "passive", "full", "restart", 0 };
         5693  +  const char * aMode[] = { "passive", "full", "restart", "truncate", 0 };
  5694   5694     assert( SQLITE_CHECKPOINT_PASSIVE==0 );
  5695   5695     assert( SQLITE_CHECKPOINT_FULL==1 );
  5696   5696     assert( SQLITE_CHECKPOINT_RESTART==2 );
         5697  +  assert( SQLITE_CHECKPOINT_TRUNCATE==3 );
  5697   5698   
  5698   5699     if( objc!=3 && objc!=4 ){
  5699   5700       Tcl_WrongNumArgs(interp, 1, objv, "DB MODE ?NAME?");
  5700   5701       return TCL_ERROR;
  5701   5702     }
  5702   5703   
  5703   5704     if( objc==4 ){

Changes to src/vdbe.c.

  5720   5720   
  5721   5721     assert( p->readOnly==0 );
  5722   5722     aRes[0] = 0;
  5723   5723     aRes[1] = aRes[2] = -1;
  5724   5724     assert( pOp->p2==SQLITE_CHECKPOINT_PASSIVE
  5725   5725          || pOp->p2==SQLITE_CHECKPOINT_FULL
  5726   5726          || pOp->p2==SQLITE_CHECKPOINT_RESTART
         5727  +       || pOp->p2==SQLITE_CHECKPOINT_TRUNCATE
  5727   5728     );
  5728   5729     rc = sqlite3Checkpoint(db, pOp->p1, pOp->p2, &aRes[1], &aRes[2]);
  5729   5730     if( rc==SQLITE_BUSY ){
  5730   5731       rc = SQLITE_OK;
  5731   5732       aRes[0] = 1;
  5732   5733     }
  5733   5734     for(i=0, pMem = &aMem[pOp->p3]; i<3; i++, pMem++){

Changes to src/wal.c.

  1618   1618   /*
  1619   1619   ** The cache of the wal-index header must be valid to call this function.
  1620   1620   ** Return the page-size in bytes used by the database.
  1621   1621   */
  1622   1622   static int walPagesize(Wal *pWal){
  1623   1623     return (pWal->hdr.szPage&0xfe00) + ((pWal->hdr.szPage&0x0001)<<16);
  1624   1624   }
         1625  +
         1626  +/*
         1627  +** The following is guaranteed when this function is called:
         1628  +**
         1629  +**   a) the WRITER lock is held,
         1630  +**   b) the entire log file has been checkpointed, and
         1631  +**   c) any existing readers are reading exclusively from the database
         1632  +**      file - there are no readers that may attempt to read a frame from
         1633  +**      the log file.
         1634  +**
         1635  +** This function updates the shared-memory structures so that the next
         1636  +** client to write to the database (which may be this one) does so by
         1637  +** writing frames into the start of the log file.
         1638  +**
         1639  +** The value of parameter salt1 is used as the aSalt[1] value in the 
         1640  +** new wal-index header. It should be passed a pseudo-random value (i.e. 
         1641  +** one obtained from sqlite3_randomness()).
         1642  +*/
         1643  +static void walRestartHdr(Wal *pWal, u32 salt1){
         1644  +  volatile WalCkptInfo *pInfo = walCkptInfo(pWal);
         1645  +  int i;                          /* Loop counter */
         1646  +  u32 *aSalt = pWal->hdr.aSalt;   /* Big-endian salt values */
         1647  +  pWal->nCkpt++;
         1648  +  pWal->hdr.mxFrame = 0;
         1649  +  sqlite3Put4byte((u8*)&aSalt[0], 1 + sqlite3Get4byte((u8*)&aSalt[0]));
         1650  +  memcpy(&pWal->hdr.aSalt[1], &salt1, 4);
         1651  +  walIndexWriteHdr(pWal);
         1652  +  pInfo->nBackfill = 0;
         1653  +  pInfo->aReadMark[1] = 0;
         1654  +  for(i=2; i<WAL_NREADER; i++) pInfo->aReadMark[i] = READMARK_NOT_USED;
         1655  +  assert( pInfo->aReadMark[0]==0 );
         1656  +}
  1625   1657   
  1626   1658   /*
  1627   1659   ** Copy as much content as we can from the WAL back into the database file
  1628   1660   ** in response to an sqlite3_wal_checkpoint() request or the equivalent.
  1629   1661   **
  1630   1662   ** The amount of information copies from WAL to database might be limited
  1631   1663   ** by active readers.  This routine will never overwrite a database page
................................................................................
  1771   1803   
  1772   1804     if( rc==SQLITE_BUSY ){
  1773   1805       /* Reset the return code so as not to report a checkpoint failure
  1774   1806       ** just because there are active readers.  */
  1775   1807       rc = SQLITE_OK;
  1776   1808     }
  1777   1809   
  1778         -  /* If this is an SQLITE_CHECKPOINT_RESTART operation, and the entire wal
  1779         -  ** file has been copied into the database file, then block until all
  1780         -  ** readers have finished using the wal file. This ensures that the next
  1781         -  ** process to write to the database restarts the wal file.
         1810  +  /* If this is an SQLITE_CHECKPOINT_RESTART or TRUNCATE operation, and the
         1811  +  ** entire wal file has been copied into the database file, then block 
         1812  +  ** until all readers have finished using the wal file. This ensures that 
         1813  +  ** the next process to write to the database restarts the wal file.
  1782   1814     */
  1783   1815     if( rc==SQLITE_OK && eMode!=SQLITE_CHECKPOINT_PASSIVE ){
  1784   1816       assert( pWal->writeLock );
  1785   1817       if( pInfo->nBackfill<pWal->hdr.mxFrame ){
  1786   1818         rc = SQLITE_BUSY;
  1787         -    }else if( eMode==SQLITE_CHECKPOINT_RESTART ){
         1819  +    }else if( eMode>=SQLITE_CHECKPOINT_RESTART ){
         1820  +      u32 salt1;
         1821  +      sqlite3_randomness(4, &salt1);
  1788   1822         assert( mxSafeFrame==pWal->hdr.mxFrame );
  1789   1823         rc = walBusyLock(pWal, xBusy, pBusyArg, WAL_READ_LOCK(1), WAL_NREADER-1);
  1790   1824         if( rc==SQLITE_OK ){
         1825  +        if( eMode==SQLITE_CHECKPOINT_TRUNCATE ){
         1826  +          /* If this is a TRUNCATE checkpoint, also truncate the wal file
         1827  +          ** to zero bytes in size on disk. 
         1828  +          **
         1829  +          ** In theory, it might be safe to do this without updating the
         1830  +          ** wal-index header in shared memory, as all subsequent reader or
         1831  +          ** writer clients should see that the entire log file has been
         1832  +          ** checkpointed and behave accordingly. This seems unsafe though,
         1833  +          ** as it would leave the system in a state where the contents of
         1834  +          ** the wal-index header do not match the contents of the 
         1835  +          ** file-system. To avoid this, update the wal-index header to
         1836  +          ** indicate that the log file contains zero valid frames.  */
         1837  +          walRestartHdr(pWal, salt1);
         1838  +          rc = sqlite3OsTruncate(pWal->pWalFd, 0);
         1839  +        }
  1791   1840           walUnlockExclusive(pWal, WAL_READ_LOCK(1), WAL_NREADER-1);
  1792   1841         }
  1793   1842       }
  1794   1843     }
  1795   1844   
  1796   1845    walcheckpoint_out:
  1797   1846     walIteratorFree(pIter);
................................................................................
  2569   2618       pWal->hdr.aFrameCksum[1] = aWalData[2];
  2570   2619       walCleanupHash(pWal);
  2571   2620     }
  2572   2621   
  2573   2622     return rc;
  2574   2623   }
  2575   2624   
  2576         -
  2577   2625   /*
  2578   2626   ** This function is called just before writing a set of frames to the log
  2579   2627   ** file (see sqlite3WalFrames()). It checks to see if, instead of appending
  2580   2628   ** to the current log file, it is possible to overwrite the start of the
  2581   2629   ** existing log file with the new frames (i.e. "reset" the log). If so,
  2582   2630   ** it sets pWal->hdr.mxFrame to 0. Otherwise, pWal->hdr.mxFrame is left
  2583   2631   ** unchanged.
................................................................................
  2602   2650           ** readers are currently using the WAL), then the transactions
  2603   2651           ** frames will overwrite the start of the existing log. Update the
  2604   2652           ** wal-index header to reflect this.
  2605   2653           **
  2606   2654           ** In theory it would be Ok to update the cache of the header only
  2607   2655           ** at this point. But updating the actual wal-index header is also
  2608   2656           ** safe and means there is no special case for sqlite3WalUndo()
  2609         -        ** to handle if this transaction is rolled back.
  2610         -        */
  2611         -        int i;                    /* Loop counter */
  2612         -        u32 *aSalt = pWal->hdr.aSalt;       /* Big-endian salt values */
  2613         -
  2614         -        pWal->nCkpt++;
  2615         -        pWal->hdr.mxFrame = 0;
  2616         -        sqlite3Put4byte((u8*)&aSalt[0], 1 + sqlite3Get4byte((u8*)&aSalt[0]));
  2617         -        aSalt[1] = salt1;
  2618         -        walIndexWriteHdr(pWal);
  2619         -        pInfo->nBackfill = 0;
  2620         -        pInfo->aReadMark[1] = 0;
  2621         -        for(i=2; i<WAL_NREADER; i++) pInfo->aReadMark[i] = READMARK_NOT_USED;
  2622         -        assert( pInfo->aReadMark[0]==0 );
         2657  +        ** to handle if this transaction is rolled back.  */
         2658  +        walRestartHdr(pWal, salt1);
  2623   2659           walUnlockExclusive(pWal, WAL_READ_LOCK(1), WAL_NREADER-1);
  2624   2660         }else if( rc!=SQLITE_BUSY ){
  2625   2661           return rc;
  2626   2662         }
  2627   2663       }
  2628   2664       walUnlockShared(pWal, WAL_READ_LOCK(0));
  2629   2665       pWal->readLock = -1;

Changes to test/wal5.test.

    51     51       proc do_wal_checkpoint { dbhandle args } {
    52     52         set a(-mode) passive
    53     53         array set a $args
    54     54         foreach key [array names a] {
    55     55           if {[lsearch {-mode -db} $key]<0} { error "unknown switch: $key" }
    56     56         }
    57     57   
    58         -      if {$a(-mode)!="restart" && $a(-mode)!="full"} { set a(-mode) passive }
           58  +      set vals {restart full truncate}
           59  +      if {[lsearch -exact $vals $a(-mode)]<0} { set a(-mode) passive }
    59     60   
    60     61         set cmd [list sqlite3_wal_checkpoint_v2 $dbhandle $a(-mode)]
    61     62         if {[info exists a(-db)]} { lappend sql $a(-db) }
    62     63   
    63     64         uplevel $cmd
    64     65       }
    65     66     }
................................................................................
   274    275       6   FULL      3   {0 4 4}   2
   275    276   
   276    277       7   RESTART   -   {0 4 4}   3
   277    278       8   RESTART   1   {1 3 3}   1
   278    279       9   RESTART   2   {1 4 3}   2
   279    280       10  RESTART   3   {1 4 4}   3
   280    281   
          282  +    11  TRUNCATE  -   {0 0 0}   3
          283  +    12  TRUNCATE  1   {1 3 3}   1
          284  +    13  TRUNCATE  2   {1 4 3}   2
          285  +    14  TRUNCATE  3   {1 4 4}   3
          286  +
   281    287     } {
   282    288       do_multiclient_test tn {
   283    289         setup_and_attach_aux
   284    290   
   285    291         proc busyhandler {x} {
   286    292           set ::max_busyhandler $x
   287    293           if {$::busy_on!="-" && $x==$::busy_on} { return 1 }
................................................................................
   343    349       code1 {sqlite3 db  test.db}
   344    350       code2 {sqlite3 db2 test.db}
   345    351       code3 {sqlite3 db3 test.db}
   346    352   
   347    353       do_test 3.$tn.5 { sql3 { PRAGMA journal_mode } } {wal}
   348    354   
   349    355       do_test 3.$tn.6 { code3 { do_wal_checkpoint db3 } } {0 0 0}
          356  +  }
          357  +
          358  +  # Test SQLITE_CHECKPOINT_TRUNCATE.
          359  +  #
          360  +  do_multiclient_test tn {
          361  +
          362  +    code1 $do_wal_checkpoint
          363  +    code2 $do_wal_checkpoint
          364  +    code3 $do_wal_checkpoint
          365  +
          366  +    do_test 3.$tn.1 {
          367  +      sql1 {
          368  +        PRAGMA page_size = 1024;
          369  +        PRAGMA journal_mode = WAL;
          370  +        PRAGMA synchronous = normal;
          371  +        CREATE TABLE t1(x, y);
          372  +        CREATE INDEX i1 ON t1(x, y);
          373  +        INSERT INTO t1 VALUES(1, 2);
          374  +        INSERT INTO t1 VALUES(3, 4);
          375  +      }
          376  +      file size test.db-wal
          377  +    } [wal_file_size 8 1024]
          378  +
          379  +    do_test 3.$tn.2 { do_wal_checkpoint db -mode truncate } {0 0 0}
          380  +    do_test 3.$tn.3 { file size test.db-wal } 0
          381  +
          382  +    do_test 3.$tn.4 {
          383  +      sql2 { SELECT * FROM t1 }
          384  +    } {1 2 3 4}
          385  +
          386  +    do_test 3.$tn.5 {
          387  +      sql2 { INSERT INTO t1 VALUES('a', 'b') }
          388  +      file size test.db-wal
          389  +    } [wal_file_size 2 1024]
          390  +
   350    391     }
   351    392   }
   352    393   
   353    394   
   354    395   finish_test