Tarantool development patches archive
 help / color / mirror / Atom feed
From: "N.Tatunov" <hollow653@gmail.com>
To: tarantool-patches@freelists.org
Cc: korablev@tarantool.org, "N.Tatunov" <hollow653@gmail.com>
Subject: [tarantool-patches] [PATCH 2/2] sql: statistics removal after dropping an index
Date: Wed,  4 Apr 2018 00:37:16 +0300	[thread overview]
Message-ID: <1522791436-8221-1-git-send-email-hollow653@gmail.com> (raw)

Currently dropping an index leads to removal of
all the entries containing the certain index name
in "_sql_statN" tables. Thus far analyze routine was fixed
so it seems that the indexes from the different tables but
with the same names should work more properly.

Closes: #3264
---

Branch: https://github.com/tarantool/tarantool/tree/N_Tatunov/gh-3264-stat-table-entries-removal
Issue: https://github.com/tarantool/tarantool/issues/3264

 src/box/sql/build.c                    |  41 ++++++-----
 test/sql/sql-statN-index-drop.result   | 127 +++++++++++++++++++++++++++++++++
 test/sql/sql-statN-index-drop.test.lua |  54 ++++++++++++++
 3 files changed, 206 insertions(+), 16 deletions(-)
 create mode 100644 test/sql/sql-statN-index-drop.result
 create mode 100644 test/sql/sql-statN-index-drop.test.lua

diff --git a/src/box/sql/build.c b/src/box/sql/build.c
index 5e3ed0f..44d7548 100644
--- a/src/box/sql/build.c
+++ b/src/box/sql/build.c
@@ -2207,25 +2207,34 @@ sqliteViewResetAll(sqlite3 * db)
 #endif				/* SQLITE_OMIT_VIEW */
 
 /*
- * Remove entries from the sqlite_statN tables (for N in (1,2,3))
+ * Remove entries from the _sql_statN tables (for N in (1, 4))
  * after a DROP INDEX or DROP TABLE command.
  */
 static void
