Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all

classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|

Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all

Yuri
The attached testcase injects the foreign key violation into a long
transaction. This makes the remainder of the transaction much slower,
even though the foreign key is deferred, and should only be checked in
the end of the transaction.


While working on this testcase, I found that sometimes the foreign key
violation doesn't trigger the error at all. Please change VIOLATION to
0, and observe that there is no failure now, though it should be.


sqlite3-3.24.0 on FreeBSD 11.2


Yuri



---testcase---

#include <sqlite3.h>
#include <stdio.h>
#include <stdlib.h>
#include <time.h>

void doSql(sqlite3 *db, const char *sql) {
   char *err_msg = 0;
   int rc = sqlite3_exec(db, sql, 0, 0, &err_msg);
   if (rc != SQLITE_OK ) {
     fprintf(stderr, "SQL error: %s\n", err_msg);
     sqlite3_free(err_msg);
     sqlite3_close(db);
     exit(1);
   }
}

#define NROWS 100000
#define VIOLATION 1000000

int main(void) {
   sqlite3 *db;

   char s[512];

   int rc = sqlite3_open(":memory:", &db);
   if (rc != SQLITE_OK) {
     fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
     sqlite3_close(db);
     return 1;
   }

   doSql(db, "PRAGMA foreign_keys = ON;");

   printf("creating B ...\n");
   doSql(db, "create table b (id int PRIMARY KEY, name text)");

   printf("populating B ...\n");
   for (int i = 0; i < NROWS; i++) {
     sprintf(s, "insert into b values(%d, 'The name field for %d')", i, i);
     doSql(db, s);
   }

   printf("creating A ...\n");
   doSql(db, "create table a (id int PRIMARY KEY, name text, aid int not
null, FOREIGN KEY(aid) REFERENCES a(id))");

   printf("populating A ...\n");
   doSql(db, "BEGIN TRANSACTION;");
   doSql(db, "PRAGMA defer_foreign_keys=ON;");
   for (int i = 0; i < NROWS; i++) {
     if (i % 1000 == 0)
       printf("...row#%d... (time=%ld)\n", i, time(0));
     sprintf(s, "insert into a values(%d, 'The name field for %d', %d)",
i, i, i);
     doSql(db, s);
     // introfuce the FK violation
     if (i == NROWS/4) {
       sprintf(s, "insert into a values(%d, 'The name field for %d',
%d)", NROWS+i, i, NROWS+i+VIOLATION);
       doSql(db, s);
     }
   }
   doSql(db, "COMMIT TRANSACTION;");

   sqlite3_close(db);

   return 0;
}

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all

Keith Medcalf

You observe no violation when VIOLATION is 0 because there is no referential integrity violation to report ...

However, you are correct that when inserting data the as shown in your code (where there is a referential integrity violation) the insertion is much slower after the violation occurs, presumably because on each subsequent insert into table a it is checking whether the deferred violation has been resolved yet.  Presently this is how the referential integrity checking works in SQLite3.

