[tarantool-patches] Re: [PATCH v1 1/1] sql: introduce TRUNCATE TABLE operation
Kirill Shcherbatov
kshcherbatov at tarantool.org
Thu Jul 19 13:00:15 MSK 2018
Hi! Thank you for you suggestions. I've reworked code.
===================================
We have introduced a new P2 argument for OP_Clear opcode
that calles box_truncate instead of tarantoolSqlite3ClearTable.
Thus, sql_table_delete_from called with is_truncate = true
cause such behavior.
Closes #2201.
@TarantoolBot document
Title: New TRUNCATE operation
Removes all rows from a table or specified partitions of a table,
without logging the individual row deletions.
TRUNCATE TABLE is similar to the DELETE statement with no WHERE
clause; however, TRUNCATE TABLE is faster and uses fewer system
resources.
It couldn't be used with system tables. It couldn't be called in
transaction.
The triggers on target table would not be called.
Example:
TRUNCATE TABLE t1;
---
extra/mkkeywordhash.c | 1 +
src/box/sql/build.c | 2 +-
src/box/sql/delete.c | 12 ++++++++----
src/box/sql/fkey.c | 2 +-
src/box/sql/parse.y | 11 ++++++++++-
src/box/sql/sqliteInt.h | 2 +-
src/box/sql/trigger.c | 4 ++--
src/box/sql/vdbe.c | 12 ++++++++++--
test/sql/delete.result | 28 ++++++++++++++++++++++++++++
test/sql/delete.test.lua | 17 +++++++++++++++++
10 files changed, 79 insertions(+), 12 deletions(-)
diff --git a/extra/mkkeywordhash.c b/extra/mkkeywordhash.c
index 1ec1538..705caf1 100644
--- a/extra/mkkeywordhash.c
+++ b/extra/mkkeywordhash.c
@@ -281,6 +281,7 @@ static Keyword aKeywordTable[] = {
{ "VARCHAR", "TK_ID", RESERVED, true },
{ "WHENEVER", "TK_STANDARD", RESERVED, true },
{ "WHILE", "TK_STANDARD", RESERVED, true },
+ { "TRUNCATE", "TK_TRUNCATE", ALWAYS, true },
};
/* Number of keywords */
diff --git a/src/box/sql/build.c b/src/box/sql/build.c
index a64d723..be4c860 100644
--- a/src/box/sql/build.c
+++ b/src/box/sql/build.c
@@ -1953,7 +1953,7 @@ vdbe_emit_stat_space_clear(struct Parse *parse, const char *stat_table_name,
* On memory allocation error sql_table delete_from
* releases memory for its own.
*/
- sql_table_delete_from(parse, src_list, where);
+ sql_table_delete_from(parse, src_list, where, false);
}
/**
diff --git a/src/box/sql/delete.c b/src/box/sql/delete.c
index f9d3498..f5b010e 100644
--- a/src/box/sql/delete.c
+++ b/src/box/sql/delete.c
@@ -73,7 +73,7 @@ sql_materialize_view(struct Parse *parse, const char *name, struct Expr *where,
void
sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
- struct Expr *where)
+ struct Expr *where, bool is_truncate)
{
struct sqlite3 *db = parse->db;
if (parse->nErr || db->mallocFailed)
@@ -183,10 +183,13 @@ sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
/* Special case: A DELETE without a WHERE clause deletes
* everything. It is easier just to erase the whole table.
*/
- if (where == NULL && !is_complex) {
+ if ((where == NULL && !is_complex) || (is_truncate && !is_view)) {
assert(!is_view);
-
- sqlite3VdbeAddOp1(v, OP_Clear, space_id);
+ if (!is_truncate) {
+ sqlite3VdbeAddOp1(v, OP_Clear, space_id);
+ } else {
+ sqlite3VdbeAddOp2(v, OP_Clear, space_id, true);
+ }
/* Do not start Tarantool's transaction in case of
* truncate optimization. This is workaround until
@@ -195,6 +198,7 @@ sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
*/
parse->initiateTTrans = false;
} else {
+ assert(!is_truncate || (is_truncate && !is_view));
/* Resolve the column names in the WHERE clause. */
struct NameContext nc;
memset(&nc, 0, sizeof(nc));
diff --git a/src/box/sql/fkey.c b/src/box/sql/fkey.c
index 6a91890..e67866c 100644
--- a/src/box/sql/fkey.c
+++ b/src/box/sql/fkey.c
@@ -784,7 +784,7 @@ sqlite3FkDropTable(Parse *parser, SrcList *name, Table *table)
/* Staring new transaction before DELETE FROM <tbl> */
sqlite3VdbeAddOp0(v, OP_TTransaction);
sql_table_delete_from(parser, sqlite3SrcListDup(parser->db, name, 0),
- NULL);
+ NULL, false);
parser->disableTriggers = 0;
/*
* If the DELETE has generated immediate foreign key
diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index 0c510f5..700f40d 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -722,7 +722,16 @@ cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W). {
sqlSubProgramsRemaining = SQL_MAX_COMPILING_TRIGGERS;
/* Instruct SQL to initate Tarantool's transaction. */
pParse->initiateTTrans = true;
- sql_table_delete_from(pParse,X,W);
+ sql_table_delete_from(pParse,X,W,false);
+}
+
+/////////////////////////// The TRUNCATE statement /////////////////////////////
+//
+cmd ::= TRUNCATE TABLE fullname(X). {
+ sqlSubProgramsRemaining = SQL_MAX_COMPILING_TRIGGERS;
+ /* Instruct SQL to initate Tarantool's transaction. */
+ pParse->initiateTTrans = true;
+ sql_table_delete_from(pParse, X, NULL,true);
}
%type where_opt {Expr*}
diff --git a/src/box/sql/sqliteInt.h b/src/box/sql/sqliteInt.h
index 54661cb..5eb4825 100644
--- a/src/box/sql/sqliteInt.h
+++ b/src/box/sql/sqliteInt.h
@@ -3703,7 +3703,7 @@ void sqlite3OpenTable(Parse *, int iCur, Table *, int);
*/
void
sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
- struct Expr *where);
+ struct Expr *where, bool is_truncate);
void sqlite3Update(Parse *, SrcList *, ExprList *, Expr *,
enum on_conflict_action);
diff --git a/src/box/sql/trigger.c b/src/box/sql/trigger.c
index ec0bc98..1949fdb 100644
--- a/src/box/sql/trigger.c
+++ b/src/box/sql/trigger.c
@@ -698,8 +698,8 @@ codeTriggerProgram(Parse * pParse, /* The parser context */
targetSrcList(pParse, pStep),
sqlite3ExprDup(db,
pStep->pWhere,
- 0)
- );
+ 0),
+ false);
break;
}
default:
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index f50e389..357beaa 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -39,6 +39,7 @@
* in this file for details. If in doubt, do not deviate from existing
* commenting and indentation practices when changing or adding code.
*/
+#include "box/box.h"
#include "box/txn.h"
#include "box/session.h"
#include "sqliteInt.h"
@@ -4566,8 +4567,9 @@ case OP_IdxGE: { /* jump */
break;
}
-/* Opcode: Clear P1 * * * *
+/* Opcode: Clear P1 P2 * * *
* Synopsis: space id = P1
+ * If P2 is not 0, use Truncate semantics.
*
* Delete all contents of the space, which space id is given
* in P1 argument. It is worth mentioning, that clearing routine
@@ -4579,7 +4581,13 @@ case OP_Clear: {
uint32_t space_id = pOp->p1;
struct space *space = space_by_id(space_id);
assert(space != NULL);
- rc = tarantoolSqlite3ClearTable(space);
+ rc = 0;
+ if (pOp->p2 > 0) {
+ if (box_truncate(space_id) != 0)
+ rc = SQL_TARANTOOL_ERROR;
+ } else {
+ rc = tarantoolSqlite3ClearTable(space);
+ }
if (rc) goto abort_due_to_error;
break;
}
diff --git a/test/sql/delete.result b/test/sql/delete.result
index c33079c..0ab8c6a 100644
--- a/test/sql/delete.result
+++ b/test/sql/delete.result
@@ -33,6 +33,34 @@ box.sql.execute("SELECT * FROM t1;");
---
- - [2, 4]
...
+--
+-- gh-2201: TRUNCATE TABLE operation
+--
+box.sql.execute("TRUNCATE TABLE \"_sql_stat1\";")
+---
+- error: Can't truncate a system space, space '_sql_stat1'
+...
+box.sql.execute("START TRANSACTION")
+---
+...
+box.sql.execute("TRUNCATE TABLE t1;")
+---
+- error: DDL does not support multi-statement transactions
+...
+box.sql.execute("ROLLBACK")
+---
+...
+box.sql.execute("SELECT * FROM t1;")
+---
+- - [2, 4]
+...
+box.sql.execute("TRUNCATE TABLE t1;")
+---
+...
+box.sql.execute("SELECT * FROM t1;")
+---
+- []
+...
-- Cleanup
box.sql.execute("DROP TABLE t1;");
---
diff --git a/test/sql/delete.test.lua b/test/sql/delete.test.lua
index 1721989..2a6427f 100644
--- a/test/sql/delete.test.lua
+++ b/test/sql/delete.test.lua
@@ -21,7 +21,24 @@ box.sql.execute("DELETE FROM t1 WHERE a=1;");
-- Verify
box.sql.execute("SELECT * FROM t1;");
+--
+-- gh-2201: TRUNCATE TABLE operation
+--
+box.sql.execute("TRUNCATE TABLE \"_sql_stat1\";")
+
+box.sql.execute("START TRANSACTION")
+box.sql.execute("TRUNCATE TABLE t1;")
+box.sql.execute("ROLLBACK")
+box.sql.execute("SELECT * FROM t1;")
+
+box.sql.execute("CREATE VIEW v1 AS SELECT * FROM t1;")
+box.sql.execute("TRUNCATE TABLE v1;")
+
+box.sql.execute("TRUNCATE TABLE t1;")
+box.sql.execute("SELECT * FROM t1;")
+
-- Cleanup
+box.sql.execute("DROP VIEW v1");
box.sql.execute("DROP TABLE t1;");
-- Debug
--
2.7.4
More information about the Tarantool-patches
mailing list