Class PostgresFts

The script that makes the index, three statements for exec() to run on each open:

1. A DO block that adds the _tsv column when the content table has none.
2. A DO block that creates the GIN index idx_<table>_tsv when the content table's schema has no relation of that name.
3. An UPDATE that fills _tsv on the rows where it is null.

ALTER TABLE takes its ACCESS EXCLUSIVE lock, and CREATE INDEX its SHARE lock, before either reads its IF NOT EXISTS, and a lock request waits behind every queued request it conflicts with: an open behind a long read of the table would hold up each later read and write of it until that read ended. So each block reads the catalog first, through to_regclass, which finds the content table as the statement does (the search path, a schema prefix, quoted names) and locks nothing. An open that finds the column and the index takes no lock on the table beyond the UPDATE's ROW EXCLUSIVE, which no read or write of the table waits for.

Two first opens can both pass a check. The later ALTER TABLE waits for the earlier one's lock and then finds the column, which its IF NOT EXISTS passes. The later CREATE INDEX shares the earlier one's lock, builds an index of its own, and fails on the catalog's unique name (unique_violation) once the earlier one commits: its block takes that failure as the index made.

Generate a WHERE clause fragment for full-text matching. SQLite: memory_traces_fts MATCH ? Postgres: _tsv @@ websearch_to_tsquery('english', $1), in the index's own language

A rank value, in each engine's own direction. SQLite FTS5: memory_traces_fts.rank (lower is better) SQLite FTS4: length(offsets(memory_traces_fts)) (higher is better) Postgres: ts_rank(_tsv, websearch_to_tsquery('english', $1)) (higher is better) Use relevanceOrder to order the same way on every engine.

An ORDER BY fragment that puts the best match first on every engine. SQLite FTS5: memory_traces_fts.rank SQLite FTS4: length(offsets(memory_traces_fts)) DESC Postgres: ts_rank(_tsv, websearch_to_tsquery('english', $1)) DESC

Generate the rebuild/reindex command. SQLite: INSERT INTO fts_table(fts_table) VALUES('rebuild') Postgres: UPDATE content_table SET _tsv = to_tsvector('english', col1 || ' ' || col2)

Generate an INSERT to sync external-content FTS after a row insert. SQLite: INSERT INTO fts_table (rowid, col1, col2) VALUES (expr, ?, ?) Postgres: UPDATE content_table SET _tsv = to_tsvector(...) WHERE ...

Sanitize natural-language input into a safe search query. Every engine: the words, quoted, joined with OR, with the operators of the query syntax stripped, so it matches the same rows everywhere.

Generate a SELECT joining the FTS index to the content table. This handles the structural difference between FTS5 (separate virtual table joined via rowid) and Postgres (tsvector column on the content table itself).