Note that if you change your reference to table b rather than table a and you are operating only on table a this does not occur since there is no operation on table b which might resolve the violation.  It also makes the extra insert a violation when you define VIOLATION as 0 ...

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Yuri
>Sent: Wednesday, 1 August, 2018 16:57
>To: [hidden email]
>Subject: [sqlite] Violated failed foreign key constraint delays the
>rest of transaction ; Some foreign key violations don't trigger the
>error at all
>
>The attached testcase injects the foreign key violation into a long
>transaction. This makes the remainder of the transaction much slower,
>even though the foreign key is deferred, and should only be checked
>in
>the end of the transaction.
>
>
>While working on this testcase, I found that sometimes the foreign
>key
>violation doesn't trigger the error at all. Please change VIOLATION
>to
>0, and observe that there is no failure now, though it should be.
>
>
>sqlite3-3.24.0 on FreeBSD 11.2
>
>
>Yuri
>
>
>
>---testcase---
>
>#include <sqlite3.h>
>#include <stdio.h>
>#include <stdlib.h>
>#include <time.h>
>
>void doSql(sqlite3 *db, const char *sql) {
>   char *err_msg = 0;
>   int rc = sqlite3_exec(db, sql, 0, 0, &err_msg);
>   if (rc != SQLITE_OK ) {
>     fprintf(stderr, "SQL error: %s\n", err_msg);
>     sqlite3_free(err_msg);
>     sqlite3_close(db);
>     exit(1);
>   }
>}
>
>#define NROWS 100000
>#define VIOLATION 1000000
>
>int main(void) {
>   sqlite3 *db;
>
>   char s[512];
>
>   int rc = sqlite3_open(":memory:", &db);
>   if (rc != SQLITE_OK) {
>     fprintf(stderr, "Cannot open database: %s\n",
>sqlite3_errmsg(db));
>     sqlite3_close(db);
>     return 1;
>   }
>
>   doSql(db, "PRAGMA foreign_keys = ON;");
>
>   printf("creating B ...\n");
>   doSql(db, "create table b (id int PRIMARY KEY, name text)");
>
>   printf("populating B ...\n");
>   for (int i = 0; i < NROWS; i++) {
>     sprintf(s, "insert into b values(%d, 'The name field for %d')",
>i, i);
>     doSql(db, s);
>   }
>
>   printf("creating A ...\n");
>   doSql(db, "create table a (id int PRIMARY KEY, name text, aid int
>not
>null, FOREIGN KEY(aid) REFERENCES a(id))");
>
>   printf("populating A ...\n");
>   doSql(db, "BEGIN TRANSACTION;");
>   doSql(db, "PRAGMA defer_foreign_keys=ON;");
>   for (int i = 0; i < NROWS; i++) {
>     if (i % 1000 == 0)
>       printf("...row#%d... (time=%ld)\n", i, time(0));
>     sprintf(s, "insert into a values(%d, 'The name field for %d',
>%d)",
>i, i, i);
>     doSql(db, s);
>     // introfuce the FK violation
>     if (i == NROWS/4) {
>       sprintf(s, "insert into a values(%d, 'The name field for %d',
>%d)", NROWS+i, i, NROWS+i+VIOLATION);
>       doSql(db, s);
>     }
>   }
>   doSql(db, "COMMIT TRANSACTION;");
>
>   sqlite3_close(db);
>
>   return 0;
>}
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all

Dominique Devienne
In reply to this post by Yuri
On Thu, Aug 2, 2018 at 12:57 AM Yuri <[hidden email]> wrote:

> The attached testcase injects the foreign key violation into a long
> transaction. This makes the remainder of the transaction much slower,
> even though the foreign key is deferred, and should only be checked in
> the end of the transaction.
>

Right. Reproduced on Win7 VS2017 (/Od x64), SQLite 3.24 from official
amalgamation.
(see 1st run's output at the end)

First 25K rows inserted within the same second,
then after the first violation is introduced, each 1K batch takes 3-4s,
increasing up to 12-13s per 1K batch towards the end.

That's a serious slow down indeed. Behaves correctly, so not a bug,
but a fairly big missed opportunity to optimize this case.

While working on this testcase, I found that sometimes the foreign key
> violation doesn't trigger the error at all. Please change VIOLATION to
> 0, and observe that there is no failure now, though it should be.
>

Also reproduced (see 2nd run output below).
This does appear as a bug, OTOH, although maybe I'm missing something...
I'm sure DRH or Dan will tell us soon what's going on.

That one is "fast all the way", taking 2-3s, and reports no violation
indeed.

In a weird way, that's consistent, since it "doesn't see" the violations,
it remains "fast" I guess :)

Interesting test case Yuri! --DD

--DD