-sqlite3ClearStatTables(Parse * pParse,	/* The parsing context */
-		       const char *zType,	/* "idx" or "tbl" */
-		       const char *zName	/* Name of index or table */
+sql_clear_stat_tables(Parse * pParse,	/* The parsing context */
+		       const char *zType,	    /* "idx" or "tbl" */
+               const char *table_name,  /* Name of the table*/
+		       const char *idx_name	/* Name of the index*/
     )
 {
-	int i;
-	for (i = 1; i <= 4; i++) {
-		char zTab[24];
-		sqlite3_snprintf(sizeof(zTab), zTab, "_sql_stat%d", i);
-		if (sqlite3FindTable(pParse->db, zTab)) {
-			sqlite3NestedParse(pParse,
-					   "DELETE FROM \"%s\" WHERE \"%s\"=%Q",
-					   zTab, zType, zName);
-		}
-	}
+    int i, j;
+    if(strcmp(zType, "idx") == 0)
+        for(i = 1, j = 1; i <= 2; i++, j++) {
+            char zTab[24];
+            sqlite3_snprintf(sizeof(zTab), zTab, "_sql_stat%d", i * j);
+            sqlite3NestedParse(pParse,
+                        "DELETE FROM \"%s\" WHERE (\"idx\"=%Q AND "
+                        "\"tbl\"=%Q)",
+                        zTab, idx_name, table_name);
+        }
+    else
+        for(i = 1, j = 1; i <= 2; i++, j++) {
+            char zTab[24];
+            sqlite3_snprintf(sizeof(zTab), zTab, "_sql_stat%d", i * j);
+            sqlite3NestedParse(pParse,
+                        "DELETE FROM \"%s\" WHERE \"tbl\"=%Q",
+                        zTab, table_name);
+        }
 }
 
 /*
@@ -2415,7 +2424,7 @@ sqlite3DropTable(Parse * pParse, SrcList * pName, int isView, int noErr)
 	 */
 
 	sqlite3BeginWriteOperation(pParse, 1);
-	sqlite3ClearStatTables(pParse, "tbl", pTab->zName);
+    sql_clear_stat_tables(pParse, "tbl", pTab->zName, NULL);
 	sqlite3FkDropTable(pParse, pName, pTab);
 	sqlite3CodeDropTable(pParse, pTab, isView);
 
@@ -3417,7 +3426,7 @@ sqlite3DropIndex(Parse * pParse, SrcList * pName, Token * pName2, int ifExists)
 	 * But firstly, delete statistics since schema
 	 * changes after DDL.
 	 */
-	sqlite3ClearStatTables(pParse, "idx", pIndex->zName);
+    sql_clear_stat_tables(pParse, "idx", pIndex->pTable->zName, pIndex->zName);
 	int record_reg = ++pParse->nMem;
 	int space_id_reg = ++pParse->nMem;
 	sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_PAGENO_TO_SPACEID(pIndex->tnum),
diff --git a/test/sql/sql-statN-index-drop.result b/test/sql/sql-statN-index-drop.result
new file mode 100644
index 0000000..c7e476f
--- /dev/null
+++ b/test/sql/sql-statN-index-drop.result
@@ -0,0 +1,127 @@
+test_run = require('test_run').new()
+---
+...
+-- Initializing some things.
+box.sql.execute("CREATE TABLE t1(id PRIMARY KEY, a);")
+---
+...
+box.sql.execute("CREATE TABLE t2(id PRIMARY KEY, a);")
+---
+...
+box.sql.execute("CREATE INDEX i1 ON t1(a);")
+---
+...
+box.sql.execute("CREATE INDEX i1 ON t2(a);")
+---
+...
+box.sql.execute("INSERT INTO t1 VALUES(1, 2);")
+---
+...
+box.sql.execute("INSERT INTO t2 VALUES(1, 2);")
+---
+...
+-- Analyze.
+box.sql.execute("ANALYZE;")
+---
+...
+-- Checking the data.
+box.sql.execute("SELECT * FROM \"_sql_stat4\";")
+---
+- - ['T1', 'I1', '1', '0', '0', !!binary kQI=]
+  - ['T1', 'T1', '1', '0', '0', !!binary kQE=]
+  - ['T2', 'I1', '1', '0', '0', !!binary kQI=]
+  - ['T2', 'T2', '1', '0', '0', !!binary kQE=]
+...
+box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+---
+- - ['T1', 'I1', '1 1']
+  - ['T1', 'T1', '1 1']
+  - ['T2', 'I1', '1 1']
+  - ['T2', 'T2', '1 1']
+...
+-- Dropping an index.
+box.sql.execute("DROP INDEX i1 ON t1;")
+---
+...
+-- Checking the DROP INDEX results.
+box.sql.execute("SELECT * FROM \"_sql_stat4\";")
+---
+- - ['T1', 'T1', '1', '0', '0', !!binary kQE=]
+  - ['T2', 'I1', '1', '0', '0', !!binary kQI=]
+  - ['T2', 'T2', '1', '0', '0', !!binary kQE=]
+...
+box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+---
+- - ['T1', 'T1', '1 1']
+  - ['T2', 'I1', '1 1']
+  - ['T2', 'T2', '1 1']
+...
+--Cleaning up.
+box.sql.execute("DROP TABLE t1;")
+---
+...
+box.sql.execute("DROP TABLE t2;")
+---
+...
+-- Same test but dropping an INDEX ON t2.
+box.sql.execute("CREATE TABLE t1(id PRIMARY KEY, a);")
+---
+...
+box.sql.execute("CREATE TABLE t2(id PRIMARY KEY, a);")
+---
+...
+box.sql.execute("CREATE INDEX i1 ON t1(a);")
+---
+...
+box.sql.execute("CREATE INDEX i1 ON t2(a);")
+---
+...
+box.sql.execute("INSERT INTO t1 VALUES(1, 2);")
+---
+...
+box.sql.execute("INSERT INTO t2 VALUES(1, 2);")
+---
+...
+-- Analyze.
+box.sql.execute("ANALYZE;")
+---
+...
+-- Checking the data.
+box.sql.execute("SELECT * FROM \"_sql_stat4\";")
+---
+- - ['T1', 'I1', '1', '0', '0', !!binary kQI=]
+  - ['T1', 'T1', '1', '0', '0', !!binary kQE=]
+  - ['T2', 'I1', '1', '0', '0', !!binary kQI=]
+  - ['T2', 'T2', '1', '0', '0', !!binary kQE=]
+...
+box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+---
+- - ['T1', 'I1', '1 1']
+  - ['T1', 'T1', '1 1']
+  - ['T2', 'I1', '1 1']
+  - ['T2', 'T2', '1 1']
+...
+-- Dropping an index.
+box.sql.execute("DROP INDEX i1 ON t2;")
+---
+...
+-- Checking the DROP INDEX results.
+box.sql.execute("SELECT * FROM \"_sql_stat4\";")
+---
+- - ['T1', 'I1', '1', '0', '0', !!binary kQI=]
+  - ['T1', 'T1', '1', '0', '0', !!binary kQE=]
+  - ['T2', 'T2', '1', '0', '0', !!binary kQE=]
+...
+box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+---
+- - ['T1', 'I1', '1 1']
+  - ['T1', 'T1', '1 1']
+  - ['T2', 'T2', '1 1']
+...
+--Cleaning up.
+box.sql.execute("DROP TABLE t1;")
+---
+...
+box.sql.execute("DROP TABLE t2;")
+---
+...
diff --git a/test/sql/sql-statN-index-drop.test.lua b/test/sql/sql-statN-index-drop.test.lua
new file mode 100644
index 0000000..bf4a752
--- /dev/null
+++ b/test/sql/sql-statN-index-drop.test.lua
@@ -0,0 +1,54 @@
+test_run = require('test_run').new()
+
+-- Initializing some things.
+box.sql.execute("CREATE TABLE t1(id PRIMARY KEY, a);")
+box.sql.execute("CREATE TABLE t2(id PRIMARY KEY, a);")
+box.sql.execute("CREATE INDEX i1 ON t1(a);")
+box.sql.execute("CREATE INDEX i1 ON t2(a);")
+box.sql.execute("INSERT INTO t1 VALUES(1, 2);")
+box.sql.execute("INSERT INTO t2 VALUES(1, 2);")
+
+-- Analyze.
+box.sql.execute("ANALYZE;")
+
+-- Checking the data.
+box.sql.execute("SELECT * FROM \"_sql_stat4\";")
+box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+
+-- Dropping an index.
+box.sql.execute("DROP INDEX i1 ON t1;")
+
+-- Checking the DROP INDEX results.
+box.sql.execute("SELECT * FROM \"_sql_stat4\";")
+box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+
+--Cleaning up.
+box.sql.execute("DROP TABLE t1;")
+box.sql.execute("DROP TABLE t2;")
+
+-- Same test but dropping an INDEX ON t2.
+
+box.sql.execute("CREATE TABLE t1(id PRIMARY KEY, a);")
+box.sql.execute("CREATE TABLE t2(id PRIMARY KEY, a);")
+box.sql.execute("CREATE INDEX i1 ON t1(a);")
+box.sql.execute("CREATE INDEX i1 ON t2(a);")
+box.sql.execute("INSERT INTO t1 VALUES(1, 2);")
+box.sql.execute("INSERT INTO t2 VALUES(1, 2);")
+
+-- Analyze.
+box.sql.execute("ANALYZE;")
+
+-- Checking the data.
+box.sql.execute("SELECT * FROM \"_sql_stat4\";")
+box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+
+-- Dropping an index.
+box.sql.execute("DROP INDEX i1 ON t2;")
+
+-- Checking the DROP INDEX results.
+box.sql.execute("SELECT * FROM \"_sql_stat4\";")
+box.sql.execute("SELECT * FROM \"_sql_stat1\";")
+
+--Cleaning up.
+box.sql.execute("DROP TABLE t1;")
+box.sql.execute("DROP TABLE t2;")
-- 
2.7.4

             reply	other threads:[~2018-04-03 21:39 UTC|newest]

Thread overview: 16+ messages / expand[flat|nested]  mbox.gz  Atom feed  top
2018-04-03 21:37 N.Tatunov [this message]
2018-04-04 14:06 ` [tarantool-patches] " n.pettik
2018-04-04 15:46   ` Hollow111
2018-04-04 16:11     ` n.pettik
2018-04-04 16:34       ` Hollow111
2018-04-05 18:01         ` n.pettik
     [not found]           ` <CAEi+_aq5oyeB0cbnxAXXjQqu=h+PCGaaZuLkk3p33yq371+Xog@mail.gmail.com>
2018-04-07  2:12             ` Hollow111
2018-04-09 12:16               ` n.pettik
2018-04-12  6:06                 ` Hollow111
2018-04-13  8:50                   ` n.pettik
2018-04-14  4:29                     ` Hollow111
2018-04-14  8:13                       ` n.pettik
2018-04-15  6:09                         ` Hollow111
2018-04-15  6:35                           ` Hollow111
2018-04-15 22:41                             ` n.pettik
2018-04-16 13:19                               ` Kirill Yukhin

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

  Avoid top-posting and favor interleaved quoting:
  https://en.wikipedia.org/wiki/Posting_style#Interleaved_style

* Reply using the --to, --cc, and --in-reply-to
  switches of git-send-email(1):

  git send-email \
    --in-reply-to=1522791436-8221-1-git-send-email-hollow653@gmail.com \
    --to=hollow653@gmail.com \
    --cc=korablev@tarantool.org \
    --cc=tarantool-patches@freelists.org \
    --subject='Re: [tarantool-patches] [PATCH 2/2] sql: statistics removal after dropping an index' \
    /path/to/YOUR_REPLY

  https://kernel.org/pub/software/scm/git/docs/git-send-email.html

* If your mail client supports setting the In-Reply-To header
  via mailto: links, try the mailto: link

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox