SQLite

Check-in [3c25cb4ab8]
Login

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

Overview
Comment:Avoid invoking the update or pre-update hooks during VACUUM operations.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 3c25cb4ab8885a50e2a485fe76f5ffd5dd8ebe1306aca8c0989e0b7fd7dd18d2
User & Date: dan 2021-02-18 15:59:37.490
Context
2021-02-19
14:32
Add support for "ALTER TABLE ... DROP COLUMN ..." commands. (check-in: c844a331e7 user: dan tags: trunk)
2021-02-18
22:47
Merge changes from trunk into the alter-table-drop-column branch. (check-in: 9ea640073f user: drh tags: alter-table-drop-column)
15:59
Avoid invoking the update or pre-update hooks during VACUUM operations. (check-in: 3c25cb4ab8 user: dan tags: trunk)
15:45
Improvement to the INSERT optimization of check-in [16ac213c57196361] so that it works with SQLITE_ENABLE_HIDDEN_COLUMN but is also easier to maintain and a little faster as well. (check-in: f985a78ecc user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/insert.c.
︙︙
2422
2423
2424
2425
2426
2427
2428

2429
2430
2431
2432
2433
2434
2435

  Table *pTab,                    /* Table being updated */
  int iCur,                       /* Cursor number for table */
  int regData                     /* Data containing new record */
){
  Vdbe *v = pParse->pVdbe;
  int r = sqlite3GetTempReg(pParse);
  assert( !HasRowid(pTab) );

  sqlite3VdbeAddOp2(v, OP_Integer, 0, r);
  sqlite3VdbeAddOp4(v, OP_Insert, iCur, regData, r, (char*)pTab, P4_TABLE);
  sqlite3VdbeChangeP5(v, OPFLAG_ISNOOP);
  sqlite3ReleaseTempReg(pParse, r);
}
#else
# define codeWithoutRowidPreupdate(a,b,c,d)








>








2422
2423
2424
2425
2426
2427
2428
2429
2430
2431
2432
2433
2434
2435
2436

  Table *pTab,                    /* Table being updated */
  int iCur,                       /* Cursor number for table */
  int regData                     /* Data containing new record */
){
  Vdbe *v = pParse->pVdbe;
  int r = sqlite3GetTempReg(pParse);
  assert( !HasRowid(pTab) );
  assert( 0==(pParse->db->mDbFlags & DBFLAG_Vacuum) );
  sqlite3VdbeAddOp2(v, OP_Integer, 0, r);
  sqlite3VdbeAddOp4(v, OP_Insert, iCur, regData, r, (char*)pTab, P4_TABLE);
  sqlite3VdbeChangeP5(v, OPFLAG_ISNOOP);
  sqlite3ReleaseTempReg(pParse, r);
}
#else
# define codeWithoutRowidPreupdate(a,b,c,d)
︙︙
2954
2955
2956
2957
2958
2959
2960

2961
2962
2963
2964
2965



2966

2967

2968
2969
2970
2971
2972
2973
2974

    if( db->mDbFlags & DBFLAG_Vacuum ){
      sqlite3VdbeAddOp1(v, OP_SeekEnd, iDest);
      insFlags = OPFLAG_APPEND|OPFLAG_USESEEKRESULT|OPFLAG_PREFORMAT;
    }else{
      insFlags = OPFLAG_NCHANGE|OPFLAG_LASTROWID|OPFLAG_APPEND|OPFLAG_PREFORMAT;
    }
#ifdef SQLITE_ENABLE_PREUPDATE_HOOK

    sqlite3VdbeAddOp3(v, OP_RowData, iSrc, regData, 1);
    insFlags &= ~OPFLAG_PREFORMAT;
#else
    sqlite3VdbeAddOp3(v, OP_RowCell, iDest, iSrc, regRowid);