--- 1st run: with VIOLATION = 1000000 ----
D:\>sqlite_fk_error.exe
creating B ...
populating B ...
creating A ...
populating A ...
...row#0... (time=1533192850)
...row#1000... (time=1533192850)
...row#2000... (time=1533192850)
...row#3000... (time=1533192850)
...row#4000... (time=1533192850)
...row#5000... (time=1533192850)
...row#6000... (time=1533192850)
...row#7000... (time=1533192850)
...row#8000... (time=1533192850)
...row#9000... (time=1533192850)
...row#10000... (time=1533192850)
...row#11000... (time=1533192850)
...row#12000... (time=1533192850)
...row#13000... (time=1533192850)
...row#14000... (time=1533192850)
...row#15000... (time=1533192850)
...row#16000... (time=1533192850)
...row#17000... (time=1533192850)
...row#18000... (time=1533192850)
...row#19000... (time=1533192850)
...row#20000... (time=1533192850)
...row#21000... (time=1533192850)
...row#22000... (time=1533192850)
...row#23000... (time=1533192850)
...row#24000... (time=1533192850)
...row#25000... (time=1533192850)
...row#26000... (time=1533192853)
...row#27000... (time=1533192857)
...row#28000... (time=1533192860)
...row#29000... (time=1533192864)
...row#30000... (time=1533192868)
...row#31000... (time=1533192872)
...row#32000... (time=1533192876)
...row#33000... (time=1533192880)
...row#34000... (time=1533192884)
...row#35000... (time=1533192889)
...row#36000... (time=1533192893)
...row#37000... (time=1533192898)
...row#38000... (time=1533192903)
...row#39000... (time=1533192908)
...row#40000... (time=1533192913)
...row#41000... (time=1533192918)
...row#42000... (time=1533192924)
...row#43000... (time=1533192929)
...row#44000... (time=1533192935)
...row#45000... (time=1533192940)
...row#46000... (time=1533192946)
...row#47000... (time=1533192952)
...row#48000... (time=1533192959)
...row#49000... (time=1533192965)
...row#50000... (time=1533192971)
...row#51000... (time=1533192978)
...row#52000... (time=1533192985)
...row#53000... (time=1533192992)
...row#54000... (time=1533192999)
...row#55000... (time=1533193006)
...row#56000... (time=1533193013)
...row#57000... (time=1533193020)
...row#58000... (time=1533193028)
...row#59000... (time=1533193035)
...row#60000... (time=1533193043)
...row#61000... (time=1533193051)
...row#62000... (time=1533193059)
...row#63000... (time=1533193067)
...row#64000... (time=1533193075)
...row#65000... (time=1533193084)
...row#66000... (time=1533193092)
...row#67000... (time=1533193101)
...row#68000... (time=1533193110)
...row#69000... (time=1533193119)
...row#70000... (time=1533193128)
...row#71000... (time=1533193137)
...row#72000... (time=1533193146)
...row#73000... (time=1533193156)
...row#74000... (time=1533193165)
...row#75000... (time=1533193175)
...row#76000... (time=1533193185)
...row#77000... (time=1533193195)
...row#78000... (time=1533193205)
...row#79000... (time=1533193216)
...row#80000... (time=1533193226)
...row#81000... (time=1533193236)
...row#82000... (time=1533193247)
...row#83000... (time=1533193258)
...row#84000... (time=1533193269)
...row#85000... (time=1533193280)
...row#86000... (time=1533193291)
...row#87000... (time=1533193303)
...row#88000... (time=1533193314)
...row#89000... (time=1533193326)
...row#90000... (time=1533193337)
...row#91000... (time=1533193349)
...row#92000... (time=1533193361)
...row#93000... (time=1533193373)
...row#94000... (time=1533193386)
...row#95000... (time=1533193398)
...row#96000... (time=1533193411)
...row#97000... (time=1533193423)
...row#98000... (time=1533193436)
...row#99000... (time=1533193449)
SQL error: FOREIGN KEY constraint failed

