[sqlite] Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all

[Yuri]

The attached testcase injects the foreign key violation into a long 
transaction. This makes the remainder of the transaction much slower, 
even though the foreign key is deferred, and should only be checked in 
the end of the transaction.

While working on this testcase, I found that sometimes the foreign key 
violation doesn't trigger the error at all. Please change VIOLATION to 
0, and observe that there is no failure now, though it should be.


sqlite3-3.24.0 on FreeBSD 11.2


Yuri



---testcase---

#include <sqlite3.h>
#include <stdio.h>
#include <stdlib.h>
#include <time.h>

void doSql(sqlite3 *db, const char *sql) {
  char *err_msg = 0;
  int rc = sqlite3_exec(db, sql, 0, 0, &err_msg);
  if (rc != SQLITE_OK ) {
    fprintf(stderr, "SQL error: %s\n", err_msg);
    sqlite3_free(err_msg);
    sqlite3_close(db);
    exit(1);
  }
}

#define NROWS 100000
#define VIOLATION 1000000

int main(void) {
  sqlite3 *db;

  char s[512];

  int rc = sqlite3_open(":memory:", &db);
  if (rc != SQLITE_OK) {
    fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
    sqlite3_close(db);
    return 1;
  }

  doSql(db, "PRAGMA foreign_keys = ON;");

  printf("creating B ...\n");
  doSql(db, "create table b (id int PRIMARY KEY, name text)");

  printf("populating B ...\n");
  for (int i = 0; i < NROWS; i++) {
    sprintf(s, "insert into b values(%d, 'The name field for %d')", i, i);
    doSql(db, s);
  }

  printf("creating A ...\n");
  doSql(db, "create table a (id int PRIMARY KEY, name text, aid int not 
null, FOREIGN KEY(aid) REFERENCES a(id))");

  printf("populating A ...\n");
  doSql(db, "BEGIN TRANSACTION;");
  doSql(db, "PRAGMA defer_foreign_keys=ON;");
  for (int i = 0; i < NROWS; i++) {
    if (i % 1000 == 0)
      printf("...row#%d... (time=%ld)\n", i, time(0));
    sprintf(s, "insert into a values(%d, 'The name field for %d', %d)", 
i, i, i);
    doSql(db, s);
    // introfuce the FK violation
    if (i == NROWS/4) {
      sprintf(s, "insert into a values(%d, 'The name field for %d', 
%d)", NROWS+i, i, NROWS+i+VIOLATION);
      doSql(db, s);
    }
  }
  doSql(db, "COMMIT TRANSACTION;");

  sqlite3_close(db);

  return 0;
}

_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users