#endif



    sqlite3VdbeAddOp4(v, OP_Insert, iDest, regData, regRowid,

        (char*)pDest, P4_TABLE);

    sqlite3VdbeChangeP5(v, insFlags);

    sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1); VdbeCoverage(v);
    sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0);
    sqlite3VdbeAddOp2(v, OP_Close, iDest, 0);
  }else{
    sqlite3TableLock(pParse, iDbDest, pDest->tnum, 1, pDest->zName);








>
|
|
|
<

>
>
>
|
>
|
>








2955
2956
2957
2958
2959
2960
2961
2962
2963
2964
2965

2966
2967
2968
2969
2970
2971
2972
2973
2974
2975
2976
2977
2978
2979
2980

    if( db->mDbFlags & DBFLAG_Vacuum ){
      sqlite3VdbeAddOp1(v, OP_SeekEnd, iDest);
      insFlags = OPFLAG_APPEND|OPFLAG_USESEEKRESULT|OPFLAG_PREFORMAT;
    }else{
      insFlags = OPFLAG_NCHANGE|OPFLAG_LASTROWID|OPFLAG_APPEND|OPFLAG_PREFORMAT;
    }
#ifdef SQLITE_ENABLE_PREUPDATE_HOOK
    if( (db->mDbFlags & DBFLAG_Vacuum)==0 ){
      sqlite3VdbeAddOp3(v, OP_RowData, iSrc, regData, 1);
      insFlags &= ~OPFLAG_PREFORMAT;
    }else

#endif
    {
      sqlite3VdbeAddOp3(v, OP_RowCell, iDest, iSrc, regRowid);
    }
    sqlite3VdbeAddOp3(v, OP_Insert, iDest, regData, regRowid);
    if( (db->mDbFlags & DBFLAG_Vacuum)==0 ){
      sqlite3VdbeChangeP4(v, -1, (char*)pDest, P4_TABLE);
    }
    sqlite3VdbeChangeP5(v, insFlags);

    sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1); VdbeCoverage(v);
    sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0);
    sqlite3VdbeAddOp2(v, OP_Close, iDest, 0);
  }else{
    sqlite3TableLock(pParse, iDbDest, pDest->tnum, 1, pDest->zName);
︙︙
2999
3000
3001
3002
3003
3004
3005
3006
3007
3008
3009
3010
3011
3012
3013
3014
3015
3016
3017
3018
3019
3020
3021
3022
3023
3024


3025

3026
3027
3028
3029
3030
3031
3032

      ** should be inserted. This is faster.
      **
      ** If any of the indexed columns use a collation sequence other than
      ** BINARY, this optimization is disabled. This is because the user 
      ** might change the definition of a collation sequence and then run
      ** a VACUUM command. In that case keys may not be written in strictly
      ** sorted order.  */
#ifdef SQLITE_ENABLE_PREUPDATE_HOOK
      if( (HasRowid(pDest) || !IsPrimaryKeyIndex(pDestIdx)) )
#endif
      {
        for(i=0; i<pSrcIdx->nColumn; i++){
          const char *zColl = pSrcIdx->azColl[i];
          if( sqlite3_stricmp(sqlite3StrBINARY, zColl) ) break;
        }
        if( i==pSrcIdx->nColumn ){
          idxInsFlags = OPFLAG_USESEEKRESULT|OPFLAG_PREFORMAT;
          sqlite3VdbeAddOp1(v, OP_SeekEnd, iDest);
          sqlite3VdbeAddOp2(v, OP_RowCell, iDest, iSrc);
        }
      }
    }else if( !HasRowid(pSrc) && pDestIdx->idxType==SQLITE_IDXTYPE_PRIMARYKEY ){
      idxInsFlags |= OPFLAG_NCHANGE;
    }
    if( idxInsFlags!=(OPFLAG_USESEEKRESULT|OPFLAG_PREFORMAT) ){
      sqlite3VdbeAddOp3(v, OP_RowData, iSrc, regData, 1);


      if( !HasRowid(pDest) && IsPrimaryKeyIndex(pDestIdx) ){

        codeWithoutRowidPreupdate(pParse, pDest, iDest, regData);
      }
    }
    sqlite3VdbeAddOp2(v, OP_IdxInsert, iDest, regData);
    sqlite3VdbeChangeP5(v, idxInsFlags|OPFLAG_APPEND);
    sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1+1); VdbeCoverage(v);
    sqlite3VdbeJumpHere(v, addr1);








<
<
<
<
|
|
|
|
|
|
|
|
<






>
>
|
>








3005
3006
3007
3008
3009
3010
3011




3012
3013
3014
3015
3016
3017
3018
3019

