inserts with triggers that update related FTS tables croak when wrapped in a transaction

[punkish]

I have a sequence of inserts wrapped in a transaction somewhat like below

try {
    const treatment {
        journalTitle: 'foo',
        kingdom: 'Animalia',
        phylum: 'Hemiptera',
        …
        treatmentAuthors: [],
        bibRefCitations: [],
        materialCitations: [],
        figureCitations: []
    }

const insertTreatment = (treatment, cache) => {
    // step1: insert journalTitle, kingdom, phylum, etc., and get 
    // their ids, and append them into the treatment object
    
    const { journals_id } = insertJournalGet_journals_id(treatment);
    const { kingdoms_id } = insertKingdomGet_kingdoms_id(treatment);
    …
    treatment.journals_id = journals_id;
    treatment.kingdoms_id = kingdoms_id;
    …

    // step2: insert treatment
    const { treatments_id } = insertTreatmentGet_treatments_id.get(treatment);
    // TRIGGER fires

    // step3: insert bibRefCitations
    const bibRefCitationsCache = {};
    const bibRefCitations = treatment.bibRefCitations;

    if (bibRefCitations.length) {
        for (const bibRefCitation of bibRefCitations) {
            bibRefCitation.treatments_id = treatments_id;
            // INSERT
            const { bibRefCitations_id } = insertBibRefCitationGet_bibRefCitations_id.get(bibRefCitation);
            // TRIGGER fires
            const bibRefCitationId = bibRefCitation.bibRefCitationId;
            bibRefCitations[bibRefCitationId] = bibRefCitations_id;
        }
    }

    … and so on …
}

As noted above with the commend // TRIGGER fires, some of the inserts trigger an insert into related FTS tables. Below is a simplified (but not dumb-ified) schema of the db.

CREATE TABLE bibRefCitations (
    id INTEGER PRIMARY KEY,
    … lots of columns …
    fulltext TEXT COLLATE NOCASE
);

CREATE VIRTUAL TABLE bibRefCitationsFts USING fts5 (
    fulltext,
    content=''
);

CREATE TRIGGER IF NOT EXISTS bc_afterInsert 
    AFTER INSERT ON bibRefCitations 
    BEGIN
        INSERT INTO bibRefCitationsFts(rowid, fulltext) 
        VALUES (new.id, new.fulltext);
    END;`,

CREATE TABLE figureCitations (
    id INTEGER PRIMARY KEY,
    … lots of columns …
    captionText TEXT COLLATE NOCASE
);

CREATE VIRTUAL TABLE figureCitationsFts USING fts5 (
    captionText,
    content=''
);

CREATE TRIGGER IF NOT EXISTS fc_afterInsert 
    AFTER INSERT ON figureCitations 
    BEGIN
        INSERT INTO figureCitationsFts(rowid, captionText) 
        VALUES (new.id, new.captionText);
    END;

CREATE TABLE journals (
    id INTEGER PRIMARY KEY,
    journalTitle TEXT UNIQUE NOT NULL COLLATE NOCASE
);

CREATE TABLE treatments (
    id INTEGER PRIMARY KEY,
    … lots of columns …
    fulltext TEXT COLLATE NOCASE
);

CREATE VIRTUAL TABLE treatmentsFts USING fts5 (
    treatmentTitle,
    fulltext,
    content=''
);

CREATE TRIGGER IF NOT EXISTS tr_afterInsert 
    AFTER INSERT ON treatments 
    BEGIN
        INSERT INTO treatmentsFts(rowid, treatmentTitle, fulltext) 
        VALUES (new.id, new.treatmentTitle, new.fulltext);
    END;

If I disable the triggers, the transaction completes ok. But if the triggers are active, the transaction fails with the message below.

/Users/punkish/Projects/zenodeo3/node_modules/better-sqlite3/lib/methods/transaction.js:66
		after.run();
		      ^
SqliteError: no such savepoint: 	_bs3.
    at sqliteTransaction (/Users/punkish/Projects/zenodeo3/node_modules/better-sqlite3/lib/methods/transaction.js:66:9)
    at Module.<anonymous> (file:///Users/punkish/Projects/zenodeo3/bin/truebug/lib/database/index.js:245:13)
    at Module.sqliteTransaction (/Users/punkish/Projects/zenodeo3/node_modules/better-sqlite3/lib/methods/transaction.js:65:24)
    at processFiles (file:///Users/punkish/Projects/zenodeo3/bin/truebug/index.js:159:26)
    at etl (file:///Users/punkish/Projects/zenodeo3/bin/truebug/index.js:208:9)
    at update (file:///Users/punkish/Projects/zenodeo3/bin/truebug/index.js:251:9)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'SQLITE_ERROR'
}

If I change the triggers to something innocuous like below, then also the transaction completes ok.

CREATE TRIGGER IF NOT EXISTS tr_afterInsert 
    AFTER INSERT ON treatments 
    BEGIN
        SELECT rowid FROM treatmentsFts WHERE rowid = 0;
    END;

I entered the SQL statements, wrapped in a transaction, on the SQLite CLI, and they work fine even with the triggers. So, this is likely a problem with better-sqlite3 or, more likely, with my code. Can anyone shed some light on what is going on? Perhaps an important note: the above JavaScript code is a transaction that inserts a single "treatment" (see const treatment {} at the top of the post) runs inside another transaction that inserts many treatments like so

const insertTreatments = db.transaction((treatments) => {

        for (const treatment of treatments) {
            insertTreatment(treatment, cache);
        }

});

[Prinzhorn]

I didn't read through your whole thing, just linking this here as it sounds related and you might just run into a SQLite bug as well #654

[punkish]

thanks. Yes, it seems very similar to what I am doing and experiencing. I am also using insert with RETURNING and triggers that insert into FTS tables, all wrapped in a transaction. The link you provided to issue #654 has the last post from Nov 2022. Shall I assume that the bug still exists in either SQLite or in better-sqlite3?

[Prinzhorn]

There hasn't been any activity in the SQLite forum, so I assume they overlooked that https://sqlite.org/forum/forumpost/21127c1160

[punkish]

yes, but to be fair, there was some activity, and it was thought that the problem was fixed, except it wasn't. But it wasn't followed up also and, as you hint above, it seems to have fallen through the cracks. I am not knowledgeable about the better-sqlite3 internals, but perhaps if one of the module developers were to follow up again on SQLite forum, it might yield some result. I did jog it in my thread but it perhaps needs its own thread with clear description of the problem.