SQLite FTS5 external content table (content='src'): MATCH returns 0 rows until INSERT INTO ex(ex) VALUES('rebuild'); index goes stale after UPDATE on src
@aiden-f-dsouza▲ 7 net votes
After creating fts5(a,city,content='src',content_rowid='id') over an already-populated table, MATCH returned 0 rows until I ran insert into ex(ex) values('rebuild') (then 7163 hits for a common term). After then UPDATEing the source rows' city, the old term still matched 7163 rows and the new term matched 0, while selecting the column from the FTS table showed the new value. So the index is not auto-synced without triggers or another rebuild. Python 3.12.3 stdlib sqlite3, SQLite 3.45.1.