--- 2nd run: with VIOLATION = 0 ----
D:\>sqlite_fk_error.exe
creating B ...
populating B ...
creating A ...
populating A ...
...row#0... (time=1533194133)
...row#1000... (time=1533194133)
...row#2000... (time=1533194133)
...row#3000... (time=1533194133)
...row#4000... (time=1533194133)
...row#5000... (time=1533194133)
...row#6000... (time=1533194133)
...row#7000... (time=1533194133)
...row#8000... (time=1533194133)
...row#9000... (time=1533194133)
...row#10000... (time=1533194133)
...row#11000... (time=1533194133)
...row#12000... (time=1533194133)
...row#13000... (time=1533194133)
...row#14000... (time=1533194134)
...row#15000... (time=1533194134)
...row#16000... (time=1533194134)
...row#17000... (time=1533194134)
...row#18000... (time=1533194134)
...row#19000... (time=1533194134)
...row#20000... (time=1533194134)
...row#21000... (time=1533194134)
...row#22000... (time=1533194134)
...row#23000... (time=1533194134)
...row#24000... (time=1533194134)
...row#25000... (time=1533194134)
...row#26000... (time=1533194134)
...row#27000... (time=1533194134)
...row#28000... (time=1533194134)
...row#29000... (time=1533194134)
...row#30000... (time=1533194134)
...row#31000... (time=1533194134)
...row#32000... (time=1533194134)
...row#33000... (time=1533194134)
...row#34000... (time=1533194134)
...row#35000... (time=1533194134)
...row#36000... (time=1533194134)
...row#37000... (time=1533194134)
...row#38000... (time=1533194134)
...row#39000... (time=1533194134)
...row#40000... (time=1533194134)
...row#41000... (time=1533194134)
...row#42000... (time=1533194134)
...row#43000... (time=1533194134)
...row#44000... (time=1533194134)
...row#45000... (time=1533194134)
...row#46000... (time=1533194134)
...row#47000... (time=1533194134)
...row#48000... (time=1533194134)
...row#49000... (time=1533194134)
...row#50000... (time=1533194134)
...row#51000... (time=1533194134)
...row#52000... (time=1533194134)
...row#53000... (time=1533194134)
...row#54000... (time=1533194134)
...row#55000... (time=1533194134)
...row#56000... (time=1533194134)
...row#57000... (time=1533194134)
...row#58000... (time=1533194134)
...row#59000... (time=1533194134)
...row#60000... (time=1533194134)
...row#61000... (time=1533194134)
...row#62000... (time=1533194135)
...row#63000... (time=1533194135)
...row#64000... (time=1533194135)
...row#65000... (time=1533194135)
...row#66000... (time=1533194135)
...row#67000... (time=1533194135)
...row#68000... (time=1533194135)
...row#69000... (time=1533194135)
...row#70000... (time=1533194135)
...row#71000... (time=1533194135)
...row#72000... (time=1533194135)
...row#73000... (time=1533194135)
...row#74000... (time=1533194135)
...row#75000... (time=1533194135)
...row#76000... (time=1533194135)
...row#77000... (time=1533194135)
...row#78000... (time=1533194135)
...row#79000... (time=1533194135)
...row#80000... (time=1533194135)
...row#81000... (time=1533194135)
...row#82000... (time=1533194135)
...row#83000... (time=1533194135)
...row#84000... (time=1533194135)
...row#85000... (time=1533194135)
...row#86000... (time=1533194135)
...row#87000... (time=1533194135)
...row#88000... (time=1533194135)
...row#89000... (time=1533194135)
...row#90000... (time=1533194135)
...row#91000... (time=1533194135)
...row#92000... (time=1533194135)
...row#93000... (time=1533194135)
...row#94000... (time=1533194135)
...row#95000... (time=1533194135)
...row#96000... (time=1533194135)
...row#97000... (time=1533194135)
...row#98000... (time=1533194135)
...row#99000... (time=1533194135)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all

Dominique Devienne
In reply to this post by Keith Medcalf
On Thu, Aug 2, 2018 at 9:35 AM Keith Medcalf <[hidden email]> wrote:

> You observe no violation when VIOLATION is 0 because there is no
> referential integrity violation to report ...
>

