/ Check-in [35a20a5f]
Login

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

Overview
Comment:Experimental opimizations to speed up FK constraint CASCADE and SET NULL action processing. Requires further testing.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | experimental-fk-actions
Files: files | file ages | folders
SHA1: 35a20a5f22245c70faa51965951e8cc011defa93
User & Date: dan 2014-12-16 20:13:30
Original Comment: Experimental opimizations to speed up FK constraint CASCADE and SET NULL action processing.
Context
2014-12-17
14:38
Fix some comments in fkey.c. Add tests to fkey8.test. Closed-Leaf check-in: 210cb2a6 user: dan tags: experimental-fk-actions
2014-12-16
20:13
Experimental opimizations to speed up FK constraint CASCADE and SET NULL action processing. Requires further testing. check-in: 35a20a5f user: dan tags: experimental-fk-actions
00:20
Enhanced "stress2" testing in the threadtest3.c test program. check-in: ae43539e user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/fkey.c.

   433    433       ** incrementing a counter. This is necessary as the VM code is being
   434    434       ** generated for will not open a statement transaction.  */
   435    435       assert( nIncr==1 );
   436    436       sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_FOREIGNKEY,
   437    437           OE_Abort, 0, P4_STATIC, P5_ConstraintFK);
   438    438     }else{
   439    439       if( nIncr>0 && pFKey->isDeferred==0 ){
   440         -      sqlite3ParseToplevel(pParse)->mayAbort = 1;
          440  +      sqlite3MayAbort(pParse);
   441    441       }
   442    442       sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr);
   443    443     }
   444    444   
   445    445     sqlite3VdbeResolveLabel(v, iOk);
   446    446     sqlite3VdbeAddOp1(v, OP_Close, iCur);
   447    447   }
................................................................................
   504    504   
   505    505   /*
   506    506   ** This function is called to generate code executed when a row is deleted
   507    507   ** from the parent table of foreign key constraint pFKey and, if pFKey is 
   508    508   ** deferred, when a row is inserted into the same table. When generating
   509    509   ** code for an SQL UPDATE operation, this function may be called twice -
   510    510   ** once to "delete" the old row and once to "insert" the new row.
          511  +**
          512  +** Parameter nIncr is passed -1 when inserting a row (as this may decrease
          513  +** the number of FK violations in the db) or +1 when deleting one (as this
          514  +** may increase the number of FK constraint problems).
   511    515   **
   512    516   ** The code generated by this function scans through the rows in the child
   513    517   ** table that correspond to the parent table row being deleted or inserted.
   514    518   ** For each child row found, one of the following actions is taken:
   515    519   **
   516    520   **   Operation | FK type   | Action taken
   517    521   **   --------------------------------------------------------------------------
................................................................................
   625    629     sqlite3ResolveExprNames(&sNameContext, pWhere);
   626    630   
   627    631     /* Create VDBE to loop through the entries in pSrc that match the WHERE
   628    632     ** clause. If the constraint is not deferred, throw an exception for
   629    633     ** each row found. Otherwise, for deferred constraints, increment the
   630    634     ** deferred constraint counter by nIncr for each row selected.  */
   631    635     pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0, 0, 0);
   632         -  if( nIncr>0 && pFKey->isDeferred==0 ){
   633         -    sqlite3ParseToplevel(pParse)->mayAbort = 1;
   634         -  }
   635    636     sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr);
   636    637     if( pWInfo ){
   637    638       sqlite3WhereEnd(pWInfo);
   638    639     }
   639    640   
   640    641     /* Clean up the WHERE clause constructed above. */
   641    642     sqlite3ExprDelete(db, pWhere);
