On Thu, Aug 2, 2018 at 12:57 AM Yuri <y...@rawbw.com> 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