Really Keith? Parent IDs are in range [0, NROWS)
Child/FK IDs inserted are in range  [NROWS, 2*NROWS)
How's that not an FK violation? At least that's how I read the code. Am I
reading wrong? --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all

Keith Medcalf

Yes.  Look at the CREATE TABLE for table A (completely ignore table B as it serves no purpose whatsoever)....

If you change the database to write to a database on disk so you can examine it after (or modify doSql so that it outputs the SQL so that you can read it) everything will become much clearer ...

Also, if you create an index on the child key as is required for performance, there is no "slowdown" whatsoever....

ie after this line:
   doSql(db, "create table a (id int PRIMARY KEY, name text, aid int not null, FOREIGN KEY(aid) REFERENCES a(id))");
insert this line"
   doSql(db, "create index aid on a (aid)");


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Dominique Devienne
>Sent: Thursday, 2 August, 2018 01:48
>To: General Discussion of SQLite Database
>Subject: Re: [sqlite] Violated failed foreign key constraint delays
>the rest of transaction ; Some foreign key violations don't trigger
>the error at all
>
>On Thu, Aug 2, 2018 at 9:35 AM Keith Medcalf <[hidden email]>
>wrote:
>
>> You observe no violation when VIOLATION is 0 because there is no
>> referential integrity violation to report ...
>>
>
>Really Keith? Parent IDs are in range [0, NROWS)
>Child/FK IDs inserted are in range  [NROWS, 2*NROWS)
>How's that not an FK violation? At least that's how I read the code.
>Am I
>reading wrong? --DD
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all

Keith Medcalf
In reply to this post by Dominique Devienne

This is the line that create the violation:

       sprintf(s, "insert into a values(%d, 'The name field for %d', %d)", NROWS+i, i, NROWS+i+VIOLATION);

if VIOLATION is 0 then you are inserting a record with id = NROWS+i and aid = NROWS+i.  Since the referential constraint is that aid references id in the same table, it is satisfied by the record itself, thus the constraint is not violated.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Dominique Devienne
>Sent: Thursday, 2 August, 2018 01:48
>To: General Discussion of SQLite Database
>Subject: Re: [sqlite] Violated failed foreign key constraint delays
>the rest of transaction ; Some foreign key violations don't trigger
>the error at all
>
>On Thu, Aug 2, 2018 at 9:35 AM Keith Medcalf <[hidden email]>
>wrote:
>
>> You observe no violation when VIOLATION is 0 because there is no
>> referential integrity violation to report ...
>>
>
>Really Keith? Parent IDs are in range [0, NROWS)
>Child/FK IDs inserted are in range  [NROWS, 2*NROWS)
>How's that not an FK violation? At least that's how I read the code.
>Am I
>reading wrong? --DD
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all

Dominique Devienne
In reply to this post by Keith Medcalf
On Thu, Aug 2, 2018 at 10:34 AM Keith Medcalf <[hidden email]> wrote:

>
> Yes.  Look at the CREATE TABLE for table A (completely ignore table B as
> it serves no purpose whatsoever)....
>

Rah, silly me... I assumed A.aid referenced B.id. Why have a B table at all
then.


> Also, if you create an index on the child key as is required for
> performance, there is no "slowdown" whatsoever....


Of course. Something I always do in Oracle, index all my FK columns...
Silly me again.

Boy, not my morning :). Thanks for setting me straight Keith. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all

David Raymond
The way I <think> it happens with deferred foreign keys is this (Gut feeling from observations. Experts please correct me if I'm way off):

Keep track with an integer, let's call it "netBroken"

While netBroken = 0 then when you insert a row you don't have to check if anything references it, only if it references something. (In this case that's a quick search with the primary key) If it does, then you're good. If it doesn't, then increment netBroken to let you know "hey, I broke something"

If netBroken is != 0, then when you insert a row you not only have to check if the new row is broken, but you also have to check if it fixes a previously broken thing. (In this case that includes an unindexed search on aid) If the new row doesn't reference something, then increment netBroken. If something references the new row, then decrement netBroken by the number of rows that reference it, "hey, I fixed something"

