/ Check-in [dff0314b7e]
Login

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

Overview
Comment:Merge fixes and enhancements from trunk.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | alter-table-rename-column
Files: files | file ages | folders
SHA3-256: dff0314b7e7ef2d04375ff21fbf89039bf5f64a9cdca53d7bc7120faacd92c06
User & Date: drh 2018-08-14 18:12:33
Context
2018-08-14
19:27
Improved error messages when an ALTER TABLE RENAME COLUMN fails due to a duplicate column name. check-in: 37d11b8e82 user: drh tags: alter-table-rename-column
18:12
Merge fixes and enhancements from trunk. check-in: dff0314b7e user: drh tags: alter-table-rename-column
16:18
Fix ALTER TABLE RENAME COLUMN in cases where the column being renamed is an IPK declared with a separate PRIMARY KEY clause - "CREATE TABLE x(y INTEGER, PRIMARY KEY(y))". check-in: 32ca8418df user: dan tags: alter-table-rename-column
15:12
Fix UPSERT so that it checks the target-constraint first and fires the DO UPDATE if that constraint is violated regardless of whether or not other constraints are in violation. This aligns SQLite behavior with what PostgreSQL does. Fix for ticket [908f001483982c43cdb476dfb590a1a]. check-in: 529fb55e3d user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/insert.c.

  1174   1174     testcase( w.eCode==0 );
  1175   1175     testcase( w.eCode==CKCNSTRNT_COLUMN );
  1176   1176     testcase( w.eCode==CKCNSTRNT_ROWID );
  1177   1177     testcase( w.eCode==(CKCNSTRNT_ROWID|CKCNSTRNT_COLUMN) );
  1178   1178     return !w.eCode;
  1179   1179   }
  1180   1180   
  1181         -/*
  1182         -** An instance of the ConstraintAddr object remembers the byte-code addresses
  1183         -** for sections of the constraint checks that deal with uniqueness constraints
  1184         -** on the rowid and on the upsert constraint.
  1185         -**
  1186         -** This information is passed into checkReorderConstraintChecks() to insert
  1187         -** some OP_Goto operations so that the rowid and upsert constraints occur
  1188         -** in the correct order relative to other constraints.
  1189         -*/
  1190         -typedef struct ConstraintAddr ConstraintAddr;
  1191         -struct ConstraintAddr {
  1192         -  int ipkTop;          /* Subroutine for rowid constraint check */
  1193         -  int upsertTop;       /* Label for upsert constraint check subroutine */
  1194         -  int upsertTop2;      /* Copy of upsertTop not cleared by the call */
  1195         -  int upsertBtm;       /* upsert constraint returns to this label */
  1196         -  int ipkBtm;          /* Return opcode rowid constraint check */
  1197         -};
  1198         -
  1199         -/*
  1200         -** Generate any OP_Goto operations needed to cause constraints to be
  1201         -** run that haven't already been run.
  1202         -*/
  1203         -static void reorderConstraintChecks(Vdbe *v, ConstraintAddr *p){
  1204         -  if( p->upsertTop ){
  1205         -    testcase( sqlite3VdbeLabelHasBeenResolved(v, p->upsertTop) );
  1206         -    sqlite3VdbeGoto(v, p->upsertTop);
  1207         -    VdbeComment((v, "call upsert subroutine"));
  1208         -    sqlite3VdbeResolveLabel(v, p->upsertBtm);
  1209         -    p->upsertTop = 0;
  1210         -  }
  1211         -  if( p->ipkTop ){
  1212         -    sqlite3VdbeGoto(v, p->ipkTop);
  1213         -    VdbeComment((v, "call rowid unique-check subroutine"));
  1214         -    sqlite3VdbeJumpHere(v, p->ipkBtm);
  1215         -    p->ipkTop = 0;
  1216         -  }
  1217         -}
  1218         -
  1219   1181   /*
  1220   1182   ** Generate code to do constraint checks prior to an INSERT or an UPDATE
  1221   1183   ** on table pTab.
  1222   1184   **
  1223   1185   ** The regNewData parameter is the first register in a range that contains
  1224   1186   ** the data to be inserted or the data after the update.  There will be
  1225   1187   ** pTab->nCol+1 registers in this range.  The first register (the one
................................................................................
  1321   1283     int i;               /* loop counter */
  1322   1284     int ix;              /* Index loop counter */
  1323   1285     int nCol;            /* Number of columns */
  1324   1286     int onError;         /* Conflict resolution strategy */
  1325   1287     int addr1;           /* Address of jump instruction */
  1326   1288     int seenReplace = 0; /* True if REPLACE is used to resolve INT PK conflict */
  1327   1289     int nPkField;        /* Number of fields in PRIMARY KEY. 1 for ROWID tables */
  1328         -  ConstraintAddr sAddr;/* Address information for constraint reordering */
  1329   1290     Index *pUpIdx = 0;   /* Index to which to apply the upsert */
  1330   1291     u8 isUpdate;         /* True if this is an UPDATE operation */
  1331   1292     u8 bAffinityDone = 0;  /* True if the OP_Affinity operation has been run */
  1332   1293     int upsertBypass = 0;  /* Address of Goto to bypass upsert subroutine */
         1294  +  int upsertJump = 0;    /* Address of Goto that jumps into upsert subroutine */
         1295  +  int ipkTop = 0;        /* Top of the IPK uniqueness check */
         1296  +  int ipkBottom = 0;     /* OP_Goto at the end of the IPK uniqueness check */
  1333   1297   
  1334   1298     isUpdate = regOldData!=0;
  1335   1299     db = pParse->db;
  1336   1300     v = sqlite3GetVdbe(pParse);
  1337   1301     assert( v!=0 );
  1338   1302     assert( pTab->pSelect==0 );  /* This table is not a VIEW */
  1339   1303     nCol = pTab->nCol;
  1340         -  memset(&sAddr, 0, sizeof(sAddr));
  1341   1304     
  1342   1305     /* pPk is the PRIMARY KEY index for WITHOUT ROWID tables and NULL for
  1343   1306     ** normal rowid tables.  nPkField is the number of key fields in the 
  1344   1307     ** pPk index or 1 for a rowid table.  In other words, nPkField is the
  1345   1308     ** number of fields in the true primary key of the table. */
  1346   1309     if( HasRowid(pTab) ){
  1347   1310       pPk = 0;
................................................................................
  1437   1400       pParse->iSelfTab = 0;
  1438   1401     }
  1439   1402   #endif /* !defined(SQLITE_OMIT_CHECK) */
  1440   1403   
  1441   1404     /* UNIQUE and PRIMARY KEY constraints should be handled in the following
  1442   1405     ** order:
  1443   1406     **
  1444         -  **   (1)  OE_Abort, OE_Fail, OE_Rollback, OE_Ignore
  1445         -  **   (2)  OE_Update
         1407  +  **   (1)  OE_Update
         1408  +  **   (2)  OE_Abort, OE_Fail, OE_Rollback, OE_Ignore
  1446   1409     **   (3)  OE_Replace
  1447   1410     **
  1448   1411     ** OE_Fail and OE_Ignore must happen before any changes are made.
  1449   1412     ** OE_Update guarantees that only a single row will change, so it
  1450   1413     ** must happen before OE_Replace.  Technically, OE_Abort and OE_Rollback
  1451   1414     ** could happen in any order, but they are grouped up front for
  1452   1415     ** convenience.
         1416  +  **
         1417  +  ** 2018-08-14: Ticket https://www.sqlite.org/src/info/908f001483982c43
         1418  +  ** The order of constraints used to have OE_Update as (2) and OE_Abort
         1419  +  ** and so forth as (1). But apparently PostgreSQL checks the OE_Update
         1420  +  ** constraint before any others, so it had to be moved.
  1453   1421     **
  1454   1422     ** Constraint checking code is generated in this order:
  1455   1423     **   (A)  The rowid constraint
  1456   1424     **   (B)  Unique index constraints that do not have OE_Replace as their
  1457   1425     **        default conflict resolution strategy
  1458   1426     **   (C)  Unique index that do use OE_Replace by default.
  1459   1427     **
................................................................................
  1466   1434       if( pUpsert->pUpsertTarget==0 ){
  1467   1435         /* An ON CONFLICT DO NOTHING clause, without a constraint-target.
  1468   1436         ** Make all unique constraint resolution be OE_Ignore */
  1469   1437         assert( pUpsert->pUpsertSet==0 );
  1470   1438         overrideError = OE_Ignore;
  1471   1439         pUpsert = 0;
  1472   1440       }else if( (pUpIdx = pUpsert->pUpsertIdx)!=0 ){
  1473         -      /* If the constraint-target is on some column other than
  1474         -      ** then ROWID, then we might need to move the UPSERT around
  1475         -      ** so that it occurs in the correct order. */
  1476         -      sAddr.upsertTop = sAddr.upsertTop2 = sqlite3VdbeMakeLabel(v);
  1477         -      sAddr.upsertBtm = sqlite3VdbeMakeLabel(v);
         1441  +      /* If the constraint-target uniqueness check must be run first.
         1442  +      ** Jump to that uniqueness check now */
         1443  +      upsertJump = sqlite3VdbeAddOp0(v, OP_Goto);
         1444  +      VdbeComment((v, "UPSERT constraint goes first"));
  1478   1445       }
  1479   1446     }
  1480   1447   
  1481   1448     /* If rowid is changing, make sure the new rowid does not previously
  1482   1449     ** exist in the table.
  1483   1450     */
  1484   1451     if( pkChng && pPk==0 ){
................................................................................
  1502   1469       }
  1503   1470   
  1504   1471       /* If the response to a rowid conflict is REPLACE but the response
  1505   1472       ** to some other UNIQUE constraint is FAIL or IGNORE, then we need
  1506   1473       ** to defer the running of the rowid conflict checking until after
  1507   1474       ** the UNIQUE constraints have run.
  1508   1475       */
  1509         -    assert( OE_Update>OE_Replace );
  1510         -    assert( OE_Ignore<OE_Replace );
  1511         -    assert( OE_Fail<OE_Replace );
  1512         -    assert( OE_Abort<OE_Replace );
  1513         -    assert( OE_Rollback<OE_Replace );
  1514         -    if( onError>=OE_Replace
  1515         -     && (pUpsert || onError!=overrideError)
  1516         -     && pTab->pIndex
         1476  +    if( onError==OE_Replace      /* IPK rule is REPLACE */
         1477  +     && onError!=overrideError   /* Rules for other contraints are different */
         1478  +     && pTab->pIndex             /* There exist other constraints */
  1517   1479       ){
  1518         -      sAddr.ipkTop = sqlite3VdbeAddOp0(v, OP_Goto)+1;
         1480  +      ipkTop = sqlite3VdbeAddOp0(v, OP_Goto)+1;
         1481  +      VdbeComment((v, "defer IPK REPLACE until last"));
  1519   1482       }
  1520   1483   
  1521   1484       if( isUpdate ){
  1522   1485         /* pkChng!=0 does not mean that the rowid has changed, only that
  1523   1486         ** it might have changed.  Skip the conflict logic below if the rowid
  1524   1487         ** is unchanged. */
  1525   1488         sqlite3VdbeAddOp3(v, OP_Eq, regNewData, addrRowidOk, regOldData);
................................................................................
  1606   1569         case OE_Ignore: {
  1607   1570           testcase( onError==OE_Ignore );
  1608   1571           sqlite3VdbeGoto(v, ignoreDest);
  1609   1572           break;
  1610   1573         }
  1611   1574       }
  1612   1575       sqlite3VdbeResolveLabel(v, addrRowidOk);
  1613         -    if( sAddr.ipkTop ){
  1614         -      sAddr.ipkBtm = sqlite3VdbeAddOp0(v, OP_Goto);
  1615         -      sqlite3VdbeJumpHere(v, sAddr.ipkTop-1);
         1576  +    if( ipkTop ){
         1577  +      ipkBottom = sqlite3VdbeAddOp0(v, OP_Goto);
         1578  +      sqlite3VdbeJumpHere(v, ipkTop-1);
  1616   1579       }
  1617   1580     }
  1618   1581   
  1619   1582     /* Test all UNIQUE constraints by creating entries for each UNIQUE
  1620   1583     ** index and making sure that duplicate entries do not already exist.
  1621   1584     ** Compute the revised record entries for indices as we go.
  1622   1585     **
................................................................................
  1626   1589     for(ix=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, ix++){
  1627   1590       int regIdx;          /* Range of registers hold conent for pIdx */
  1628   1591       int regR;            /* Range of registers holding conflicting PK */
  1629   1592       int iThisCur;        /* Cursor for this UNIQUE index */
  1630   1593       int addrUniqueOk;    /* Jump here if the UNIQUE constraint is satisfied */
  1631   1594   
  1632   1595       if( aRegIdx[ix]==0 ) continue;  /* Skip indices that do not change */
  1633         -    if( bAffinityDone==0 ){
  1634         -      sqlite3TableAffinity(v, pTab, regNewData+1);
  1635         -      bAffinityDone = 1;
  1636         -    }
  1637   1596       if( pUpIdx==pIdx ){
  1638         -      addrUniqueOk = sAddr.upsertBtm;
         1597  +      addrUniqueOk = upsertJump+1;
  1639   1598         upsertBypass = sqlite3VdbeGoto(v, 0);
  1640   1599         VdbeComment((v, "Skip upsert subroutine"));
  1641         -      sqlite3VdbeResolveLabel(v, sAddr.upsertTop2);
         1600  +      sqlite3VdbeJumpHere(v, upsertJump);
  1642   1601       }else{
  1643   1602         addrUniqueOk = sqlite3VdbeMakeLabel(v);
         1603  +    }
         1604  +    if( bAffinityDone==0 && (pUpIdx==0 || pUpIdx==pIdx) ){
         1605  +      sqlite3TableAffinity(v, pTab, regNewData+1);
         1606  +      bAffinityDone = 1;
  1644   1607       }
  1645   1608       VdbeNoopComment((v, "uniqueness check for %s", pIdx->zName));
  1646   1609       iThisCur = iIdxCur+ix;
  1647   1610   
  1648   1611   
  1649   1612       /* Skip partial indices for which the WHERE clause is not true */
  1650   1613       if( pIdx->pPartIdxWhere ){
................................................................................
  1709   1672         if( pUpsert->pUpsertSet==0 ){
  1710   1673           onError = OE_Ignore;  /* DO NOTHING is the same as INSERT OR IGNORE */
  1711   1674         }else{
  1712   1675           onError = OE_Update;  /* DO UPDATE */
  1713   1676         }
  1714   1677       }
  1715   1678   
  1716         -    /* Invoke subroutines to handle IPK replace and upsert prior to running
  1717         -    ** the first REPLACE constraint check. */
  1718         -    if( onError==OE_Replace ){
  1719         -      testcase( sAddr.ipkTop );
  1720         -      testcase( sAddr.upsertTop
  1721         -             && sqlite3VdbeLabelHasBeenResolved(v,sAddr.upsertTop) );
  1722         -      reorderConstraintChecks(v, &sAddr);
  1723         -    }
  1724         -
  1725   1679       /* Collision detection may be omitted if all of the following are true:
  1726   1680       **   (1) The conflict resolution algorithm is REPLACE
  1727   1681       **   (2) The table is a WITHOUT ROWID table
  1728   1682       **   (3) There are no secondary indexes on the table
  1729   1683       **   (4) No delete triggers need to be fired if there is a conflict
  1730   1684       **   (5) No FK constraint counters need to be updated if a conflict occurs.
  1731   1685       */ 
................................................................................
  1839   1793               regR, nPkField, 0, OE_Replace,
  1840   1794               (pIdx==pPk ? ONEPASS_SINGLE : ONEPASS_OFF), iThisCur);
  1841   1795           seenReplace = 1;
  1842   1796           break;
  1843   1797         }
  1844   1798       }
  1845   1799       if( pUpIdx==pIdx ){
         1800  +      sqlite3VdbeGoto(v, upsertJump+1);
  1846   1801         sqlite3VdbeJumpHere(v, upsertBypass);
  1847   1802       }else{
  1848   1803         sqlite3VdbeResolveLabel(v, addrUniqueOk);
  1849   1804       }
  1850   1805       if( regR!=regIdx ) sqlite3ReleaseTempRange(pParse, regR, nPkField);
         1806  +  }
  1851   1807   
         1808  +  /* If the IPK constraint is a REPLACE, run it last */
         1809  +  if( ipkTop ){
         1810  +    sqlite3VdbeGoto(v, ipkTop+1);
         1811  +    VdbeComment((v, "Do IPK REPLACE"));
         1812  +    sqlite3VdbeJumpHere(v, ipkBottom);
  1852   1813     }
  1853         -  testcase( sAddr.ipkTop!=0 );
  1854         -  testcase( sAddr.upsertTop
  1855         -         && sqlite3VdbeLabelHasBeenResolved(v,sAddr.upsertTop) );
  1856         -  reorderConstraintChecks(v, &sAddr);
  1857         -  
         1814  +
  1858   1815     *pbMayReplace = seenReplace;
  1859   1816     VdbeModuleComment((v, "END: GenCnstCks(%d)", seenReplace));
  1860   1817   }
  1861   1818   
  1862   1819   #ifdef SQLITE_ENABLE_NULL_TRIM
  1863   1820   /*
  1864   1821   ** Change the P5 operand on the last opcode (which should be an OP_MakeRecord)

Changes to src/os_unix.c.

   698    698   ** Function unixMutexHeld() is used to assert() that the global mutex 
   699    699   ** is held when required. This function is only used as part of assert() 
   700    700   ** statements. e.g.
   701    701   **
   702    702   **   unixEnterMutex()
   703    703   **     assert( unixMutexHeld() );
   704    704   **   unixEnterLeave()
          705  +**
          706  +** To prevent deadlock, the global unixBigLock must must be acquired
          707  +** before the unixInodeInfo.pLockMutex mutex, if both are held.  It is
          708  +** OK to get the pLockMutex without holding unixBigLock first, but if
          709  +** that happens, the unixBigLock mutex must not be acquired until after
          710  +** pLockMutex is released.
          711  +**
          712  +**      OK:     enter(unixBigLock),  enter(pLockInfo)
          713  +**      OK:     enter(unixBigLock)
          714  +**      OK:     enter(pLockInfo)
          715  +**   ERROR:     enter(pLockInfo), enter(unixBigLock)
   705    716   */
   706    717   static sqlite3_mutex *unixBigLock = 0;
   707    718   static void unixEnterMutex(void){
          719  +  assert( sqlite3_mutex_notheld(unixBigLock) );  /* Not a recursive mutex */
   708    720     sqlite3_mutex_enter(unixBigLock);
   709    721   }
   710    722   static void unixLeaveMutex(void){
          723  +  assert( sqlite3_mutex_held(unixBigLock) );
   711    724     sqlite3_mutex_leave(unixBigLock);
   712    725   }
   713    726   #ifdef SQLITE_DEBUG
   714    727   static int unixMutexHeld(void) {
   715    728     return sqlite3_mutex_held(unixBigLock);
   716    729   }
   717    730   #endif
