"unsafe use of virtual table" when creating triggers for sqlite full text search (fts)

Question

I am attempting to make use of SQLite's full text search (FTS) extension in an external content table by following the official documentation. I am running sqlite version 3.42.0 from the Arch linux package sqlite, currently on version 3.42.0-1.

The schema is as follows:

-- Create main data table
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

-- Create fts index table
CREATE VIRTUAL TABLE users_fts USING fts5 (
    content='users',
    content_rowid='id',
    name
);

-- Create insert trigger
CREATE TRIGGER users_fts_insert AFTER INSERT ON users
BEGIN
    INSERT INTO users_fts (rowid, name) VALUES (new.rowid, new.name);
END;

-- Insert some sample data
INSERT INTO users (rowid, name) VALUES
(NULL, 'John Smith'),
(NULL, 'John Doe'),
(NULL, 'Jane Doe');

When running the above in sqlite3, the INSERT step fails with the error message:

Parse error near line 21: unsafe use of virtual table "users_fts"

If I comment out the trigger block it successfully creates both tables and inserts values into the main table. Curiously, the FTS table is also seemingly created with matching rows, and the rows appear to be kept synchronized across various INSERT/UPDATE/DELETEs, although the actual FTS functionality via MATCH does not work (e.g. SELECT * FROM users_fts WHERE users_fts MATCH 'John' returns no rows).

As I don't want to have to deal with manually keeping the two tables in sync, I would really prefer to have an automatic solution based on triggers such as the above. Is the issue in how I am setting up the trigger, or in how I am configuring the FTS table? Or perhaps there's some hidden limitation in SQLite FTS that precludes this kind of trigger?

Any help here would be greatly appreciated!

Monkeypot

I had the same issue, with using sqlite3 -init and bulk inserting into a table that has a trigger on it. No direct involvement of the FTS table, only 2-3 levels of triggers deep. – Whipperin

Answer 1

This is a side effect of the TRUSTED_SCHEMA pragma being set to false.

The pragma was added in Sqlite 3.31, and is documented as defaulting to true, but 3.42 now has the sqlite3 shell program disable it by default. It appears to be a side effect of a new option, --unsafe-testing. Without that option being present, several settings including this one are toggled from their defaults.

Happily, connections to databases in user code aren't affected, just ones made with the standard sqlite3 program. So you can invoke it with --unsafe-testing, or run the following command after starting the shell (Every time; it's not persistent):

PRAGMA trusted_schema=1;

Telmatelo

I don't see anyone mentioning running into the problem yet in the sqlite forum, which is kind of surprising. Maybe not many people use sqlite3 for interactive or batch insertion of fts data into foreign content tables, as this seems likely to be the most common use case to be impacted. – Telmatelo

That seems to have fixed it! I'm surprised that this setting is different in the interactive shell vs the library, but I guess if people don't bother using the REPL directly for batch fts insertion as you mentioned, that would explain why every online resource I found expected the triggers to work. Thanks for your help! – Monkeypot