At the end of the transaction look at what you have for netBroken. If it's 0 then yay! Your balance sheet came out ok and you can commit. If it's non-zero then you didn't fix everything you broke. This also explains why with deferred foreign keys it can't tell you what record violated things, because it didn't keep track of the individual record, only the net count of broken things.


So for your test script there, try inserting a record with the violation ID a little bit later and see if it suddenly speeds up again. In my Python version of your script it does indeed speed back up again once the outstanding violation is fixed.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Dominique Devienne
Sent: Thursday, August 02, 2018 4:59 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all

On Thu, Aug 2, 2018 at 10:34 AM Keith Medcalf <[hidden email]> wrote:

>
> Yes.  Look at the CREATE TABLE for table A (completely ignore table B as
> it serves no purpose whatsoever)....
>

Rah, silly me... I assumed A.aid referenced B.id. Why have a B table at all
then.


> Also, if you create an index on the child key as is required for
> performance, there is no "slowdown" whatsoever....


Of course. Something I always do in Oracle, index all my FK columns...
Silly me again.

Boy, not my morning :). Thanks for setting me straight Keith. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all

Yuri
On 8/2/18 7:02 AM, David Raymond wrote:
> So for your test script there, try inserting a record with the violation ID a little bit later and see if it suddenly speeds up again. In my Python version of your script it does indeed speed back up again once the outstanding violation is fixed.


The main problem is that this bug makes it difficult to handle bugs in
our code. A bug causing the FK violation automatically wastes a lot of
time before being detected, because the slowdown is in the range of
1000X. FK violations aren't out of the ordinary or abnormal, they are a
valid outcome of some queries, and should be handled reasonably.


Yuri


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all

Keith Medcalf

Many versions ago a CLI command (that is, the sqlite3 Command Line Interface) was created so that folks would stop complaining about referential integrity enforcement being slow when they did not create the indexes that were necessary to enforce referential integrity (because failing to have the appropriate indexes means that a table scan is required, rather than a simple B-Tree index operation, and this is VERY slow, especially for non-trivially sized tables (meaning more than the number of rows that can be counted on one hand)).  This command is:

.lint fkey-indexes

and it will tell you what indexes you forgot to create that cause the issue you are seeing.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Yuri
>Sent: Thursday, 2 August, 2018 16:05
>To: [hidden email]
>Subject: Re: [sqlite] Violated failed foreign key constraint delays
>the rest of transaction ; Some foreign key violations don't trigger
>the error at all
>
>On 8/2/18 7:02 AM, David Raymond wrote:
>> So for your test script there, try inserting a record with the
>violation ID a little bit later and see if it suddenly speeds up
>again. In my Python version of your script it does indeed speed back
>up again once the outstanding violation is fixed.
>
>
>The main problem is that this bug makes it difficult to handle bugs
>in
>our code. A bug causing the FK violation automatically wastes a lot
>of
>time before being detected, because the slowdown is in the range of
>1000X. FK violations aren't out of the ordinary or abnormal, they are
>a
>valid outcome of some queries, and should be handled reasonably.
>
>
>Yuri
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all

Yuri
On 8/2/18 3:17 PM, Keith Medcalf wrote:
> .lint fkey-indexes
>
> and it will tell you what indexes you forgot to create that cause the issue you are seeing.


But this problem isn't about a missing index.


Yuri


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all

Keith Medcalf
It is.  If you create the missing index then your "testcase" does not demonstrate any slowdown.

After this line:
   doSql(db, "create table a (id int PRIMARY KEY, name text, aid int not null, FOREIGN KEY(aid) REFERENCES a(id))");
insert this line:
   doSql(db, "create index aid on a (aid)");

All your problems will vanish.

You are required to have a UNIQUE index on the PARENT KEYS in a foreign key relationship.