................................................................................
   805    806             return 1;
   806    807           }
   807    808         }
   808    809       }
   809    810     }
   810    811     return 0;
   811    812   }
          813  +
          814  +/*
          815  +** Return true if the parser passed as the first argument is being
          816  +** used to code a trigger that is really a "SET NULL" action belonging
          817  +** to trigger pFKey.
          818  +*/
          819  +static int isSetNullAction(Parse *pParse, FKey *pFKey){
          820  +  Parse *pTop = sqlite3ParseToplevel(pParse);
          821  +  if( pTop->pTriggerPrg ){
          822  +    Trigger *p = pTop->pTriggerPrg->pTrigger;
          823  +    if( (p==pFKey->apTrigger[0] && pFKey->aAction[0]==OE_SetNull)
          824  +     || (p==pFKey->apTrigger[1] && pFKey->aAction[1]==OE_SetNull)
          825  +    ){
          826  +      return 1;
          827  +    }
          828  +  }
          829  +  return 0;
          830  +}
   812    831   
   813    832   /*
   814    833   ** This function is called when inserting, deleting or updating a row of
   815    834   ** table pTab to generate VDBE code to perform foreign key constraint 
   816    835   ** processing for the operation.
   817    836   **
   818    837   ** For a DELETE operation, parameter regOld is passed the index of the
................................................................................
   858    877     for(pFKey=pTab->pFKey; pFKey; pFKey=pFKey->pNextFrom){
   859    878       Table *pTo;                   /* Parent table of foreign key pFKey */
   860    879       Index *pIdx = 0;              /* Index on key columns in pTo */
   861    880       int *aiFree = 0;
   862    881       int *aiCol;
   863    882       int iCol;
   864    883       int i;
   865         -    int isIgnore = 0;
          884  +    int bIgnore = 0;
   866    885   
   867    886       if( aChange 
   868    887        && sqlite3_stricmp(pTab->zName, pFKey->zTo)!=0
   869    888        && fkChildIsModified(pTab, pFKey, aChange, bChngRowid)==0 
   870    889       ){
   871    890         continue;
   872    891       }
................................................................................
   917    936         /* Request permission to read the parent key columns. If the 
   918    937         ** authorization callback returns SQLITE_IGNORE, behave as if any
   919    938         ** values read from the parent table are NULL. */
   920    939         if( db->xAuth ){
   921    940           int rcauth;
   922    941           char *zCol = pTo->aCol[pIdx ? pIdx->aiColumn[i] : pTo->iPKey].zName;
   923    942           rcauth = sqlite3AuthReadCol(pParse, pTo->zName, zCol, iDb);
   924         -        isIgnore = (rcauth==SQLITE_IGNORE);
          943  +        bIgnore = (rcauth==SQLITE_IGNORE);
   925    944         }
   926    945   #endif
   927    946       }
   928    947   
   929    948       /* Take a shared-cache advisory read-lock on the parent table. Allocate 
   930    949       ** a cursor to use to search the unique index on the parent key columns 
   931    950       ** in the parent table.  */
................................................................................
   932    951       sqlite3TableLock(pParse, iDb, pTo->tnum, 0, pTo->zName);
   933    952       pParse->nTab++;
   934    953   
   935    954       if( regOld!=0 ){
   936    955         /* A row is being removed from the child table. Search for the parent.
   937    956         ** If the parent does not exist, removing the child row resolves an 
   938    957         ** outstanding foreign key constraint violation. */
   939         -      fkLookupParent(pParse, iDb, pTo, pIdx, pFKey, aiCol, regOld, -1,isIgnore);
          958  +      fkLookupParent(pParse, iDb, pTo, pIdx, pFKey, aiCol, regOld, -1, bIgnore);
   940    959       }
   941         -    if( regNew!=0 ){
          960  +    if( regNew!=0 && !isSetNullAction(pParse, pFKey) ){
   942    961         /* A row is being added to the child table. If a parent row cannot
   943         -      ** be found, adding the child row has violated the FK constraint. */ 
   944         -      fkLookupParent(pParse, iDb, pTo, pIdx, pFKey, aiCol, regNew, +1,isIgnore);
          962  +      ** be found, adding the child row has violated the FK constraint. 
          963  +      **
          964  +      ** If this operation is being performed as part of a trigger program
          965  +      ** that is actually a "SET NULL" action belonging to this very 
          966  +      ** foreign key, then omit this scan altogether. As the child keys
          967  +      ** values are guaranteed to be NULL, it is not possible for adding
          968  +      ** this row to cause an FK violation. */
          969  +      fkLookupParent(pParse, iDb, pTo, pIdx, pFKey, aiCol, regNew, +1, bIgnore);
   945    970       }
   946    971   
   947    972       sqlite3DbFree(db, aiFree);
   948    973     }
   949    974   
   950    975     /* Loop through all the foreign key constraints that refer to this table.
   951    976     ** (the "child" constraints) */
