/ Check-in [2772404b]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Avoid opening a statement journal on single-row UPDATEs without triggers or FK constraints.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 2772404b8c570caf3c31d2b0530cf347a24f6f60e220e726c086537b38ebfa85
User & Date: drh 2018-04-20 19:32:35
Context
2018-04-20
19:46
Avoid the use of statement journals on DELETEs of a single row without triggers or foreign keys. check-in: 20bf5800 user: drh tags: trunk
19:32
Avoid opening a statement journal on single-row UPDATEs without triggers or FK constraints. check-in: 2772404b user: drh tags: trunk
18:01
Fix a VDBE comment on upsert. Provide an error message when upsert detects index corruption. check-in: 279c48f6 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/update.c.

335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
...
452
453
454
455
456
457
458


459
460
461
462
463
464

465
466
467
468
469
470
471
...
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895

896
897
898
899
900
901
902
    memset(aToOpen, 1, nIdx+1);
  }

  /* Begin generating code. */
  v = sqlite3GetVdbe(pParse);
  if( v==0 ) goto update_cleanup;
  if( pParse->nested==0 ) sqlite3VdbeCountChanges(v);
  sqlite3BeginWriteOperation(pParse, 1, iDb);

  /* Allocate required registers. */
  if( !IsVirtual(pTab) ){
    regRowSet = ++pParse->nMem;
    regOldRowid = regNewRowid = ++pParse->nMem;
    if( chngPk || pTrigger || hasFK ){
      regOld = pParse->nMem + 1;
................................................................................
    **
    **   UPDATE t1 SET b=b+1 WHERE b>?
    **
    ** Fall back to ONEPASS_OFF if where.c has selected a ONEPASS_MULTI
    ** strategy that uses an index for which one or more columns are being
    ** updated.  */
    eOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass);


    if( eOnePass==ONEPASS_MULTI ){
      int iCur = aiCurOnePass[1];
      if( iCur>=0 && iCur!=iDataCur && aToOpen[iCur-iBaseCur] ){
        eOnePass = ONEPASS_OFF;
      }
      assert( iCur!=iDataCur || !HasRowid(pTab) );

    }
  }

  if( HasRowid(pTab) ){
    /* Read the rowid of the current row of the WHERE scan. In ONEPASS_OFF
    ** mode, write the rowid into the FIFO. In either of the one-pass modes,
    ** leave it in register regOldRowid.  */
................................................................................
    sqlite3VdbeAddOp2(v, OP_SCopy, regArg+2+iPk, regArg+1);
  }

  bOnePass = sqlite3WhereOkOnePass(pWInfo, aDummy);

  if( bOnePass ){
    /* If using the onepass strategy, no-op out the OP_OpenEphemeral coded
    ** above. Also, if this is a top-level parse (not a trigger), clear the
    ** multi-write flag so that the VM does not open a statement journal */
    sqlite3VdbeChangeToNoop(v, addr);
    if( sqlite3IsToplevel(pParse) ){
      pParse->isMultiWrite = 0;
    }
  }else{
    /* Create a record from the argument register contents and insert it into
    ** the ephemeral table. */

    sqlite3VdbeAddOp3(v, OP_MakeRecord, regArg, nArg, regRec);
#ifdef SQLITE_DEBUG
    /* Signal an assert() within OP_MakeRecord that it is allowed to
    ** accept no-change records with serial_type 10 */
    sqlite3VdbeChangeP5(v, OPFLAG_NOCHNG_MAGIC);
#endif
    sqlite3VdbeAddOp2(v, OP_NewRowid, ephemTab, regRowid);







|







 







>
>
|
|
|
|
|
|
>







 







|
<

<
<
<



>







335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
...
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
...
883
884
885
886
887
888
889
890

891



892
893
894
895
896
897
898
899
900
901
902
    memset(aToOpen, 1, nIdx+1);
  }

  /* Begin generating code. */
  v = sqlite3GetVdbe(pParse);
  if( v==0 ) goto update_cleanup;
  if( pParse->nested==0 ) sqlite3VdbeCountChanges(v);
  sqlite3BeginWriteOperation(pParse, pTrigger || hasFK, iDb);

  /* Allocate required registers. */
  if( !IsVirtual(pTab) ){
    regRowSet = ++pParse->nMem;
    regOldRowid = regNewRowid = ++pParse->nMem;
    if( chngPk || pTrigger || hasFK ){
      regOld = pParse->nMem + 1;
................................................................................
    **
    **   UPDATE t1 SET b=b+1 WHERE b>?
    **
    ** Fall back to ONEPASS_OFF if where.c has selected a ONEPASS_MULTI
    ** strategy that uses an index for which one or more columns are being
    ** updated.  */
    eOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass);
    if( eOnePass!=ONEPASS_SINGLE ){
      sqlite3MultiWrite(pParse);
      if( eOnePass==ONEPASS_MULTI ){
        int iCur = aiCurOnePass[1];
        if( iCur>=0 && iCur!=iDataCur && aToOpen[iCur-iBaseCur] ){
          eOnePass = ONEPASS_OFF;
        }
        assert( iCur!=iDataCur || !HasRowid(pTab) );
      }
    }
  }

  if( HasRowid(pTab) ){
    /* Read the rowid of the current row of the WHERE scan. In ONEPASS_OFF
    ** mode, write the rowid into the FIFO. In either of the one-pass modes,
    ** leave it in register regOldRowid.  */
................................................................................
    sqlite3VdbeAddOp2(v, OP_SCopy, regArg+2+iPk, regArg+1);
  }

  bOnePass = sqlite3WhereOkOnePass(pWInfo, aDummy);

  if( bOnePass ){
    /* If using the onepass strategy, no-op out the OP_OpenEphemeral coded
    ** above. */

    sqlite3VdbeChangeToNoop(v, addr);



  }else{
    /* Create a record from the argument register contents and insert it into
    ** the ephemeral table. */
    sqlite3MultiWrite(pParse);
    sqlite3VdbeAddOp3(v, OP_MakeRecord, regArg, nArg, regRec);
#ifdef SQLITE_DEBUG
    /* Signal an assert() within OP_MakeRecord that it is allowed to
    ** accept no-change records with serial_type 10 */
    sqlite3VdbeChangeP5(v, OPFLAG_NOCHNG_MAGIC);
#endif
    sqlite3VdbeAddOp2(v, OP_NewRowid, ephemTab, regRowid);