Depending on whether the PARENT:CHILD is 1:1 or 1:N you need either a UNIQUE (1:1) or regular index on the child key.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: Yuri [mailto:[hidden email]]
>Sent: Thursday, 2 August, 2018 16:37
>To: SQLite mailing list; Keith Medcalf
>Subject: Re: [sqlite] Violated failed foreign key constraint delays
>the rest of transaction ; Some foreign key violations don't trigger
>the error at all
>
>On 8/2/18 3:17 PM, Keith Medcalf wrote:
>> .lint fkey-indexes
>>
>> and it will tell you what indexes you forgot to create that cause
>the issue you are seeing.
>
>
>But this problem isn't about a missing index.
>
>
>Yuri
>
>
>




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all

Yuri
On 8/2/18 3:46 PM, Keith Medcalf wrote:
> You are required to have a UNIQUE index on the PARENT KEYS in a foreign key relationship.
>
> Depending on whether the PARENT:CHILD is 1:1 or 1:N you need either a UNIQUE (1:1) or regular index on the child key.


Why is index on PARENT KEY in a foreign key relationship required for
inserts? Missing index should slow down deletion of the target record in
FK, but insertions shouldn't need checking if such parent key is already
present or not. Insertion in the parent part of FK checks if the target
exists or not. If it exists, insertion succeeds, if not, it fails. It
doesn't need to check if another parent key already exists.


Yuri


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all

Graham Holden
Hello Yuri,

Friday, August 03, 2018, 12:06:14 AM, Yuri wrote:

> On 8/2/18 3:46 PM, Keith Medcalf wrote:
>> You are required to have a UNIQUE index on the PARENT KEYS in a foreign key relationship.
>>
>> Depending on whether the PARENT:CHILD is 1:1 or 1:N you need either
>> a UNIQUE (1:1) or regular index on the child key.

> Why is index on PARENT KEY in a foreign key relationship required for
> inserts? Missing index should slow down deletion of the target record in
> FK, but insertions shouldn't need checking if such parent key is already
> present or not. Insertion in the parent part of FK checks if the target
> exists or not. If it exists, insertion succeeds, if not, it fails. It
> doesn't need to check if another parent key already exists.

> Yuri

I believe David Raymond explained this: with deferred foreign key
checks, once there has been a violation, it not only needs to check
whether a new row _breaks_ a constraint, it needs to check whether
the new row _fixes_ a previous violation. For the latter, while you
don't _need_ the index Keith is suggesting, it prevents the slow-down
you are seeing.

Graham



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all

Keith Medcalf
In reply to this post by Yuri

You need a UNIQUE index on the PARENT KEY because when you operate on a child, you need to be able to look up the PARENT.  If there is no index on the PARENT KEY then you have to do a table scan.  A table scan of a billion parent records many take quite some time.

Similarly, you need an index on the CHILD KEY because when you operate on a PARENT record, you need to be able to look up the CHILD.  If there is no index on the CHILD KEY then you have to do a table scan.  A table scan of a billion child records may take quite some time.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: Yuri [mailto:[hidden email]]
>Sent: Thursday, 2 August, 2018 17:06
>To: SQLite mailing list; Keith Medcalf
>Subject: Re: [sqlite] Violated failed foreign key constraint delays
>the rest of transaction ; Some foreign key violations don't trigger
>the error at all
>
>On 8/2/18 3:46 PM, Keith Medcalf wrote:
>> You are required to have a UNIQUE index on the PARENT KEYS in a
>foreign key relationship.
>>
>> Depending on whether the PARENT:CHILD is 1:1 or 1:N you need either
>a UNIQUE (1:1) or regular index on the child key.
>
>
>Why is index on PARENT KEY in a foreign key relationship required for
>inserts? Missing index should slow down deletion of the target record
>in
>FK, but insertions shouldn't need checking if such parent key is
>already
>present or not. Insertion in the parent part of FK checks if the
>target
>exists or not. If it exists, insertion succeeds, if not, it fails. It
>doesn't need to check if another parent key already exists.
>
>
>Yuri
>
>
>




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users