[tarantool-patches] [PATCH 2/4] sql: allow transitive Lua <-> SQL transactions
Nikita Pettik
korablev at tarantool.org
Thu May 3 21:49:23 MSK 2018
This patch makes possible to start transaction in Lua and continue
operations in SQL as well, and vice versa. Previously, such transactions
result in assertion fault. To support them, it is required to hold deferred
foreign keys contrains as attributes of transaction, not particular VDBE.
Thus, deferred foreign keys counters have been completely removed from
VDBE and transfered to sql_txn struct. In its turn, if there is at least
one deferred foreign key violation, error will be raised and prevent
from commiting. Note that in this case rollback doesn't happen:
transaction becomes open untill all deferred FK violations are resolved
OR explicit ROLLBACK statement is used.
Also, 'PRAGMA defer_foreign_keys' has been slightly changed: now it is not
automatically turned off after trasaction's rollback or commit. It
can be turned off by explicit PRAGMA statement only. It was made owing
to the fact that execution of PRAGMA statement occurs in auto-commit
mode, so it ends with COMMIT. Hence, it turns off right after turning on
(outside the transaction).
Closes #3237
---
src/box/errcode.h | 1 +
src/box/sql/fkey.c | 66 ++++++----------
src/box/sql/main.c | 5 --
src/box/sql/pragma.c | 3 -
src/box/sql/sqliteInt.h | 2 -
src/box/sql/status.c | 3 +-
src/box/sql/vdbe.c | 25 +++---
src/box/sql/vdbeInt.h | 26 +------
src/box/sql/vdbeapi.c | 3 -
src/box/sql/vdbeaux.c | 53 +++++++------
src/box/txn.c | 18 ++++-
src/box/txn.h | 22 +++++-
test/box/misc.result | 1 +
test/sql/transitive-transactions.result | 124 ++++++++++++++++++++++++++++++
test/sql/transitive-transactions.test.lua | 67 ++++++++++++++++
15 files changed, 298 insertions(+), 121 deletions(-)
create mode 100644 test/sql/transitive-transactions.result
create mode 100644 test/sql/transitive-transactions.test.lua
diff --git a/src/box/errcode.h b/src/box/errcode.h
index e5d882df8..ba5288059 100644
--- a/src/box/errcode.h
+++ b/src/box/errcode.h
@@ -214,6 +214,7 @@ struct errcode_record {
/*159 */_(ER_SQL_BIND_NOT_FOUND, "Parameter %s was not found in the statement") \
/*160 */_(ER_ACTION_MISMATCH, "Field %d contains %s on conflict action, but %s in index parts") \
/*161 */_(ER_VIEW_MISSING_SQL, "Space declared as a view must have SQL statement") \
+ /*162 */_(ER_FOREIGN_KEY_CONSTRAINT, "Can not commit transaction: deferred foreign keys violations are not resolved") \
/*
* !IMPORTANT! Please follow instructions at start of the file
diff --git a/src/box/sql/fkey.c b/src/box/sql/fkey.c
index fb9a3101a..55edf6ba5 100644
--- a/src/box/sql/fkey.c
+++ b/src/box/sql/fkey.c
@@ -748,23 +748,19 @@ fkTriggerDelete(sqlite3 * dbMem, Trigger * p)
}
}
-/*
- * This function is called to generate code that runs when table pTab is
- * being dropped from the database. The SrcList passed as the second argument
- * to this function contains a single entry guaranteed to resolve to
- * table pTab.
- *
- * Normally, no code is required. However, if either
- *
- * (a) The table is the parent table of a FK constraint, or
- * (b) The table is the child table of a deferred FK constraint and it is
- * determined at runtime that there are outstanding deferred FK
- * constraint violations in the database,
- *
- * then the equivalent of "DELETE FROM <tbl>" is executed in a single transaction
- * before dropping the table from the database. If any FK violations occur,
- * rollback transaction and halt VDBE. Triggers are disabled while running this
- * DELETE, but foreign key actions are not.
+/**
+ * This function is called to generate code that runs when table
+ * pTab is being dropped from the database. The SrcList passed as
+ * the second argument to this function contains a single entry
+ * guaranteed to resolve to table pTab.
+ *
+ * Normally, no code is required. However, if the table is
+ * parent table of a FK constraint, then the equivalent
+ * of "DELETE FROM <tbl>" is executed in a single transaction
+ * before dropping the table from the database. If any FK
+ * violations occur, rollback transaction and halt VDBE. Triggers
+ * are disabled while running this DELETE, but foreign key
+ * actions are not.
*/
void
sqlite3FkDropTable(Parse * pParse, SrcList * pName, Table * pTab)
@@ -774,31 +770,17 @@ sqlite3FkDropTable(Parse * pParse, SrcList * pName, Table * pTab)
if ((user_session->sql_flags & SQLITE_ForeignKeys) &&
!space_is_view(pTab)) {
- int iSkip = 0;
Vdbe *v = sqlite3GetVdbe(pParse);
-
- assert(v); /* VDBE has already been allocated */
- if (sqlite3FkReferences(pTab) == 0) {
- /* Search for a deferred foreign key constraint for which this table
- * is the child table. If one cannot be found, return without
- * generating any VDBE code. If one can be found, then jump over
- * the entire DELETE if there are no outstanding deferred constraints
- * when this statement is run.
- */
- FKey *p;
- for (p = pTab->pFKey; p; p = p->pNextFrom) {
- if (p->isDeferred
- || (user_session->
- sql_flags & SQLITE_DeferFKs))
- break;
- }
- if (!p)
+ assert(v != NULL);
+ /*
+ * Search for a foreign key constraint for which
+ * this table is the parent table. If one can't be
+ * found, return without generating any VDBE code,
+ * since it makes no sense starting transaction
+ * to test FK violations.
+ */
+ if (sqlite3FkReferences(pTab) == NULL)
return;
- iSkip = sqlite3VdbeMakeLabel(v);
- sqlite3VdbeAddOp2(v, OP_FkIfZero, 1, iSkip);
- VdbeCoverage(v);
- }
-
pParse->disableTriggers = 1;
/* Staring new transaction before DELETE FROM <tbl> */
sqlite3VdbeAddOp0(v, OP_TTransaction);
@@ -813,10 +795,6 @@ sqlite3FkDropTable(Parse * pParse, SrcList * pName, Table * pTab)
* commit changes.
*/
sqlite3VdbeAddOp0(v, OP_FkCheckCommit);
-
- if (iSkip) {
- sqlite3VdbeResolveLabel(v, iSkip);
- }
}
}
diff --git a/src/box/sql/main.c b/src/box/sql/main.c
index 8775ef3ad..0acf7bc64 100644
--- a/src/box/sql/main.c
+++ b/src/box/sql/main.c
@@ -770,11 +770,6 @@ sqlite3RollbackAll(Vdbe * pVdbe, int tripCode)
assert((user_session->sql_flags & SQLITE_InternChanges) == 0
|| db->init.busy == 1);
- /* Any deferred constraint violations have now been resolved. */
- pVdbe->nDeferredCons = 0;
- pVdbe->nDeferredImmCons = 0;
- user_session->sql_flags &= ~SQLITE_DeferFKs;
-
/* If one has been configured, invoke the rollback-hook callback */
if (db->xRollbackCallback && (!pVdbe->auto_commit)) {
db->xRollbackCallback(db->pRollbackArg);
diff --git a/src/box/sql/pragma.c b/src/box/sql/pragma.c
index e41f69b02..73cd34cc0 100644
--- a/src/box/sql/pragma.c
+++ b/src/box/sql/pragma.c
@@ -320,9 +320,6 @@ sqlite3Pragma(Parse * pParse, Token * pId, /* First part of [schema.]id field */
user_session->sql_flags |= mask;
} else {
user_session->sql_flags &= ~mask;
- if (mask == SQLITE_DeferFKs) {
- v->nDeferredImmCons = 0;
- }
}
/* Many of the flag-pragmas modify the code
diff --git a/src/box/sql/sqliteInt.h b/src/box/sql/sqliteInt.h
index 6bfe1352d..27d421b7e 100644
--- a/src/box/sql/sqliteInt.h
+++ b/src/box/sql/sqliteInt.h
@@ -1849,8 +1849,6 @@ struct FuncDestructor {
struct Savepoint {
box_txn_savepoint_t *tnt_savepoint; /* Tarantool's savepoint struct */
char *zName; /* Savepoint name (nul-terminated) */
- i64 nDeferredCons; /* Number of deferred fk violations */
- i64 nDeferredImmCons; /* Number of deferred imm fk. */
Savepoint *pNext; /* Parent savepoint (if any) */
};
diff --git a/src/box/sql/status.c b/src/box/sql/status.c
index 8742bbf8b..84f07cc68 100644
--- a/src/box/sql/status.c
+++ b/src/box/sql/status.c
@@ -333,8 +333,7 @@ sqlite3_db_status(sqlite3 * db, /* The database connection whose status is desir
break;
}
const struct sql_txn *psql_txn = ptxn->psql_txn;
- *pCurrent = psql_txn->nDeferredImmConsSave > 0 ||
- psql_txn->nDeferredConsSave > 0;
+ *pCurrent = psql_txn->fk_deferred_count > 0;
break;
}
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index cf2b769c7..1192fc399 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -1029,7 +1029,6 @@ case OP_Halt: {
p->rc = SQLITE_BUSY;
} else {
assert(rc==SQLITE_OK || (p->rc&0xff)==SQLITE_CONSTRAINT);
- assert(rc==SQLITE_OK || p->nDeferredCons>0 || p->nDeferredImmCons>0);
rc = p->rc ? SQLITE_ERROR : SQLITE_DONE;
}
goto vdbe_return;
@@ -2950,8 +2949,8 @@ case OP_Savepoint: {
assert(pSavepoint == psql_txn->pSavepoint);
psql_txn->pSavepoint = pSavepoint->pNext;
} else {
- p->nDeferredCons = pSavepoint->nDeferredCons;
- p->nDeferredImmCons = pSavepoint->nDeferredImmCons;
+ p->psql_txn->fk_deferred_count =
+ pSavepoint->tnt_savepoint->fk_deferred_count;
}
}
}
@@ -5064,10 +5063,10 @@ case OP_Param: { /* out2 */
* statement counter is incremented (immediate foreign key constraints).
*/
case OP_FkCounter: {
- if (user_session->sql_flags & SQLITE_DeferFKs) {
- p->nDeferredImmCons += pOp->p2;
- } else if (pOp->p1) {
- p->nDeferredCons += pOp->p2;
+ if ((user_session->sql_flags & SQLITE_DeferFKs || pOp->p1 != 0) &&
+ !p->auto_commit) {
+ assert(p->psql_txn != NULL);
+ p->psql_txn->fk_deferred_count += pOp->p2;
} else {
p->nFkConstraint += pOp->p2;
}
@@ -5087,12 +5086,14 @@ case OP_FkCounter: {
* (immediate foreign key constraint violations).
*/
case OP_FkIfZero: { /* jump */
- if (pOp->p1) {
- VdbeBranchTaken(db->nDeferredCons == 0 && db->nDeferredImmCons == 0, 2);
- if (p->nDeferredCons==0 && p->nDeferredImmCons==0) goto jump_to_p2;
+ if ((user_session->sql_flags & SQLITE_DeferFKs || pOp->p1) &&
+ !p->auto_commit) {
+ assert(p->psql_txn != NULL);
+ if (p->psql_txn->fk_deferred_count == 0)
+ goto jump_to_p2;
} else {
- VdbeBranchTaken(p->nFkConstraint == 0 && db->nDeferredImmCons == 0, 2);
- if (p->nFkConstraint==0 && p->nDeferredImmCons==0) goto jump_to_p2;
+ if (p->nFkConstraint == 0)
+ goto jump_to_p2;
}
break;
}
diff --git a/src/box/sql/vdbeInt.h b/src/box/sql/vdbeInt.h
index 4b1767e24..3a907cd93 100644
--- a/src/box/sql/vdbeInt.h
+++ b/src/box/sql/vdbeInt.h
@@ -332,19 +332,6 @@ struct ScanStatus {
char *zName; /* Name of table or index */
};
-
-struct sql_txn {
- Savepoint *pSavepoint; /* List of active savepoints */
- /*
- * This variables transfer deferred constraints from one
- * VDBE to the next in the same transaction.
- * We have to do it this way because some VDBE execute ddl and do not
- * have a transaction which disallows to always store this vars here.
- */
- i64 nDeferredConsSave;
- i64 nDeferredImmConsSave;
-};
-
/*
* An instance of the virtual machine. This structure contains the complete
* state of the virtual machine.
@@ -367,8 +354,6 @@ struct Vdbe {
int iStatement; /* Statement number (or 0 if has not opened stmt) */
i64 iCurrentTime; /* Value of julianday('now') for this statement */
i64 nFkConstraint; /* Number of imm. FK constraints this VM */
- i64 nStmtDefCons; /* Number of def. constraints when stmt started */
- i64 nStmtDefImmCons; /* Number of def. imm constraints when stmt started */
uint32_t schema_ver; /* Schema version at the moment of VDBE creation. */
/*
@@ -379,17 +364,10 @@ struct Vdbe {
* ignoreRaised variable helps to track such situations
*/
u8 ignoreRaised; /* Flag for ON CONFLICT IGNORE for triggers */
-
- struct sql_txn *psql_txn; /* Data related to current transaction */
+ /** Data related to current transaction. */
+ struct sql_txn *psql_txn;
/** The auto-commit flag. */
bool auto_commit;
- /*
- * `nDeferredCons` and `nDeferredImmCons` are stored in vdbe during
- * vdbe execution and moved to sql_txn when it needs to be saved until
- * execution of the next vdbe in the same transaction.
- */
- i64 nDeferredCons; /* Number of deferred fk violations */
- i64 nDeferredImmCons; /* Number of deferred imm fk. */
/* When allocating a new Vdbe object, all of the fields below should be
* initialized to zero or NULL
diff --git a/src/box/sql/vdbeapi.c b/src/box/sql/vdbeapi.c
index d8cdefa7c..32af463f6 100644
--- a/src/box/sql/vdbeapi.c
+++ b/src/box/sql/vdbeapi.c
@@ -560,9 +560,6 @@ sqlite3Step(Vdbe * p)
db->u1.isInterrupted = 0;
}
- assert(box_txn() ||
- (p->nDeferredCons == 0 && p->nDeferredImmCons == 0));
-
#ifndef SQLITE_OMIT_TRACE
if ((db->xProfile || (db->mTrace & SQLITE_TRACE_PROFILE) != 0)
&& !db->init.busy && p->zSql) {
diff --git a/src/box/sql/vdbeaux.c b/src/box/sql/vdbeaux.c
index 0bd0d455b..991ef9bec 100644
--- a/src/box/sql/vdbeaux.c
+++ b/src/box/sql/vdbeaux.c
@@ -65,16 +65,31 @@ sqlite3VdbeCreate(Parse * pParse)
db->pVdbe = p;
p->magic = VDBE_MAGIC_INIT;
p->pParse = pParse;
- p->auto_commit = !box_txn();
p->schema_ver = box_schema_version();
- if (!p->auto_commit) {
- p->psql_txn = in_txn()->psql_txn;
- p->nDeferredCons = p->psql_txn->nDeferredConsSave;
- p->nDeferredImmCons = p->psql_txn->nDeferredImmConsSave;
- } else{
+ struct txn *txn = in_txn();
+ p->auto_commit = txn == NULL ? true : false;
+ if (txn != NULL) {
+ /*
+ * If transaction has been started in Lua, then
+ * sql_txn is NULL. On the other hand, it is not
+ * critical, since in Lua it is impossible to
+ * check FK violations, at least now.
+ */
+ if (txn->psql_txn == NULL) {
+ txn->psql_txn = region_alloc_object(&fiber()->gc,
+ struct sql_txn);
+ if (txn->psql_txn == NULL) {
+ sqlite3DbFree(db, p);
+ diag_set(OutOfMemory, sizeof(struct sql_txn),
+ "region", "struct sql_txn");
+ return NULL;
+ }
+ txn->psql_txn->pSavepoint = NULL;
+ txn->psql_txn->fk_deferred_count = 0;
+ }
+ p->psql_txn = txn->psql_txn;
+ } else {
p->psql_txn = NULL;
- p->nDeferredCons = 0;
- p->nDeferredImmCons = 0;
}
assert(pParse->aLabel == 0);
assert(pParse->nLabel == 0);
@@ -2439,11 +2454,8 @@ sqlite3VdbeCloseStatement(Vdbe * p, int eOp)
/*
* If we have an anonymous transaction opened -> perform eOp.
*/
- if (savepoint && eOp == SAVEPOINT_ROLLBACK) {
+ if (savepoint && eOp == SAVEPOINT_ROLLBACK)
rc = box_txn_rollback_to_savepoint(savepoint->tnt_savepoint);
- p->nDeferredCons = savepoint->nDeferredCons;
- p->nDeferredImmCons = savepoint->nDeferredImmCons;
- }
p->anonymous_savepoint = NULL;
return rc;
}
@@ -2462,9 +2474,9 @@ sqlite3VdbeCloseStatement(Vdbe * p, int eOp)
int
sqlite3VdbeCheckFk(Vdbe * p, int deferred)
{
- if ((deferred && (p->nDeferredCons + p->nDeferredImmCons) > 0)
- || (!deferred && p->nFkConstraint > 0)
- ) {
+ if ((deferred && p->psql_txn != NULL &&
+ p->psql_txn->fk_deferred_count > 0) ||
+ (!deferred && p->nFkConstraint > 0)) {
p->rc = SQLITE_CONSTRAINT_FOREIGNKEY;
p->errorAction = ON_CONFLICT_ACTION_ABORT;
sqlite3VdbeError(p, "FOREIGN KEY constraint failed");
@@ -2516,9 +2528,7 @@ sql_savepoint(Vdbe *p, const char *zName)
if (zName) {
pNew->zName = (char *)&pNew[1];
memcpy(pNew->zName, zName, nName);
- }
- pNew->nDeferredCons = p->nDeferredCons;
- pNew->nDeferredImmCons = p->nDeferredImmCons;
+ };
return pNew;
}
@@ -2540,7 +2550,6 @@ sqlite3VdbeHalt(Vdbe * p)
{
int rc; /* Used to store transient return codes */
sqlite3 *db = p->db;
- struct session *user_session = current_session();
/* This function contains the logic that determines if a statement or
* transaction will be committed or rolled back as a result of the
@@ -2657,10 +2666,6 @@ sqlite3VdbeHalt(Vdbe * p)
sqlite3RollbackAll(p, SQLITE_OK);
p->nChange = 0;
} else {
- p->nDeferredCons = 0;
- p->nDeferredImmCons = 0;
- user_session->sql_flags &=
- ~SQLITE_DeferFKs;
sqlite3CommitInternalChanges();
}
} else {
@@ -2739,7 +2744,7 @@ sqlite3VdbeHalt(Vdbe * p)
fiber_gc();
assert(db->nVdbeActive > 0 || box_txn() ||
- p->anonymous_savepoint == NULL);
+ p->anonymous_savepoint == NULL);
return (p->rc == SQLITE_BUSY ? SQLITE_BUSY : SQLITE_OK);
}
diff --git a/src/box/txn.c b/src/box/txn.c
index c5aaa8e0b..a831472bd 100644
--- a/src/box/txn.c
+++ b/src/box/txn.c
@@ -289,7 +289,18 @@ txn_commit(struct txn *txn)
assert(txn == in_txn());
assert(stailq_empty(&txn->stmts) || txn->engine);
-
+ /*
+ * If transaction has been started in SQL, foreign key
+ * constraints must not be violated. If not so, don't
+ * rollback transaction, just prevent from commiting.
+ */
+ if (txn->psql_txn != NULL) {
+ struct sql_txn *sql_txn = txn->psql_txn;
+ if (sql_txn->fk_deferred_count != 0) {
+ diag_set(ClientError, ER_FOREIGN_KEY_CONSTRAINT);
+ return -1;
+ }
+ }
/* Do transaction conflict resolving */
if (txn->engine) {
if (engine_prepare(txn->engine, txn) != 0)
@@ -458,6 +469,8 @@ box_txn_savepoint()
}
svp->stmt = stailq_last(&txn->stmts);
svp->in_sub_stmt = txn->in_sub_stmt;
+ if (txn->psql_txn != NULL)
+ svp->fk_deferred_count = txn->psql_txn->fk_deferred_count;
return svp;
}
@@ -484,5 +497,8 @@ box_txn_rollback_to_savepoint(box_txn_savepoint_t *svp)
return -1;
}
txn_rollback_to_svp(txn, svp->stmt);
+ if (txn->psql_txn != NULL) {
+ txn->psql_txn->fk_deferred_count = svp->fk_deferred_count;
+ }
return 0;
}
diff --git a/src/box/txn.h b/src/box/txn.h
index 9aedd9452..00cf34cdd 100644
--- a/src/box/txn.h
+++ b/src/box/txn.h
@@ -43,7 +43,7 @@ extern "C" {
/** box statistics */
extern struct rmean *rmean_box;
-struct sql_txn;
+struct Savepoint;
struct engine;
struct space;
@@ -95,10 +95,30 @@ struct txn_savepoint {
* an empty transaction.
*/
struct stailq_entry *stmt;
+ /**
+ * Each foreign key constraint is classified as either
+ * immediate (by default) or deferred. In autocommit mode
+ * they mean the same. Inside separate transaction,
+ * deferred FK constraints are not checked until the
+ * transaction tries to commit. For as long as
+ * a transaction is open, it is allowed to exist in a
+ * state violating any number of deferred FK constraints.
+ */
+ uint32_t fk_deferred_count;
};
extern double too_long_threshold;
+struct sql_txn {
+ /** List of active SQL savepoints. */
+ struct Savepoint *pSavepoint;
+ /**
+ * This variables transfer deferred constraints from one
+ * VDBE to the next in the same transaction.
+ */
+ uint32_t fk_deferred_count;
+};
+
struct txn {
/**
* A sequentially growing transaction id, assigned when
diff --git a/test/box/misc.result b/test/box/misc.result
index 4e437d88f..840abbb66 100644
--- a/test/box/misc.result
+++ b/test/box/misc.result
@@ -389,6 +389,7 @@ t;
- 'box.error.LOCAL_INSTANCE_ID_IS_READ_ONLY : 128'
- 'box.error.FUNCTION_EXISTS : 52'
- 'box.error.UPDATE_ARG_TYPE : 26'
+ - 'box.error.FOREIGN_KEY_CONSTRAINT : 162'
- 'box.error.CROSS_ENGINE_TRANSACTION : 81'
- 'box.error.ACTION_MISMATCH : 160'
- 'box.error.FORMAT_MISMATCH_INDEX_PART : 27'
diff --git a/test/sql/transitive-transactions.result b/test/sql/transitive-transactions.result
new file mode 100644
index 000000000..d282a5dfa
--- /dev/null
+++ b/test/sql/transitive-transactions.result
@@ -0,0 +1,124 @@
+test_run = require('test_run').new()
+---
+...
+test_run:cmd("setopt delimiter ';'")
+---
+- true
+...
+-- These tests are aimed at checking transitive transactions
+-- between SQL and Lua. In particular, make sure that deferred foreign keys
+-- violations are passed correctly.
+--
+-- box.cfg()
+box.begin() box.sql.execute('COMMIT');
+---
+...
+box.begin() box.sql.execute('ROLLBACK');
+---
+...
+box.sql.execute('BEGIN;') box.commit();
+---
+...
+box.sql.execute('BEGIN;') box.rollback();
+---
+...
+box.sql.execute('pragma foreign_keys = 1;');
+---
+...
+box.sql.execute('CREATE TABLE child(id INT PRIMARY KEY, x REFERENCES parent(y) DEFERRABLE INITIALLY DEFERRED);');
+---
+...
+box.sql.execute('CREATE TABLE parent(id INT PRIMARY KEY, y INT UNIQUE);');
+---
+...
+fk_violation_1 = function()
+ box.begin()
+ box.sql.execute('INSERT INTO child VALUES (1, 1);')
+ box.sql.execute('COMMIT;')
+end;
+---
+...
+fk_violation_1() box.sql.execute('ROLLBACK;')
+box.space.CHILD:select();
+---
+- error: 'Can not commit transaction: deferred foreign keys violations are not resolved'
+...
+fk_violation_2 = function()
+ box.sql.execute('BEGIN;')
+ box.sql.execute('INSERT INTO child VALUES (1, 1);')
+ box.commit()
+end;
+---
+...
+fk_violation_2() box.rollback();
+---
+- error: 'Can not commit transaction: deferred foreign keys violations are not resolved'
+...
+box.space.CHILD:select();
+---
+- []
+...
+fk_violation_3 = function()
+ box.begin()
+ box.sql.execute('INSERT INTO child VALUES (1, 1);')
+ box.sql.execute('INSERT INTO parent VALUES (1, 1);')
+ box.commit()
+end;
+---
+...
+fk_violation_3();
+---
+...
+box.space.CHILD:select();
+---
+- - [1, 1]
+...
+box.space.PARENT:select();
+---
+- - [1, 1]
+...
+-- Make sure that 'PRAGMA defer_foreign_keys' works.
+--
+box.sql.execute('DROP TABLE child;')
+box.sql.execute('CREATE TABLE child(id INT PRIMARY KEY, x REFERENCES parent(y))')
+
+fk_defer = function()
+ box.begin()
+ box.sql.execute('INSERT INTO child VALUES (1, 2);')
+ box.sql.execute('INSERT INTO parent VALUES (2, 2);')
+ box.commit()
+end;
+---
+...
+fk_defer();
+---
+- error: FOREIGN KEY constraint failed
+...
+box.space.CHILD:select();
+---
+- []
+...
+box.space.PARENT:select();
+---
+- - [1, 1]
+...
+box.sql.execute('PRAGMA defer_foreign_keys = 1;')
+fk_defer();
+---
+...
+box.space.CHILD:select();
+---
+- - [1, 2]
+...
+box.space.PARENT:select();
+---
+- - [1, 1]
+ - [2, 2]
+...
+-- Cleanup
+box.sql.execute('DROP TABLE child;');
+---
+...
+box.sql.execute('DROP TABLE parent;');
+---
+...
diff --git a/test/sql/transitive-transactions.test.lua b/test/sql/transitive-transactions.test.lua
new file mode 100644
index 000000000..d6f63a660
--- /dev/null
+++ b/test/sql/transitive-transactions.test.lua
@@ -0,0 +1,67 @@
+test_run = require('test_run').new()
+test_run:cmd("setopt delimiter ';'")
+
+-- These tests are aimed at checking transitive transactions
+-- between SQL and Lua. In particular, make sure that deferred foreign keys
+-- violations are passed correctly.
+--
+
+-- box.cfg()
+
+box.begin() box.sql.execute('COMMIT');
+box.begin() box.sql.execute('ROLLBACK');
+box.sql.execute('BEGIN;') box.commit();
+box.sql.execute('BEGIN;') box.rollback();
+
+box.sql.execute('pragma foreign_keys = 1;');
+box.sql.execute('CREATE TABLE child(id INT PRIMARY KEY, x REFERENCES parent(y) DEFERRABLE INITIALLY DEFERRED);');
+box.sql.execute('CREATE TABLE parent(id INT PRIMARY KEY, y INT UNIQUE);');
+
+fk_violation_1 = function()
+ box.begin()
+ box.sql.execute('INSERT INTO child VALUES (1, 1);')
+ box.sql.execute('COMMIT;')
+end;
+fk_violation_1() box.sql.execute('ROLLBACK;')
+box.space.CHILD:select();
+
+fk_violation_2 = function()
+ box.sql.execute('BEGIN;')
+ box.sql.execute('INSERT INTO child VALUES (1, 1);')
+ box.commit()
+end;
+fk_violation_2() box.rollback();
+box.space.CHILD:select();
+
+fk_violation_3 = function()
+ box.begin()
+ box.sql.execute('INSERT INTO child VALUES (1, 1);')
+ box.sql.execute('INSERT INTO parent VALUES (1, 1);')
+ box.commit()
+end;
+fk_violation_3();
+box.space.CHILD:select();
+box.space.PARENT:select();
+
+-- Make sure that 'PRAGMA defer_foreign_keys' works.
+--
+box.sql.execute('DROP TABLE child;')
+box.sql.execute('CREATE TABLE child(id INT PRIMARY KEY, x REFERENCES parent(y))')
+
+fk_defer = function()
+ box.begin()
+ box.sql.execute('INSERT INTO child VALUES (1, 2);')
+ box.sql.execute('INSERT INTO parent VALUES (2, 2);')
+ box.commit()
+end;
+fk_defer();
+box.space.CHILD:select();
+box.space.PARENT:select();
+box.sql.execute('PRAGMA defer_foreign_keys = 1;')
+fk_defer();
+box.space.CHILD:select();
+box.space.PARENT:select();
+
+-- Cleanup
+box.sql.execute('DROP TABLE child;');
+box.sql.execute('DROP TABLE parent;');
--
2.15.1
More information about the Tarantool-patches
mailing list