From 713de341a7ab05201e7d9294354981314ad3f863 Mon Sep 17 00:00:00 2001 From: drh Date: Sun, 24 Apr 2011 22:56:07 +0000 Subject: [PATCH] Disable the transfer optimization if the destination table contains any foreign key constraint and foreign key constraints are enabled. Ticket [6284df89debdf]. FossilOrigin-Name: ddeea5ab5f6c0c4a86cdfbbb9f24d9d54bf8d301 --- manifest | 14 +++++------ manifest.uuid | 2 +- src/insert.c | 12 ++++++++++ test/insert4.test | 60 +++++++++++++++++++++++++++++++++++++++++++++++ 4 files changed, 80 insertions(+), 8 deletions(-) diff --git a/manifest b/manifest index 65bc4f3b22..8eaf915520 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Add\sthe\s"getlock"\sutility\sfor\sdetermining\sif\sa\sdatabase\sfile\s(on\sunix)\sis\ncurrently\slocked. -D 2011-04-22T22:55:10.113 +C Disable\sthe\stransfer\soptimization\sif\sthe\sdestination\stable\scontains\nany\sforeign\skey\sconstraint\sand\sforeign\skey\sconstraints\sare\senabled.\nTicket\s[6284df89debdf]. +D 2011-04-24T22:56:07.596 F Makefile.arm-wince-mingw32ce-gcc d6df77f1f48d690bd73162294bbba7f59507c72f F Makefile.in 7a4d9524721d40ef9ee26f93f9bd6a51dba106f2 F Makefile.linux-gcc 91d710bdc4998cb015f39edf3cb314ec4f4d7e23 @@ -138,7 +138,7 @@ F src/global.c 02335177cf6946fe5525c6f0755cf181140debf3 F src/hash.c 458488dcc159c301b8e7686280ab209f1fb915af F src/hash.h 2894c932d84d9f892d4b4023a75e501f83050970 F src/hwtime.h d32741c8f4df852c7d959236615444e2b1063b08 -F src/insert.c acfb89fe4a73d703e425e167bfcc72985f4299ae +F src/insert.c cdee360e5cea59db6c4a980e4360499631222af6 F src/journal.c 552839e54d1bf76fb8f7abe51868b66acacf6a0e F src/legacy.c a199d7683d60cef73089e892409113e69c23a99f F src/lempar.c 7f026423f4d71d989e719a743f98a1cbd4e6d99e @@ -505,7 +505,7 @@ F test/init.test 15c823093fdabbf7b531fe22cf037134d09587a7 F test/insert.test aef273dd1cee84cc92407469e6bd1b3cdcb76908 F test/insert2.test 4f3a04d168c728ed5ec2c88842e772606c7ce435 F test/insert3.test 1b7db95a03ad9c5013fdf7d6722b6cd66ee55e30 -F test/insert4.test c1469999a58e86a85b74df645a820f4cc7a8273b +F test/insert4.test b3e02648a5fc3075c29e13c369b5127bf859b5a2 F test/insert5.test 1f93cbe9742110119133d7e8e3ccfe6d7c249766 F test/intarray.test 066b7d7ac38d25bf96f87f1b017bfc687551cdd4 F test/interrupt.test 42e7cf98646fd9cb4a3b131a93ed3c50b9e149f1 @@ -930,7 +930,7 @@ F tool/speedtest8.c 2902c46588c40b55661e471d7a86e4dd71a18224 F tool/speedtest8inst1.c 293327bc76823f473684d589a8160bde1f52c14e F tool/split-sqlite3c.tcl d9be87f1c340285a3e081eb19b4a247981ed290c F tool/vdbe-compress.tcl d70ea6d8a19e3571d7ab8c9b75cba86d1173ff0f -P d8b149f5e465f7794739ed0210e1e5c53110ee9a -R 46d4cc9c199137dd735f2ac252175f1e +P 0ab24b133e332ad7f4517b8e113e9c241ee9af9f +R 40e74cf200b39d155b455f390ce862eb U drh -Z 48df56ba9c3ee078a0d4702e2f08ce89 +Z 87bc3e585b95e6d3397bca153c02ab82 diff --git a/manifest.uuid b/manifest.uuid index 92d78b219d..6503aa6c35 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -0ab24b133e332ad7f4517b8e113e9c241ee9af9f \ No newline at end of file +ddeea5ab5f6c0c4a86cdfbbb9f24d9d54bf8d301 \ No newline at end of file diff --git a/src/insert.c b/src/insert.c index 588a84f36d..02456e776f 100644 --- a/src/insert.c +++ b/src/insert.c @@ -1734,6 +1734,18 @@ static int xferOptimization( return 0; /* Tables have different CHECK constraints. Ticket #2252 */ } #endif +#ifndef SQLITE_OMIT_FOREIGN_KEY + /* Disallow the transfer optimization if the destination table constains + ** any foreign key constraints. This is more restrictive than necessary. + ** But the main beneficiary of the transfer optimization is the VACUUM + ** command, and the VACUUM command disables foreign key constraints. So + ** the extra complication to make this rule less restrictive is probably + ** not worth the effort. Ticket [6284df89debdfa61db8073e062908af0c9b6118e] + */ + if( (pParse->db->flags & SQLITE_ForeignKeys)!=0 && pDest->pFKey!=0 ){ + return 0; + } +#endif /* If we get this far, it means either: ** diff --git a/test/insert4.test b/test/insert4.test index 0b069e996d..44b428a1c7 100644 --- a/test/insert4.test +++ b/test/insert4.test @@ -326,4 +326,64 @@ do_test insert4-6.7 { } } {1 {constraint failed}} +# Ticket [6284df89debdfa61db8073e062908af0c9b6118e] +# Disable the xfer optimization if the destination table contains +# a foreign key constraint +# +ifcapable foreignkey { + do_test insert4-7.1 { + set ::sqlite3_xferopt_count 0 + execsql { + CREATE TABLE t7a(x INTEGER PRIMARY KEY); INSERT INTO t7a VALUES(123); + CREATE TABLE t7b(y INTEGER REFERENCES t7a); + CREATE TABLE t7c(z INT); INSERT INTO t7c VALUES(234); + INSERT INTO t7b SELECT * FROM t7c; + SELECT * FROM t7b; + } + } {234} + do_test insert4-7.2 { + set ::sqlite3_xferopt_count + } {1} + do_test insert4-7.3 { + set ::sqlite3_xferopt_count 0 + execsql { + DELETE FROM t7b; + PRAGMA foreign_keys=ON; + } + catchsql { + INSERT INTO t7b SELECT * FROM t7c; + } + } {1 {foreign key constraint failed}} + do_test insert4-7.4 { + execsql {SELECT * FROM t7b} + } {} + do_test insert4-7.5 { + set ::sqlite3_xferopt_count + } {0} + do_test insert4-7.6 { + set ::sqlite3_xferopt_count 0 + execsql { + DELETE FROM t7b; DELETE FROM t7c; + INSERT INTO t7c VALUES(123); + INSERT INTO t7b SELECT * FROM t7c; + SELECT * FROM t7b; + } + } {123} + do_test insert4-7.7 { + set ::sqlite3_xferopt_count + } {0} + do_test insert4-7.7 { + set ::sqlite3_xferopt_count 0 + execsql { + PRAGMA foreign_keys=OFF; + DELETE FROM t7b; + INSERT INTO t7b SELECT * FROM t7c; + SELECT * FROM t7b; + } + } {123} + do_test insert4-7.8 { + set ::sqlite3_xferopt_count + } {1} +} + finish_test -- 2.47.2