From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 705A9271DB for ; Thu, 19 Jul 2018 06:00:19 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id JiA-5VqiImDL for ; Thu, 19 Jul 2018 06:00:19 -0400 (EDT) Received: from smtpng1.m.smailru.net (smtpng1.m.smailru.net [94.100.181.251]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 78DFA27178 for ; Thu, 19 Jul 2018 06:00:18 -0400 (EDT) Subject: [tarantool-patches] Re: [PATCH v1 1/1] sql: introduce TRUNCATE TABLE operation References: <70326bb69fbe25215df79e2d5e01043f93ff7c5a.1531902074.git.kshcherbatov@tarantool.org> <797d52e5-1387-dd3d-1fb2-0fafbbbdcf56@tarantool.org> <606f657b-26df-35b9-ddf1-d5bae5654d82@tarantool.org> From: Kirill Shcherbatov Message-ID: Date: Thu, 19 Jul 2018 13:00:15 +0300 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset=utf-8 Content-Language: en-US Content-Transfer-Encoding: 7bit Sender: tarantool-patches-bounce@freelists.org Errors-to: tarantool-patches-bounce@freelists.org Reply-To: tarantool-patches@freelists.org List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: tarantool-patches List-subscribe: List-owner: List-post: List-archive: To: tarantool-patches@freelists.org, Nikita Pettik 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 */ 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