................................................................................
   958    983         continue;
   959    984       }
   960    985   
   961    986       if( !pFKey->isDeferred && !(db->flags & SQLITE_DeferFKs) 
   962    987        && !pParse->pToplevel && !pParse->isMultiWrite 
   963    988       ){
   964    989         assert( regOld==0 && regNew!=0 );
   965         -      /* Inserting a single row into a parent table cannot cause an immediate
   966         -      ** foreign key violation. So do nothing in this case.  */
          990  +      /* Inserting a single row into a parent table cannot cause (or fix)
          991  +      ** an immediate foreign key violation. So do nothing in this case.  */
   967    992         continue;
   968    993       }
   969    994   
   970    995       if( sqlite3FkLocateIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ){
   971    996         if( !isIgnoreErrors || db->mallocFailed ) return;
   972    997         continue;
   973    998       }
................................................................................
   983   1008         pItem->pTab->nRef++;
   984   1009         pItem->iCursor = pParse->nTab++;
   985   1010     
   986   1011         if( regNew!=0 ){
   987   1012           fkScanChildren(pParse, pSrc, pTab, pIdx, pFKey, aiCol, regNew, -1);
   988   1013         }
   989   1014         if( regOld!=0 ){
   990         -        /* If there is a RESTRICT action configured for the current operation
   991         -        ** on the parent table of this FK, then throw an exception 
   992         -        ** immediately if the FK constraint is violated, even if this is a
   993         -        ** deferred trigger. That's what RESTRICT means. To defer checking
   994         -        ** the constraint, the FK should specify NO ACTION (represented
   995         -        ** using OE_None). NO ACTION is the default.  */
         1015  +        int eAction = pFKey->aAction[aChange!=0];
   996   1016           fkScanChildren(pParse, pSrc, pTab, pIdx, pFKey, aiCol, regOld, 1);
         1017  +        /* If this is a deferred FK constraint, or a CASCADE or SET NULL
         1018  +        ** action applies, do not set the may-abort flag on this statement.
         1019  +        ** The flag may be set on this statement for some other reason, but
         1020  +        ** not as a result of this FK constraint. */
         1021  +        if( !pFKey->isDeferred && eAction!=OE_Cascade && eAction!=OE_SetNull ){
         1022  +          sqlite3MayAbort(pParse);
         1023  +        }
   997   1024         }
   998   1025         pItem->zName = 0;
   999   1026         sqlite3SrcListDelete(db, pSrc);
  1000   1027       }
  1001   1028       sqlite3DbFree(db, aiCol);
  1002   1029     }
  1003   1030   }

