[tarantool-patches] [PATCH v4 2/4] sql: skip autoinc IDs generated inside SQL trigger
imeevma at tarantool.org
imeevma at tarantool.org
Wed Jul 17 12:54:55 MSK 2019
I changed the approach of the patch. My answers and new patch
below. This letter contains my answers to the earlier second and
fourth letters.
On 7/15/19 8:50 PM, n.pettik wrote:
>
> I’ve edited commit message:
>
> sql: skip autoinc IDs generated inside SQL trigger
>
> Currently, if an INSERT is executed inside SQL trigger and it results
> in generated autoincrement identifiers, ones will be displayed as a
> result of the statement. This is wrong, since we are not able to divide
> IDs obtained into those that belong to the table mentioned in the
> statement and those that do not belong to this table. This has been
> fixed by adding a new opcode OP_SaveAutoincValue, which follows each
> OP_IdxInsert when there's autoincrement field. On the other hand, we can
> avoid adding this opcode while producing VDBE instructions for triggers.
> OP_SaveAutoincValue retrieves and saves recently generated identifiers
> into the list, which is held in VDBE itself. Note that from now we don't
> save autoincremented value to VDBE right in sequence_next() - this
> operation is moved to OP_SaveAutoincValue. So that, VDBE can be removed
> from struct txn.
>
> For example:
> box.execute('CREATE TABLE t1 (i INT PRIMARY KEY AUTOINCREMENT);')
> box.execute('CREATE TABLE t2 (i INT PRIMARY KEY AUTOINCREMENT);')
> box.execute('CREATE TRIGGER r AFTER INSERT ON t1 FOR EACH ROW '..
> 'BEGIN INSERT INTO t2 VALUES (null); END')
> box.execute('INSERT INTO t2 VALUES (100);')
> box.execute('INSERT INTO t1 VALUES (NULL), (NULL), (NULL);')
>
> Result should be:
> ---
> - autoincrement_ids:
> - 1
> - 2
> - 3
> row_count: 3
> ...
>
> Closes #4188
>
> Closes #4188
Thanks. I changed it a bit according to new approach.
On 7/15/19 8:59 PM, n.pettik wrote:
> I’ve edited commit message:
>
> sql: omit auto-inc ID if INSERT OR IGNORE failed
>
> If INSERT statement is executed with IGNORE error action
> (i.e. INSERT OR IGNORE ...), it will return generated autoincrement
> identifiers. Even in case the insert has failed. For example:
>
> CREATE TABLE t (i INT PRIMARY KEY AUTOINCREMENT,
> a INT check (a > 0));
> INSERT OR IGNORE INTO t VALUES (null, 1), (null, -1), (null, 2);
>
> In this case only two identifiers should be returned, but before this
> patch all three are appeared. So, let's account only identifiers which
> are really inserted to the space. Also, row-count shows now
> number of successful insertions in case of INSERT OR IGNORE.
>
> Follow-up #4188
>
Thank you. I used it as an template for newly fourth patch.
>
> TBO I dislike your approach for several reasons.
> Firstly, it introduces another one auxiliary flag, which
> enlarges branching, complicates code reading etc.
> Secondly, it changes program counter in OP_Insert:
> generally speaking, I’d not consider implicit incrementation
> of program counter to be a good practice. You even don’t
> check that the next instruction is OP_SaveAutoinc…
>
> Could you spend some more time trying come up with
> another solution?
>
Thanks for your notices. Now I see that I was wrong. I fixed it by
changing the approach of the main issue.
> Also, I wonder why didn’t you split OP_SaveAutoinc into
> two opcode: OP_IsNull + OP_SaveAutoinc which would
> unconditionally save autroincremented value to the vdbe?
>
I think that is not needed now.
New patch:
>From c6ce7d0b81b5e93b87b2505ffbfda9e9c338b309 Mon Sep 17 00:00:00 2001
From: Mergen Imeev <imeevma at gmail.com>
Date: Tue, 28 May 2019 17:41:15 +0300
Subject: [PATCH] sql: skip autoinc IDs generated inside SQL trigger
Currently, if an INSERT is executed inside SQL trigger and it
results in generated autoincrement identifiers, ones will be
displayed as a result of the statement. This is wrong, since we
are not able to divide IDs obtained into those that belong to the
table mentioned in the statement and those that do not belong to
this table. This has been fixed by adding a new argument to
OP_IdxInsert. In case the argument is not 0, recently generated
identifier is retrieved and saved into the list, which is held in
VDBE itself. Note that from now we don't save autoincremented
value to VDBE right in sequence_next() - this operation is moved
to OP_IdxInsert. So that, VDBE can be removed from struct txn.
For example:
box.execute('CREATE TABLE t1 (i INT PRIMARY KEY AUTOINCREMENT);')
box.execute('CREATE TABLE t2 (i INT PRIMARY KEY AUTOINCREMENT);')
box.execute('CREATE TRIGGER r AFTER INSERT ON t1 FOR EACH ROW '..
'BEGIN INSERT INTO t2 VALUES (null); END')
box.execute('INSERT INTO t2 VALUES (100);')
box.execute('INSERT INTO t1 VALUES (NULL), (NULL), (NULL);')
Result should be:
---
- autoincrement_ids:
- 1
- 2
- 3
row_count: 3
...
Closes #4188
diff --git a/src/box/sequence.c b/src/box/sequence.c
index 2bf38f9..5996519 100644
--- a/src/box/sequence.c
+++ b/src/box/sequence.c
@@ -216,9 +216,6 @@ sequence_next(struct sequence *seq, int64_t *result)
new_data) == light_sequence_end)
return -1;
*result = def->start;
- if (txn_vdbe() != NULL &&
- vdbe_add_new_autoinc_id(txn_vdbe(), *result) != 0)
- return -1;
return 0;
}
old_data = light_sequence_get(&sequence_data_index, pos);
@@ -253,9 +250,6 @@ done:
new_data, &old_data) == light_sequence_end)
unreachable();
*result = value;
- if (txn_vdbe() != NULL &&
- vdbe_add_new_autoinc_id(txn_vdbe(), value) != 0)
- return -1;
return 0;
overflow:
if (!def->cycle) {
@@ -368,3 +362,16 @@ sequence_data_iterator_create(void)
light_sequence_iterator_freeze(&sequence_data_index, &iter->iter);
return &iter->base;
}
+
+int
+sequence_get_value(struct sequence *seq, int64_t *out_value)
+{
+ uint32_t key = seq->def->id;
+ uint32_t hash = sequence_hash(key);
+ uint32_t pos = light_sequence_find_key(&sequence_data_index, hash, key);
+ assert(pos != light_sequence_end);
+ struct sequence_data data = light_sequence_get(&sequence_data_index,
+ pos);
+ *out_value = data.value;
+ return 0;
+}
diff --git a/src/box/sequence.h b/src/box/sequence.h
index 9a745ad..b56236e 100644
--- a/src/box/sequence.h
+++ b/src/box/sequence.h
@@ -157,9 +157,6 @@ sequence_next(struct sequence *seq, int64_t *result);
int
access_check_sequence(struct sequence *seq);
-int
-vdbe_add_new_autoinc_id(struct Vdbe *vdbe, int64_t id);
-
/**
* Create an iterator over sequence data.
*
@@ -170,6 +167,16 @@ vdbe_add_new_autoinc_id(struct Vdbe *vdbe, int64_t id);
struct snapshot_iterator *
sequence_data_iterator_create(void);
+/**
+ * Get last element of given sequence.
+ *
+ * @param seq sequence to get value from.
+ * @param[out] out_value last element of sequence.
+ * @retval 0 on success, -1 on error.
+ */
+int
+sequence_get_value(struct sequence *seq, int64_t *out_value);
+
#if defined(__cplusplus)
} /* extern "C" */
diff --git a/src/box/sql/insert.c b/src/box/sql/insert.c
index d2b4e17..6a2a742 100644
--- a/src/box/sql/insert.c
+++ b/src/box/sql/insert.c
@@ -728,6 +728,11 @@ sqlInsert(Parse * pParse, /* Parser context */
}
}
+ assert(regData > 0);
+ int autoinc_reg = 0;
+ if (autoinc_fieldno < UINT32_MAX &&
+ pParse->triggered_space == NULL)
+ autoinc_reg = regData + autoinc_fieldno;
/*
* Generate code to check constraints and process
* final insertion.
@@ -737,7 +742,7 @@ sqlInsert(Parse * pParse, /* Parser context */
fk_constraint_emit_check(pParse, space, 0, regIns, 0);
vdbe_emit_insertion_completion(v, space, regIns + 1,
space->def->field_count,
- on_error);
+ on_error, autoinc_reg);
}
/* Update the count of rows that are inserted
@@ -971,14 +976,16 @@ process_index: ;
void
vdbe_emit_insertion_completion(struct Vdbe *v, struct space *space,
int raw_data_reg, uint32_t tuple_len,
- enum on_conflict_action on_conflict)
+ enum on_conflict_action on_conflict,
+ int autoinc_reg)
{
assert(v != NULL);
u16 pik_flags = OPFLAG_NCHANGE;
SET_CONFLICT_FLAG(pik_flags, on_conflict);
sqlVdbeAddOp3(v, OP_MakeRecord, raw_data_reg, tuple_len,
raw_data_reg + tuple_len);
- sqlVdbeAddOp1(v, OP_IdxInsert, raw_data_reg + tuple_len);
+ sqlVdbeAddOp3(v, OP_IdxInsert, raw_data_reg + tuple_len, 0,
+ autoinc_reg);
sqlVdbeChangeP4(v, -1, (char *)space, P4_SPACEPTR);
sqlVdbeChangeP5(v, pik_flags);
}
diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h
index 3b8c82d..654eebd 100644
--- a/src/box/sql/sqlInt.h
+++ b/src/box/sql/sqlInt.h
@@ -3506,7 +3506,8 @@ vdbe_emit_ck_constraint(struct Parse *parser, struct Expr *expr,
void
vdbe_emit_insertion_completion(struct Vdbe *v, struct space *space,
int raw_data_reg, uint32_t tuple_len,
- enum on_conflict_action on_conflict);
+ enum on_conflict_action on_conflict,
+ int autoinc_reg);
void
sql_set_multi_write(Parse *, bool);
diff --git a/src/box/sql/update.c b/src/box/sql/update.c
index d77bee0..311e60e 100644
--- a/src/box/sql/update.c
+++ b/src/box/sql/update.c
@@ -450,7 +450,7 @@ sqlUpdate(Parse * pParse, /* The parser context */
if (on_error == ON_CONFLICT_ACTION_REPLACE) {
vdbe_emit_insertion_completion(v, space, regNew,
space->def->field_count,
- on_error);
+ on_error, 0);
} else {
int key_reg;
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index 6bb7bc3..951303c 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -559,7 +559,7 @@ vdbe_autoinc_id_list(struct Vdbe *vdbe)
return &vdbe->autoinc_id_list;
}
-int
+static int
vdbe_add_new_autoinc_id(struct Vdbe *vdbe, int64_t id)
{
assert(vdbe != NULL);
@@ -4161,12 +4161,17 @@ case OP_SorterInsert: { /* in2 */
break;
}
-/* Opcode: IdxInsert P1 P2 * P4 P5
+/* Opcode: IdxInsert P1 P2 P3 P4 P5
* Synopsis: key=r[P1]
*
* @param P1 Index of a register with MessagePack data to insert.
* @param P2 If P4 is not set, then P2 is register containing pointer
* to space to insert into.
+ * @param P3 If not 0, than it is an index of a register that
+ * contains value that will be inserted into field with
+ * AUTOINCREMENT. If the value is NULL, than the newly
+ * generated autoincrement value will be saved to VDBE
+ * context.
* @param P4 Pointer to the struct space to insert to.
* @param P5 Flags. If P5 contains OPFLAG_NCHANGE, then VDBE
* accounts the change in a case of successful insertion in
@@ -4208,6 +4213,13 @@ case OP_IdxInsert: {
rc = tarantoolsqlEphemeralInsert(space, pIn2->z,
pIn2->z + pIn2->n);
}
+ if (rc == 0 && pOp->p3 > 0 && ((aMem[pOp->p3].flags) & MEM_Null) != 0) {
+ assert(space->sequence != NULL);
+ int64_t value;
+ if (sequence_get_value(space->sequence, &value) != 0)
+ goto abort_due_to_error;
+ vdbe_add_new_autoinc_id(p, value);
+ }
if (pOp->p5 & OPFLAG_OE_IGNORE) {
/* Ignore any kind of failes and do not raise error message */
diff --git a/test/sql/iproto.result b/test/sql/iproto.result
index 9639ba7..3580d6b 100644
--- a/test/sql/iproto.result
+++ b/test/sql/iproto.result
@@ -552,8 +552,6 @@ cn:execute('insert into test values (null, 1)')
---
- autoincrement_ids:
- 127
- - 1
- - 2
row_count: 1
...
box.execute('create table test3 (id int primary key autoincrement)')
diff --git a/test/sql/triggers.result b/test/sql/triggers.result
index 307b390..8ef9648 100644
--- a/test/sql/triggers.result
+++ b/test/sql/triggers.result
@@ -551,3 +551,117 @@ box.execute("DROP TABLE t1;")
---
- row_count: 1
...
+--
+-- gh-4188: Additional generated identifiers when INSERT is
+-- executed by triggers.
+--
+box.execute('CREATE TABLE t1 (i INT PRIMARY KEY AUTOINCREMENT);')
+---
+- row_count: 1
+...
+box.execute('CREATE TABLE t2 (i INT PRIMARY KEY AUTOINCREMENT);')
+---
+- row_count: 1
+...
+box.execute('CREATE TABLE t3 (i INT PRIMARY KEY AUTOINCREMENT);')
+---
+- row_count: 1
+...
+box.execute('CREATE TRIGGER r1 AFTER INSERT ON t2 FOR EACH ROW BEGIN INSERT INTO t1 VALUES (null); END')
+---
+- row_count: 1
+...
+box.execute('INSERT INTO t1 VALUES (100);')
+---
+- row_count: 1
+...
+box.execute('INSERT INTO t2 VALUES (NULL), (NULL), (NULL);')
+---
+- autoincrement_ids:
+ - 1
+ - 2
+ - 3
+ row_count: 3
+...
+box.execute('SELECT * FROM t1;')
+---
+- metadata:
+ - name: I
+ type: integer
+ rows:
+ - [100]
+ - [101]
+ - [102]
+ - [103]
+...
+box.execute('SELECT * FROM t2;')
+---
+- metadata:
+ - name: I
+ type: integer
+ rows:
+ - [1]
+ - [2]
+ - [3]
+...
+box.execute('CREATE TRIGGER r2 AFTER INSERT ON t3 FOR EACH ROW BEGIN INSERT INTO t2 VALUES (null); END')
+---
+- row_count: 1
+...
+box.execute('INSERT INTO t3 VALUES (NULL), (NULL), (NULL);')
+---
+- autoincrement_ids:
+ - 1
+ - 2
+ - 3
+ row_count: 3
+...
+box.execute('SELECT * FROM t1;')
+---
+- metadata:
+ - name: I
+ type: integer
+ rows:
+ - [100]
+ - [101]
+ - [102]
+ - [103]
+ - [104]
+ - [105]
+ - [106]
+...
+box.execute('SELECT * FROM t2;')
+---
+- metadata:
+ - name: I
+ type: integer
+ rows:
+ - [1]
+ - [2]
+ - [3]
+ - [4]
+ - [5]
+ - [6]
+...
+box.execute('SELECT * FROM t3;')
+---
+- metadata:
+ - name: I
+ type: integer
+ rows:
+ - [1]
+ - [2]
+ - [3]
+...
+box.execute('DROP TABLE t1;')
+---
+- row_count: 1
+...
+box.execute('DROP TABLE t2;')
+---
+- row_count: 1
+...
+box.execute('DROP TABLE t3;')
+---
+- row_count: 1
+...
diff --git a/test/sql/triggers.test.lua b/test/sql/triggers.test.lua
index a056e79..ee77192 100644
--- a/test/sql/triggers.test.lua
+++ b/test/sql/triggers.test.lua
@@ -191,3 +191,27 @@ box.execute([[CREATE TRIGGER r1 AFTER INSERT ON t1 FOR EACH ROW BEGIN SELECT 1;
box.session.su('admin')
box.schema.user.drop('tester')
box.execute("DROP TABLE t1;")
+
+--
+-- gh-4188: Additional generated identifiers when INSERT is
+-- executed by triggers.
+--
+box.execute('CREATE TABLE t1 (i INT PRIMARY KEY AUTOINCREMENT);')
+box.execute('CREATE TABLE t2 (i INT PRIMARY KEY AUTOINCREMENT);')
+box.execute('CREATE TABLE t3 (i INT PRIMARY KEY AUTOINCREMENT);')
+
+box.execute('CREATE TRIGGER r1 AFTER INSERT ON t2 FOR EACH ROW BEGIN INSERT INTO t1 VALUES (null); END')
+box.execute('INSERT INTO t1 VALUES (100);')
+box.execute('INSERT INTO t2 VALUES (NULL), (NULL), (NULL);')
+box.execute('SELECT * FROM t1;')
+box.execute('SELECT * FROM t2;')
+
+box.execute('CREATE TRIGGER r2 AFTER INSERT ON t3 FOR EACH ROW BEGIN INSERT INTO t2 VALUES (null); END')
+box.execute('INSERT INTO t3 VALUES (NULL), (NULL), (NULL);')
+box.execute('SELECT * FROM t1;')
+box.execute('SELECT * FROM t2;')
+box.execute('SELECT * FROM t3;')
+
+box.execute('DROP TABLE t1;')
+box.execute('DROP TABLE t2;')
+box.execute('DROP TABLE t3;')
More information about the Tarantool-patches
mailing list