FTS index lifecycle is non-atomic; interrupted CREATE/DROP leaves residual internal tables that block rebuild and can make a database fail to open on next LOAD

[chiangchenghsin-hash]

Summary

The FTS extension implements CREATE_FTS_INDEX / DROP_FTS_INDEX as table functions whose rewriteFunc returns a long string of sequential statements which the caller parses and executes one by one (not in one transaction). The source itself acknowledges this:

// TODO(Ziyi): Copy statement can't be wrapped in manual transaction, so we can't wrap all
// statements in a single transaction there.

Consequences when anything fails mid-way (crash, interruption, an error in one statement):

1. CREATE interrupted → some of the internal tables (<tableID>_<index>_{docs,terms,appears_in,appears_info}, plus the tokenize macro / stopwords table) are left behind. The next CREATE_FTS_INDEX with the same name fails with Table ... already exists. Please drop or rename ... and there is no automatic cleanup — recovery is manual.

2. DROP interrupted → the index catalog entry may be removed while the internal tables (and/or the storage-side holder) remain. In the worst state ("index entry gone, storage holder + internal tables still present"), neither CREATE_FTS_INDEX (already-exists), nor DROP_FTS_INDEX (doesn't have an index), nor DROP INDEX (not loaded yet) succeeds → no in-place recovery path.

3. Database-open failure: on LOAD EXTENSION fts, initFTSEntries iterates catalog index entries and, for each FTS index, eagerly resolves all four internal tables via getTableCatalogEntry(...); if any is missing (a torn state), it throws Cannot find table catalog entry with id N — the whole database fails to open until the leftover state is manually repaired or a backup is restored.

We reproduced these states deterministically (create → force checkpoint → drop → SIGKILL before checkpoint → reopen). Note: the "storage holder survives unloaded" crash family that this residual state triggers in the engine core is tracked separately in LadybugDB/ladybug#898 with a fix PR (LadybugDB/ladybug#897).

What would help (open to maintainers' preferred shape)

• Self-healing CREATE: before rebuilding, detect internal-set-only residue (tables that exist only in the internal catalog, not as genuine user tables of the same name) and drop it first, so a failed CREATE can simply be retried in place.
• Guarded DROP: only issue DROP TABLE for tables that actually exist and belong to the internal set (so a stray user table with the same name is not clobbered), and drop appears_in before docs/terms (referential order).
• Tolerant LOAD: when an FTS index's internal tables are incomplete, skip that index at load time instead of throwing, so the database still opens and the user can repair (DROP + re-CREATE) instead of restoring a backup.
• Longer term: execute the whole CREATE/DROP flow atomically where the engine allows it.

Existing related upstream threads (we searched)

• httpfs: compile remote-read optimization guards on wasm #71 — "FTS build with multiple indexes fails: default_english_stopwords already exists" (closed) — the stopwords-name collision variant; fixed with idempotent stopwords, not residue self-heal.
• #464 (open) / #430 / #431 / #635 — FTS crash family; none cover the residual-state recovery dead-end.
• No existing issue proposes self-healing residue cleanup, guarded DROP, or skipping broken indexes at LOAD.

We are happy to prepare a PR for the tolerance changes (residue self-heal + guarded DROP + skip-on-LOAD) if the maintainers are interested; they are small and localized to create_fts_index.cpp / drop_fts_index.cpp / fts_extension.cpp.