3020
3021
3022
3023
3024
3025
3026
3027
3028
3029
3030
3031
3032
3033
3034
3035
3036

      ** should be inserted. This is faster.
      **
      ** If any of the indexed columns use a collation sequence other than
      ** BINARY, this optimization is disabled. This is because the user 
      ** might change the definition of a collation sequence and then run
      ** a VACUUM command. In that case keys may not be written in strictly
      ** sorted order.  */




      for(i=0; i<pSrcIdx->nColumn; i++){
        const char *zColl = pSrcIdx->azColl[i];
        if( sqlite3_stricmp(sqlite3StrBINARY, zColl) ) break;
      }
      if( i==pSrcIdx->nColumn ){
        idxInsFlags = OPFLAG_USESEEKRESULT|OPFLAG_PREFORMAT;
        sqlite3VdbeAddOp1(v, OP_SeekEnd, iDest);
        sqlite3VdbeAddOp2(v, OP_RowCell, iDest, iSrc);

      }
    }else if( !HasRowid(pSrc) && pDestIdx->idxType==SQLITE_IDXTYPE_PRIMARYKEY ){
      idxInsFlags |= OPFLAG_NCHANGE;
    }
    if( idxInsFlags!=(OPFLAG_USESEEKRESULT|OPFLAG_PREFORMAT) ){
      sqlite3VdbeAddOp3(v, OP_RowData, iSrc, regData, 1);
      if( (db->mDbFlags & DBFLAG_Vacuum)==0 
       && !HasRowid(pDest) 
       && IsPrimaryKeyIndex(pDestIdx) 
      ){
        codeWithoutRowidPreupdate(pParse, pDest, iDest, regData);
      }
    }
    sqlite3VdbeAddOp2(v, OP_IdxInsert, iDest, regData);
    sqlite3VdbeChangeP5(v, idxInsFlags|OPFLAG_APPEND);
    sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1+1); VdbeCoverage(v);
    sqlite3VdbeJumpHere(v, addr1);
︙︙
Changes to test/hook.test.
︙︙
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
















1001
1002


db preupdate hook preupdate_cb
db update_hook update_cb

proc preupdate_cb {args} { lappend ::res "preupdate" $args }
proc update_cb {args} { lappend ::res "update" $args }

#set ::res [list]
#do_test 11.2 {
#  execsql VACUUM
#  set ::res
#} {}

do_test 11.3 {
  set ::res [list]
  execsql { INSERT INTO t3 SELECT a, b FROM t2 }
  set ::res
} {preupdate {INSERT main t3 0 0} preupdate {INSERT main t3 0 0}}

do_test 11.4 {
  execsql { DELETE FROM t3 }
  set ::res [list]
  execsql { INSERT INTO t3 SELECT * FROM t2 }
  set ::res
} {preupdate {INSERT main t3 0 0} preupdate {INSERT main t3 0 0}}

















finish_test









|
|
|
|
|

|





|






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



975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018


db preupdate hook preupdate_cb
db update_hook update_cb

proc preupdate_cb {args} { lappend ::res "preupdate" $args }
proc update_cb {args} { lappend ::res "update" $args }

set ::res [list]
do_test 12.2 {
  execsql VACUUM
  set ::res
} {}

do_test 12.3 {
  set ::res [list]
  execsql { INSERT INTO t3 SELECT a, b FROM t2 }
  set ::res
} {preupdate {INSERT main t3 0 0} preupdate {INSERT main t3 0 0}}

do_test 12.4 {
  execsql { DELETE FROM t3 }
  set ::res [list]
  execsql { INSERT INTO t3 SELECT * FROM t2 }
  set ::res
} {preupdate {INSERT main t3 0 0} preupdate {INSERT main t3 0 0}}

do_execsql_test 12.5 {
  CREATE TABLE t4(a COLLATE nocase PRIMARY KEY, b) WITHOUT ROWID;
  INSERT INTO t4 VALUES('abc', 1);
  INSERT INTO t4 VALUES('DEF', 2);
}

set ::res [list]
do_test 12.6 {
  execsql VACUUM
  set ::res
} {}

do_catchsql_test 12.6 {
  INSERT INTO t4 VALUES('def', 3);
} {1 {UNIQUE constraint failed: t4.a}}

finish_test