Hello,

I think I found a bug in FTS5. It prevents sqlite3_interrupt from working as expected. Please find a reproducing case in the attached C file below.

The program creates an FTS5 table, opens a transaction, performs an insert, interrupts the database, and tries to perform a rollback.

Instead of completing successfully, the rollback fails. Here is the program output:

Open statements before the rollback:
- REPLACE INTO 'main'.'documents_docsize' VALUES(?,?)
- (null)
- INSERT INTO 'main'.'documents_content' VALUES(?,?)
- PRAGMA 'main'.data_version
- REPLACE INTO 'main'.'documents_config' VALUES(?,?)
- REPLACE INTO 'main'.'documents_data'(id, block) VALUES(?,?)
ROLLBACK failed: interrupted

This is unexpected. The interruption should have no effect because no statement is supposed to be running at the time sqlite3_interrupt is called.

My interpretation is that FTS5 leaves some of its private statements open (i.e. not reset), and that makes the interruption "stick". Unfortunately, those statements are a private FTS5 implementation detail, and I do not know how the application could complete them correctly in order to restore the expected behavior.

What do you think? Is there a workaround?

Thanks in advance, Gwendal Roué

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

static void check_rc(int rc, sqlite3 *db, const char *msg) {
    if (rc != SQLITE_OK && rc != SQLITE_DONE && rc != SQLITE_ROW) {
        fprintf(stderr, "%s failed: %s\n", msg, sqlite3_errmsg(db));
        sqlite3_close(db);
        exit(1);
    }
}

int main(void) {
    sqlite3 *db = NULL;
    sqlite3_stmt *stmt = NULL;
    int rc;

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

    const char *sql_create =
        "CREATE VIRTUAL TABLE documents USING fts5(content);";

    rc = sqlite3_exec(db, sql_create, NULL, NULL, NULL);
    check_rc(rc, db, "CREATE VIRTUAL TABLE");

    rc = sqlite3_exec(db, "BEGIN TRANSACTION;", NULL, NULL, NULL);
    check_rc(rc, db, "BEGIN TRANSACTION");

    const char *sql_insert = "INSERT INTO documents(content) VALUES('whatever');";
    rc = sqlite3_exec(db, sql_insert, NULL, NULL, NULL);
    check_rc(rc, db, "INSERT");
    
    // Interrupt
    sqlite3_interrupt(db);

    // List open statements
    printf("Open statements before the rollback:\n");
    for (stmt = sqlite3_next_stmt(db, NULL); stmt != NULL; stmt = sqlite3_next_stmt(db, stmt)) {
        const char *sql = sqlite3_sql(stmt);
        printf("- %s\n", sql ? sql : "(null)");
    }
        
    // Rollback
    rc = sqlite3_exec(db, "ROLLBACK;", NULL, NULL, NULL);
    check_rc(rc, db, "ROLLBACK");

    sqlite3_close(db);
    return 0;
}

Thanks for reporting this. Hopefully now fixed here:

https://sqlite.org/src/info/f88e1d0357

Dan.

Thank you very much! :-)