Changes to src/vdbeaux.c.

   392    392   ** match, or false otherwise. This function is intended to be used as
   393    393   ** part of an assert statement in the compiler. Similar to:
   394    394   **
   395    395   **   assert( sqlite3VdbeAssertMayAbort(pParse->pVdbe, pParse->mayAbort) );
   396    396   */
   397    397   int sqlite3VdbeAssertMayAbort(Vdbe *v, int mayAbort){
   398    398     int hasAbort = 0;
          399  +  int hasFkCounter = 0;
   399    400     Op *pOp;
   400    401     VdbeOpIter sIter;
   401    402     memset(&sIter, 0, sizeof(sIter));
   402    403     sIter.v = v;
   403    404   
   404    405     while( (pOp = opIterNext(&sIter))!=0 ){
   405    406       int opcode = pOp->opcode;
   406    407       if( opcode==OP_Destroy || opcode==OP_VUpdate || opcode==OP_VRename 
   407         -#ifndef SQLITE_OMIT_FOREIGN_KEY
   408         -     || (opcode==OP_FkCounter && pOp->p1==0 && pOp->p2==1) 
   409         -#endif
   410    408        || ((opcode==OP_Halt || opcode==OP_HaltIfNull) 
   411    409         && ((pOp->p1&0xff)==SQLITE_CONSTRAINT && pOp->p2==OE_Abort))
   412    410       ){
   413    411         hasAbort = 1;
   414    412         break;
   415    413       }
          414  +#ifndef SQLITE_OMIT_FOREIGN_KEY
          415  +    if( opcode==OP_FkCounter && pOp->p1==0 && pOp->p2==1 ){
          416  +      hasFkCounter = 1;
          417  +    }
          418  +#endif
   416    419     }
   417    420     sqlite3DbFree(v->db, sIter.apSub);
   418    421   
   419    422     /* Return true if hasAbort==mayAbort. Or if a malloc failure occurred.
   420    423     ** If malloc failed, then the while() loop above may not have iterated
   421    424     ** through all opcodes and hasAbort may be set incorrectly. Return
   422    425     ** true for this case to prevent the assert() in the callers frame
   423    426     ** from failing.  */
   424         -  return ( v->db->mallocFailed || hasAbort==mayAbort );
          427  +  return ( v->db->mallocFailed || hasAbort==mayAbort || hasFkCounter );
   425    428   }
   426    429   #endif /* SQLITE_DEBUG - the sqlite3AssertMayAbort() function */
   427    430   
   428    431   /*
   429    432   ** Loop through the program looking for P2 values that are negative
   430    433   ** on jump instructions.  Each such value is a label.  Resolve the
   431    434   ** label by setting the P2 value to its correct non-zero value.

Added test/fkey8.test.

            1  +# 2001 September 15
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.
           12  +#
           13  +# This file implements tests for foreign keys.
           14  +#
           15  +
           16  +set testdir [file dirname $argv0]
           17  +source $testdir/tester.tcl
           18  +set testprefix fkey8
           19  +
           20  +ifcapable {!foreignkey} {
           21  +  finish_test
           22  +  return
           23  +}
           24  +do_execsql_test 1.0 { PRAGMA foreign_keys = 1; }
           25  +
           26  +
           27  +foreach {tn use_stmt sql schema} {
           28  +  1   1 "DELETE FROM p1" {
           29  +    CREATE TABLE p1(a PRIMARY KEY);
           30  +    CREATE TABLE c1(b REFERENCES p1);
           31  +  }
           32  +
           33  +  2.1     0 "DELETE FROM p1" {
           34  +    CREATE TABLE p1(a PRIMARY KEY);
           35  +    CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE);
           36  +  }
           37  +  2.2   0 "DELETE FROM p1" {
           38  +    CREATE TABLE p1(a PRIMARY KEY);
           39  +    CREATE TABLE c1(b REFERENCES p1 ON DELETE SET NULL);
           40  +  }
           41  +  2.3   1 "DELETE FROM p1" {
           42  +    CREATE TABLE p1(a PRIMARY KEY);
           43  +    CREATE TABLE c1(b REFERENCES p1 ON DELETE SET DEFAULT);
           44  +  }
           45  +
           46  +  3   1 "DELETE FROM p1" {
           47  +    CREATE TABLE p1(a PRIMARY KEY);
           48  +    CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE);
           49  +    CREATE TRIGGER ct1 AFTER DELETE ON c1 BEGIN
           50  +      INSERT INTO p1 VALUES('x');
           51  +    END;
           52  +  }
           53  +
           54  +  4   1 "DELETE FROM p1" {
           55  +    CREATE TABLE p1(a PRIMARY KEY);
           56  +    CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE, c PRIMARY KEY);
           57  +    CREATE TABLE cc1(d REFERENCES c1);
           58  +  }
           59  +
           60  +  5.1   0 "DELETE FROM p1" {
           61  +    CREATE TABLE p1(a PRIMARY KEY);
           62  +    CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE, c PRIMARY KEY);
           63  +    CREATE TABLE cc1(d REFERENCES c1 ON DELETE CASCADE);
           64  +  }
           65  +  5.2   0 "DELETE FROM p1" {
           66  +    CREATE TABLE p1(a PRIMARY KEY);
           67  +    CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE, c PRIMARY KEY);
           68  +    CREATE TABLE cc1(d REFERENCES c1 ON DELETE SET NULL);
           69  +  }
           70  +
           71  +} {
           72  +  drop_all_tables
           73  +  do_test 1.$tn {
           74  +    execsql $schema
           75  +    set stmt [sqlite3_prepare_v2 db $sql -1 dummy]
           76  +    set ret [uses_stmt_journal $stmt]
           77  +    sqlite3_finalize $stmt
           78  +    set ret
           79  +  } $use_stmt
           80  +}
           81  +
           82  +
           83  +
           84  +
           85  +finish_test