................................................................................
  1107   1120   **
  1108   1121   ** A single inode can have multiple file descriptors, so each unixFile
  1109   1122   ** structure contains a pointer to an instance of this object and this
  1110   1123   ** object keeps a count of the number of unixFile pointing to it.
  1111   1124   **
  1112   1125   ** Mutex rules:
  1113   1126   **
  1114         -**  (1) The pLockMutex mutex must be held in order to read or write
         1127  +**  (1) Only the pLockMutex mutex must be held in order to read or write
  1115   1128   **      any of the locking fields:
  1116         -**          nShared, nLock, eFileLock, or bProcessLock
         1129  +**          nShared, nLock, eFileLock, bProcessLock, pUnused
  1117   1130   **
  1118   1131   **  (2) When nRef>0, then the following fields are unchanging and can
  1119   1132   **      be read (but not written) without holding any mutex:
  1120   1133   **          fileId, pLockMutex
  1121   1134   **
  1122   1135   **  (3) With the exceptions above, all the fields may only be read
  1123   1136   **      or written while holding the global unixBigLock mutex.
         1137  +**
         1138  +** Deadlock prevention:  The global unixBigLock mutex may not
         1139  +** be acquired while holding the pLockMutex mutex.  If both unixBigLock
         1140  +** and pLockMutex are needed, then unixBigLock must be acquired first.
  1124   1141   */
  1125   1142   struct unixInodeInfo {
  1126   1143     struct unixFileId fileId;       /* The lookup key */
  1127   1144     sqlite3_mutex *pLockMutex;      /* Hold this mutex for... */
  1128   1145     int nShared;                      /* Number of SHARED locks held */
  1129   1146     int nLock;                        /* Number of outstanding file locks */
  1130   1147     unsigned char eFileLock;          /* One of SHARED_LOCK, RESERVED_LOCK etc. */
  1131   1148     unsigned char bProcessLock;       /* An exclusive process lock is held */
         1149  +  UnixUnusedFd *pUnused;            /* Unused file descriptors to close */
  1132   1150     int nRef;                       /* Number of pointers to this structure */
  1133   1151     unixShmNode *pShmNode;          /* Shared memory associated with this inode */
  1134         -  UnixUnusedFd *pUnused;          /* Unused file descriptors to close */
  1135   1152     unixInodeInfo *pNext;           /* List of all unixInodeInfo objects */
  1136   1153     unixInodeInfo *pPrev;           /*    .... doubly linked */
  1137   1154   #if SQLITE_ENABLE_LOCKING_STYLE
  1138   1155     unsigned long long sharedByte;  /* for AFP simulated shared lock */
  1139   1156   #endif
  1140   1157   #if OS_VXWORKS
  1141   1158     sem_t *pSem;                    /* Named POSIX semaphore */
................................................................................
  1143   1160   #endif
  1144   1161   };
  1145   1162   
  1146   1163   /*
  1147   1164   ** A lists of all unixInodeInfo objects.
  1148   1165   */
  1149   1166   static unixInodeInfo *inodeList = 0;  /* All unixInodeInfo objects */
  1150         -static unsigned int nUnusedFd = 0;    /* Total unused file descriptors */
         1167  +
         1168  +#ifdef SQLITE_DEBUG
         1169  +/*
         1170  +** True if the inode mutex is held, or not.  Used only within assert()
         1171  +** to help verify correct mutex usage.
         1172  +*/
         1173  +int unixFileMutexHeld(unixFile *pFile){
         1174  +  assert( pFile->pInode );
         1175  +  return sqlite3_mutex_held(pFile->pInode->pLockMutex);
         1176  +}
         1177  +int unixFileMutexNotheld(unixFile *pFile){
         1178  +  assert( pFile->pInode );
         1179  +  return sqlite3_mutex_notheld(pFile->pInode->pLockMutex);
         1180  +}
         1181  +#endif
  1151   1182   
  1152   1183   /*
  1153   1184   **
  1154   1185   ** This function - unixLogErrorAtLine(), is only ever called via the macro
  1155   1186   ** unixLogError().
  1156   1187   **
  1157   1188   ** It is invoked after an error occurs in an OS function and errno has been
................................................................................
  1249   1280   /*
  1250   1281   ** Close all file descriptors accumuated in the unixInodeInfo->pUnused list.
  1251   1282   */ 
  1252   1283   static void closePendingFds(unixFile *pFile){
  1253   1284     unixInodeInfo *pInode = pFile->pInode;
  1254   1285     UnixUnusedFd *p;
  1255   1286     UnixUnusedFd *pNext;
         1287  +  assert( unixFileMutexHeld(pFile) );
  1256   1288     for(p=pInode->pUnused; p; p=pNext){
  1257   1289       pNext = p->pNext;
  1258   1290       robust_close(pFile, p->fd, __LINE__);
  1259   1291       sqlite3_free(p);
  1260         -    nUnusedFd--;
  1261   1292     }
  1262   1293     pInode->pUnused = 0;
  1263   1294   }
  1264   1295   
  1265   1296   /*
  1266   1297   ** Release a unixInodeInfo structure previously allocated by findInodeInfo().
  1267   1298   **
  1268   1299   ** The mutex entered using the unixEnterMutex() function must be held
  1269   1300   ** when this function is called.
  1270   1301   */
  1271   1302   static void releaseInodeInfo(unixFile *pFile){
  1272   1303     unixInodeInfo *pInode = pFile->pInode;
  1273   1304     assert( unixMutexHeld() );
         1305  +  assert( unixFileMutexNotheld(pFile) );
  1274   1306     if( ALWAYS(pInode) ){
  1275   1307       pInode->nRef--;
  1276   1308       if( pInode->nRef==0 ){
  1277   1309         assert( pInode->pShmNode==0 );
         1310  +      sqlite3_mutex_enter(pInode->pLockMutex);
  1278   1311         closePendingFds(pFile);
         1312  +      sqlite3_mutex_leave(pInode->pLockMutex);
  1279   1313         if( pInode->pPrev ){
  1280   1314           assert( pInode->pPrev->pNext==pInode );
  1281   1315           pInode->pPrev->pNext = pInode->pNext;
  1282   1316         }else{
  1283   1317           assert( inodeList==pInode );
  1284   1318           inodeList = pInode->pNext;
  1285   1319         }
................................................................................
  1287   1321           assert( pInode->pNext->pPrev==pInode );
  1288   1322           pInode->pNext->pPrev = pInode->pPrev;
  1289   1323         }
  1290   1324         sqlite3_mutex_free(pInode->pLockMutex);
  1291   1325         sqlite3_free(pInode);
  1292   1326       }
  1293   1327     }
  1294         -  assert( inodeList!=0 || nUnusedFd==0 );
  1295   1328   }
  1296   1329   
  1297   1330   /*
  1298   1331   ** Given a file descriptor, locate the unixInodeInfo object that
  1299   1332   ** describes that file descriptor.  Create a new one if necessary.  The
  1300   1333   ** return value might be uninitialized if an error occurs.
  1301   1334   **
................................................................................
  1357   1390     memset(&fileId, 0, sizeof(fileId));
  1358   1391     fileId.dev = statbuf.st_dev;
  1359   1392   #if OS_VXWORKS
  1360   1393     fileId.pId = pFile->pId;
  1361   1394   #else
  1362   1395     fileId.ino = (u64)statbuf.st_ino;
  1363   1396   #endif
  1364         -  assert( inodeList!=0 || nUnusedFd==0 );
  1365   1397     pInode = inodeList;
  1366   1398     while( pInode && memcmp(&fileId, &pInode->fileId, sizeof(fileId)) ){
  1367   1399       pInode = pInode->pNext;
  1368   1400     }
  1369   1401     if( pInode==0 ){
  1370   1402       pInode = sqlite3_malloc64( sizeof(*pInode) );
  1371   1403       if( pInode==0 ){
................................................................................
  1822   1854   /*
  1823   1855   ** Add the file descriptor used by file handle pFile to the corresponding
  1824   1856   ** pUnused list.
  1825   1857   */
  1826   1858   static void setPendingFd(unixFile *pFile){
  1827   1859     unixInodeInfo *pInode = pFile->pInode;
  1828   1860     UnixUnusedFd *p = pFile->pPreallocatedUnused;
         1861  +  assert( unixFileMutexHeld(pFile) );
  1829   1862     p->pNext = pInode->pUnused;
  1830   1863     pInode->pUnused = p;
  1831   1864     pFile->h = -1;
  1832   1865     pFile->pPreallocatedUnused = 0;
  1833         -  nUnusedFd++;
  1834   1866   }
  1835   1867   
  1836   1868   /*
  1837   1869   ** Lower the locking level on file descriptor pFile to eFileLock.  eFileLock
  1838   1870   ** must be either NO_LOCK or SHARED_LOCK.
  1839   1871   **
  1840   1872   ** If the locking level of the file descriptor is already at or below
................................................................................
  1984   2016   
  1985   2017       /* Decrement the count of locks against this same file.  When the
  1986   2018       ** count reaches zero, close any other file descriptors whose close
  1987   2019       ** was deferred because of outstanding locks.
  1988   2020       */
  1989   2021       pInode->nLock--;
  1990   2022       assert( pInode->nLock>=0 );
  1991         -    if( pInode->nLock==0 ){
  1992         -      closePendingFds(pFile);
  1993         -    }
         2023  +    if( pInode->nLock==0 ) closePendingFds(pFile);
  1994   2024     }
  1995   2025   
  1996   2026   end_unlock:
  1997   2027     sqlite3_mutex_leave(pInode->pLockMutex);
  1998         -  if( rc==SQLITE_OK ) pFile->eFileLock = eFileLock;
         2028  +  if( rc==SQLITE_OK ){
         2029  +    pFile->eFileLock = eFileLock;
         2030  +  }
  1999   2031     return rc;
  2000   2032   }
  2001   2033   
  2002   2034   /*
  2003   2035   ** Lower the locking level on file descriptor pFile to eFileLock.  eFileLock
  2004   2036   ** must be either NO_LOCK or SHARED_LOCK.
  2005   2037   **
................................................................................
  2062   2094   
  2063   2095   /*
  2064   2096   ** Close a file.
  2065   2097   */
  2066   2098   static int unixClose(sqlite3_file *id){
  2067   2099     int rc = SQLITE_OK;
  2068   2100     unixFile *pFile = (unixFile *)id;
         2101  +  unixInodeInfo *pInode = pFile->pInode;
         2102  +
         2103  +  assert( pInode!=0 );
  2069   2104     verifyDbFile(pFile);
  2070   2105     unixUnlock(id, NO_LOCK);
         2106  +  assert( unixFileMutexNotheld(pFile) );
  2071   2107     unixEnterMutex();
  2072   2108   
  2073   2109     /* unixFile.pInode is always valid here. Otherwise, a different close
  2074   2110     ** routine (e.g. nolockClose()) would be called instead.
  2075   2111     */
  2076   2112     assert( pFile->pInode->nLock>0 || pFile->pInode->bProcessLock==0 );
  2077         -  if( ALWAYS(pFile->pInode) && pFile->pInode->nLock ){
         2113  +  sqlite3_mutex_enter(pInode->pLockMutex);
         2114  +  if( pFile->pInode->nLock ){
  2078   2115       /* If there are outstanding locks, do not actually close the file just
  2079   2116       ** yet because that would clear those locks.  Instead, add the file
  2080   2117       ** descriptor to pInode->pUnused list.  It will be automatically closed 
  2081   2118       ** when the last lock is cleared.
  2082   2119       */
  2083   2120       setPendingFd(pFile);
  2084   2121     }
         2122  +  sqlite3_mutex_leave(pInode->pLockMutex);
  2085   2123     releaseInodeInfo(pFile);
  2086   2124     rc = closeUnixFile(id);
  2087   2125     unixLeaveMutex();
  2088   2126     return rc;
  2089   2127   }
  2090   2128   
  2091   2129   /************** End of the posix advisory lock implementation *****************
................................................................................
  2675   2713    ** Close a file.
  2676   2714    */
  2677   2715   static int semXClose(sqlite3_file *id) {
  2678   2716     if( id ){
  2679   2717       unixFile *pFile = (unixFile*)id;
  2680   2718       semXUnlock(id, NO_LOCK);
  2681   2719       assert( pFile );
         2720  +    assert( unixFileMutexNotheld(pFile) );
  2682   2721       unixEnterMutex();
  2683   2722       releaseInodeInfo(pFile);
  2684   2723       unixLeaveMutex();
  2685   2724       closeUnixFile(id);
  2686   2725     }
  2687   2726     return SQLITE_OK;
  2688   2727   }
................................................................................
  3115   3154           pInode->eFileLock = NO_LOCK;
  3116   3155           pFile->eFileLock = NO_LOCK;
  3117   3156         }
  3118   3157       }
  3119   3158       if( rc==SQLITE_OK ){
  3120   3159         pInode->nLock--;
  3121   3160         assert( pInode->nLock>=0 );
  3122         -      if( pInode->nLock==0 ){
  3123         -        closePendingFds(pFile);
  3124         -      }
         3161  +      if( pInode->nLock==0 ) closePendingFds(pFile);
  3125   3162       }
  3126   3163     }
  3127   3164     
  3128   3165     sqlite3_mutex_leave(pInode->pLockMutex);
  3129         -  if( rc==SQLITE_OK ) pFile->eFileLock = eFileLock;
         3166  +  if( rc==SQLITE_OK ){
         3167  +    pFile->eFileLock = eFileLock;
         3168  +  }
  3130   3169     return rc;
  3131   3170   }
  3132   3171   
  3133   3172   /*
  3134   3173   ** Close a file & cleanup AFP specific locking context 
  3135   3174   */
  3136   3175   static int afpClose(sqlite3_file *id) {
  3137   3176     int rc = SQLITE_OK;
  3138   3177     unixFile *pFile = (unixFile*)id;
  3139   3178     assert( id!=0 );
  3140   3179     afpUnlock(id, NO_LOCK);
         3180  +  assert( unixFileMutexNotheld(pFile) );
  3141   3181     unixEnterMutex();
  3142         -  if( pFile->pInode && pFile->pInode->nLock ){
  3143         -    /* If there are outstanding locks, do not actually close the file just
  3144         -    ** yet because that would clear those locks.  Instead, add the file
  3145         -    ** descriptor to pInode->aPending.  It will be automatically closed when
  3146         -    ** the last lock is cleared.
  3147         -    */
  3148         -    setPendingFd(pFile);
         3182  +  if( pFile->pInode ){
         3183  +    unixInodeInfo *pInode = pFile->pInode;
         3184  +    sqlite3_mutex_enter(pInode->pLockMutex);
         3185  +    if( pFile->pInode->nLock ){
         3186  +      /* If there are outstanding locks, do not actually close the file just
         3187  +      ** yet because that would clear those locks.  Instead, add the file
         3188  +      ** descriptor to pInode->aPending.  It will be automatically closed when
         3189  +      ** the last lock is cleared.
         3190  +      */
         3191  +      setPendingFd(pFile);
         3192  +    }
         3193  +    sqlite3_mutex_leave(pInode->pLockMutex);
  3149   3194     }
  3150   3195     releaseInodeInfo(pFile);
  3151   3196     sqlite3_free(pFile->lockingContext);
  3152   3197     rc = closeUnixFile(id);
  3153   3198     unixLeaveMutex();
  3154   3199     return rc;
  3155   3200   }
................................................................................
  4447   4492     if( p==0 ) return SQLITE_NOMEM_BKPT;
  4448   4493     memset(p, 0, sizeof(*p));
  4449   4494     assert( pDbFd->pShm==0 );
  4450   4495   
  4451   4496     /* Check to see if a unixShmNode object already exists. Reuse an existing
  4452   4497     ** one if present. Create a new one if necessary.
  4453   4498     */
         4499  +  assert( unixFileMutexNotheld(pDbFd) );
  4454   4500     unixEnterMutex();
  4455   4501     pInode = pDbFd->pInode;
  4456   4502     pShmNode = pInode->pShmNode;
  4457   4503     if( pShmNode==0 ){
  4458   4504       struct stat sStat;                 /* fstat() info for database file */
  4459   4505   #ifndef SQLITE_SHM_DIRECTORY
  4460   4506       const char *zBasePath = pDbFd->zPath;
................................................................................
  4829   4875   ** any load or store begun after the barrier.
  4830   4876   */
  4831   4877   static void unixShmBarrier(
  4832   4878     sqlite3_file *fd                /* Database file holding the shared memory */
  4833   4879   ){
  4834   4880     UNUSED_PARAMETER(fd);
  4835   4881     sqlite3MemoryBarrier();         /* compiler-defined memory barrier */
         4882  +  assert( unixFileMutexNotheld((unixFile*)fd) );
  4836   4883     unixEnterMutex();               /* Also mutex, for redundancy */
  4837   4884     unixLeaveMutex();
  4838   4885   }
  4839   4886   
  4840   4887   /*
  4841   4888   ** Close a connection to shared-memory.  Delete the underlying 
  4842   4889   ** storage if deleteFlag is true.
................................................................................
  4870   4917     /* Free the connection p */
  4871   4918     sqlite3_free(p);
  4872   4919     pDbFd->pShm = 0;
  4873   4920     sqlite3_mutex_leave(pShmNode->mutex);
  4874   4921   
  4875   4922     /* If pShmNode->nRef has reached 0, then close the underlying
  4876   4923     ** shared-memory file, too */
         4924  +  assert( unixFileMutexNotheld(pDbFd) );
  4877   4925     unixEnterMutex();
  4878   4926     assert( pShmNode->nRef>0 );
  4879   4927     pShmNode->nRef--;
  4880   4928     if( pShmNode->nRef==0 ){
  4881   4929       if( deleteFlag && pShmNode->h>=0 ){
  4882   4930         osUnlink(pShmNode->zFilename);
  4883   4931       }
................................................................................
  5196   5244     unixUnlock,               /* xUnlock method */
  5197   5245     unixCheckReservedLock,    /* xCheckReservedLock method */
  5198   5246     unixShmMap                /* xShmMap method */
  5199   5247   )
  5200   5248   IOMETHODS(
  5201   5249     nolockIoFinder,           /* Finder function name */
  5202   5250     nolockIoMethods,          /* sqlite3_io_methods object name */
  5203         -  3,                        /* shared memory is disabled */
         5251  +  3,                        /* shared memory and mmap are enabled */
  5204   5252     nolockClose,              /* xClose method */
  5205   5253     nolockLock,               /* xLock method */
  5206   5254     nolockUnlock,             /* xUnlock method */
  5207   5255     nolockCheckReservedLock,  /* xCheckReservedLock method */
  5208   5256     0                         /* xShmMap method */
  5209   5257   )
  5210   5258   IOMETHODS(
................................................................................
  5692   5740     ** almost certain that an open() call on the same path will also fail.
  5693   5741     ** For this reason, if an error occurs in the stat() call here, it is
  5694   5742     ** ignored and -1 is returned. The caller will try to open a new file
  5695   5743     ** descriptor on the same path, fail, and return an error to SQLite.
  5696   5744     **
  5697   5745     ** Even if a subsequent open() call does succeed, the consequences of
  5698   5746     ** not searching for a reusable file descriptor are not dire.  */
  5699         -  if( nUnusedFd>0 && 0==osStat(zPath, &sStat) ){
         5747  +  if( inodeList!=0 && 0==osStat(zPath, &sStat) ){
  5700   5748       unixInodeInfo *pInode;
  5701   5749   
  5702   5750       pInode = inodeList;
  5703   5751       while( pInode && (pInode->fileId.dev!=sStat.st_dev
  5704   5752                        || pInode->fileId.ino!=(u64)sStat.st_ino) ){
  5705   5753          pInode = pInode->pNext;
  5706   5754       }
  5707   5755       if( pInode ){
  5708   5756         UnixUnusedFd **pp;
         5757  +      assert( sqlite3_mutex_notheld(pInode->pLockMutex) );
         5758  +      sqlite3_mutex_enter(pInode->pLockMutex);
  5709   5759         for(pp=&pInode->pUnused; *pp && (*pp)->flags!=flags; pp=&((*pp)->pNext));
  5710   5760         pUnused = *pp;
  5711   5761         if( pUnused ){
  5712         -        nUnusedFd--;
  5713   5762           *pp = pUnused->pNext;
  5714   5763         }
         5764  +      sqlite3_mutex_leave(pInode->pLockMutex);
  5715   5765       }
  5716   5766     }
  5717   5767     unixLeaveMutex();
  5718   5768   #endif    /* if !OS_VXWORKS */
  5719   5769     return pUnused;
  5720   5770   }
  5721   5771   

Changes to src/vdbe.h.

   234    234   void sqlite3VdbeRunOnlyOnce(Vdbe*);
   235    235   void sqlite3VdbeReusable(Vdbe*);
   236    236   void sqlite3VdbeDelete(Vdbe*);
   237    237   void sqlite3VdbeClearObject(sqlite3*,Vdbe*);
   238    238   void sqlite3VdbeMakeReady(Vdbe*,Parse*);
   239    239   int sqlite3VdbeFinalize(Vdbe*);
   240    240   void sqlite3VdbeResolveLabel(Vdbe*, int);
   241         -#ifdef SQLITE_COVERAGE_TEST
   242         -  int sqlite3VdbeLabelHasBeenResolved(Vdbe*,int);
   243         -#endif
   244    241   int sqlite3VdbeCurrentAddr(Vdbe*);
   245    242   #ifdef SQLITE_DEBUG
   246    243     int sqlite3VdbeAssertMayAbort(Vdbe *, int);
   247    244   #endif
   248    245   void sqlite3VdbeResetStepResult(Vdbe*);
   249    246   void sqlite3VdbeRewind(Vdbe*);
   250    247   int sqlite3VdbeReset(Vdbe*);

Changes to src/vdbeaux.c.

   433    433       }
   434    434   #endif
   435    435       assert( p->aLabel[j]==(-1) ); /* Labels may only be resolved once */
   436    436       p->aLabel[j] = v->nOp;
   437    437     }
   438    438   }
   439    439   
   440         -#ifdef SQLITE_COVERAGE_TEST
   441         -/*
   442         -** Return TRUE if and only if the label x has already been resolved.
   443         -** Return FALSE (zero) if label x is still unresolved.
   444         -**
   445         -** This routine is only used inside of testcase() macros, and so it
   446         -** only exists when measuring test coverage.
   447         -*/
   448         -int sqlite3VdbeLabelHasBeenResolved(Vdbe *v, int x){
   449         -  return v->pParse->aLabel && v->pParse->aLabel[ADDR(x)]>=0;
   450         -}
   451         -#endif /* SQLITE_COVERAGE_TEST */
   452         -
   453    440   /*
   454    441   ** Mark the VDBE as one that can only be run one time.
   455    442   */
   456    443   void sqlite3VdbeRunOnlyOnce(Vdbe *p){
   457    444     p->runOnlyOnce = 1;
   458    445   }
   459    446   

