From: imeevma@tarantool.org To: korablev@tarantool.org Cc: tarantool-patches@freelists.org Subject: [tarantool-patches] [PATCH v4 2/4] sql: skip autoinc IDs generated inside SQL trigger Date: Wed, 17 Jul 2019 12:54:55 +0300 [thread overview] Message-ID: <c6ce7d0b81b5e93b87b2505ffbfda9e9c338b309.1563356272.git.imeevma@gmail.com> (raw) In-Reply-To: <cover.1563356272.git.imeevma@gmail.com> 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@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;')
next prev parent reply other threads:[~2019-07-17 9:55 UTC|newest] Thread overview: 13+ messages / expand[flat|nested] mbox.gz Atom feed top 2019-07-17 9:54 [tarantool-patches] [PATCH v4 0/4] sql: do not show IDs generated by trigger imeevma 2019-07-17 9:54 ` [tarantool-patches] [PATCH v4 1/4] sql: remove unnecessary AUTOINCREMENT ID generation imeevma 2019-07-17 9:54 ` imeevma [this message] 2019-07-17 16:58 ` [tarantool-patches] Re: [PATCH v4 2/4] sql: skip autoinc IDs generated inside SQL trigger n.pettik 2019-07-19 9:33 ` Mergen Imeev 2019-07-17 9:54 ` [tarantool-patches] [PATCH v4 3/4] sql: remove VDBE from TXN imeevma 2019-07-17 9:54 ` [tarantool-patches] [PATCH v4 4/4] sql: do not increase row-count if INSERT or REPLACE failed imeevma 2019-07-17 16:57 ` [tarantool-patches] " n.pettik 2019-07-17 18:08 ` [tarantool-patches] " Мерген Имеев 2019-07-19 9:36 ` Mergen Imeev 2019-07-22 10:48 ` n.pettik 2019-07-22 11:26 ` Mergen Imeev 2019-07-24 13:55 ` [tarantool-patches] Re: [PATCH v4 0/4] sql: do not show IDs generated by trigger 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=c6ce7d0b81b5e93b87b2505ffbfda9e9c338b309.1563356272.git.imeevma@gmail.com \ --to=imeevma@tarantool.org \ --cc=korablev@tarantool.org \ --cc=tarantool-patches@freelists.org \ --subject='Re: [tarantool-patches] [PATCH v4 2/4] sql: skip autoinc IDs generated inside SQL trigger' \ /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