Changes to test/upsert1.test.

   123    123     PRAGMA integrity_check;
   124    124   } {ok}
   125    125   do_execsql_test upsert1-610 {
   126    126     DELETE FROM t1;
   127    127     INSERT OR IGNORE INTO t1(a) VALUES('1'),(1) ON CONFLICT(a) DO NOTHING;
   128    128     PRAGMA integrity_check;
   129    129   } {ok}
          130  +
          131  +# 2018-08-14
          132  +# Ticket https://www.sqlite.org/src/info/908f001483982c43
          133  +# If there are multiple uniqueness contraints, the UPSERT should fire
          134  +# if the one constraint it targets fails, regardless of whether or not
          135  +# the other constraints pass or fail.  In other words, the UPSERT constraint
          136  +# should be tested first.
          137  +#
          138  +do_execsql_test upsert1-700 {
          139  +  DROP TABLE t1;
          140  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT, d INT, e INT);
          141  +  CREATE UNIQUE INDEX t1b ON t1(b);
          142  +  CREATE UNIQUE INDEX t1e ON t1(e);
          143  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
          144  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
          145  +    ON CONFLICT(e) DO UPDATE SET c=excluded.c;
          146  +  SELECT * FROM t1;
          147  +} {1 2 33 4 5}
          148  +do_execsql_test upsert1-710 {
          149  +  DELETE FROM t1;
          150  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
          151  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
          152  +    ON CONFLICT(a) DO UPDATE SET c=excluded.c;
          153  +  SELECT * FROM t1;
          154  +} {1 2 33 4 5}
          155  +do_execsql_test upsert1-720 {
          156  +  DELETE FROM t1;
          157  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
          158  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
          159  +    ON CONFLICT(b) DO UPDATE SET c=excluded.c;
          160  +  SELECT * FROM t1;
          161  +} {1 2 33 4 5}
          162  +do_execsql_test upsert1-730 {
          163  +  DROP TABLE t1;
          164  +  CREATE TABLE t1(a INT, b INT, c INT, d INT, e INT);
          165  +  CREATE UNIQUE INDEX t1a ON t1(a);
          166  +  CREATE UNIQUE INDEX t1b ON t1(b);
          167  +  CREATE UNIQUE INDEX t1e ON t1(e);
          168  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
          169  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
          170  +    ON CONFLICT(e) DO UPDATE SET c=excluded.c;
          171  +  SELECT * FROM t1;
          172  +} {1 2 33 4 5}
          173  +do_execsql_test upsert1-740 {
          174  +  DELETE FROM t1;
          175  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
          176  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
          177  +    ON CONFLICT(a) DO UPDATE SET c=excluded.c;
          178  +  SELECT * FROM t1;
          179  +} {1 2 33 4 5}
          180  +do_execsql_test upsert1-750 {
          181  +  DELETE FROM t1;
          182  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
          183  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
          184  +    ON CONFLICT(b) DO UPDATE SET c=excluded.c;
          185  +  SELECT * FROM t1;
          186  +} {1 2 33 4 5}
          187  +do_execsql_test upsert1-760 {
          188  +  DROP TABLE t1;
          189  +  CREATE TABLE t1(a INT PRIMARY KEY, b INT, c INT, d INT, e INT) WITHOUT ROWID;
          190  +  CREATE UNIQUE INDEX t1a ON t1(a);
          191  +  CREATE UNIQUE INDEX t1b ON t1(b);
          192  +  CREATE UNIQUE INDEX t1e ON t1(e);
          193  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
          194  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
          195  +    ON CONFLICT(e) DO UPDATE SET c=excluded.c;
          196  +  SELECT * FROM t1;
          197  +} {1 2 33 4 5}
          198  +do_execsql_test upsert1-770 {
          199  +  DELETE FROM t1;
          200  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
          201  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
          202  +    ON CONFLICT(a) DO UPDATE SET c=excluded.c;
          203  +  SELECT * FROM t1;
          204  +} {1 2 33 4 5}
          205  +do_execsql_test upsert1-780 {
          206  +  DELETE FROM t1;
          207  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
          208  +  INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
          209  +    ON CONFLICT(b) DO UPDATE SET c=excluded.c;
          210  +  SELECT * FROM t1;
          211  +} {1 2 33 4 5}
          212  +
   130    213   
   131    214   finish_test