max / makenotwork
| 1 | -- Full-text search vectors for discover, and the removal of the index that |
| 2 | -- should never have covered `body`. |
| 3 | -- |
| 4 | -- RUN THIS BEFORE THE CATALOG GROWS. `items` is a growth table |
| 5 | -- (tests/migration_hygiene.rs) and `ADD COLUMN ... GENERATED ... STORED` |
| 6 | -- rewrites it under ACCESS EXCLUSIVE, blocking every write for the duration. |
| 7 | -- At the current catalog size that is seconds. It does not stay seconds, and |
| 8 | -- the hygiene guard does not catch it: that test inspects CREATE INDEX and |
| 9 | -- CREATE TABLE, not ALTER TABLE, so this file passes the lint while doing the |
| 10 | -- more disruptive thing. If this has to be re-run against a grown table, |
| 11 | -- convert it first to a plain nullable column plus a trigger and a chunked |
| 12 | -- backfill. That form takes no long lock, but a trigger can drift from the |
| 13 | -- expression it is meant to maintain in a way a generated column cannot, so it |
| 14 | -- is worse to live with and only worth it under real load. |
| 15 | -- |
| 16 | -- The index builds are NOT here. `CREATE INDEX CONCURRENTLY` cannot run inside |
| 17 | -- a transaction, and `-- no-transaction` alone is not enough: Postgres wraps a |
| 18 | -- multi-statement simple query in an implicit transaction anyway, so a |
| 19 | -- concurrent build has to be the only statement in its file. Hence 177 and 178. |
| 20 | -- |
| 21 | -- Statements here are written re-runnably regardless: IF EXISTS on the drop, |
| 22 | -- IF NOT EXISTS on the adds. |
| 23 | -- |
| 24 | -- Two changes, one reason each. |
| 25 | -- |
| 26 | -- 1. DROP idx_items_search (001_initial_schema.sql:473). |
| 27 | -- |
| 28 | -- It has never had a reader: `to_tsvector` appears nowhere in src/, only in |
| 29 | -- 001. So today it is pure write amplification, recomputed and re-indexed on |
| 30 | -- every item insert and update. |
| 31 | -- |
| 32 | -- It is also not safe to adopt, which is the part worth recording. Its |
| 33 | -- expression is |
| 34 | -- |
| 35 | -- to_tsvector('english', title || ' ' || COALESCE(description, '') |
| 36 | -- || ' ' || COALESCE(body, '')) |
| 37 | -- |
| 38 | -- and `items.body` is the sellable article text, gated behind has_purchased |
| 39 | -- (routes/pages/public/content/item.rs:104; the store page renders only a short |
| 40 | -- preview). A public search predicate served by that index answers "does this |
| 41 | -- paid item contain this phrase?" for anyone, unauthenticated, one query at a |
| 42 | -- time. That is a paywall oracle, and it is exactly the trap the next person |
| 43 | -- wiring up FTS would fall into by reaching for the index that already exists. |
| 44 | -- Dropping it removes the trap along with the write cost. |
| 45 | -- |
| 46 | -- 2. ADD search_tsv to items and projects. |
| 47 | -- |
| 48 | -- Title and description only. `body` is deliberately excluded per the above, |
| 49 | -- and there is no honest way to include it: search runs before purchase, so |
| 50 | -- there is no entitlement to check at query time. |
| 51 | -- |
| 52 | -- Generated-stored columns rather than expression indexes, matching |
| 53 | -- multithreaded's 018. An expression index only serves a predicate written |
| 54 | -- character-identical to its expression, which has already cost time here once |
| 55 | -- (see the COALESCE(i.description, '') comment in db/discover.rs, and the |
| 56 | -- ordering note it carries). A named column cannot drift that way. |
| 57 | -- |
| 58 | -- setweight gives title precedence inside ts_rank itself, replacing the |
| 59 | -- hand-rolled `similarity(description) * 0.5` weighting that the trigram path |
| 60 | -- uses. 'A' outranks 'B' in every ts_rank variant, so a title hit sorts above a |
| 61 | -- description-only hit without a magic constant. |
| 62 | -- |
| 63 | -- Both to_tsvector(regconfig, text) and setweight are IMMUTABLE with the config |
| 64 | -- named explicitly, which is what makes them legal in a generated column. The |
| 65 | -- bare to_tsvector(text) form is not, it depends on default_text_search_config, |
| 66 | -- so the 'english' argument is load-bearing rather than decoration. |
| 67 | |
| 68 | IF EXISTS idx_items_search; |
| 69 | |
| 70 | items ADD COLUMN IF NOT EXISTS search_tsv tsvector |
| 71 | GENERATED ALWAYS AS ( |
| 72 | setweight(to_tsvector('english', title), 'A') |
| 73 | || setweight(to_tsvector('english', COALESCE(description, '')), 'B') |
| 74 | ) STORED; |
| 75 | |
| 76 | -- Projects carry no description in the search path (PROJECT_SEARCH_CLAUSE |
| 77 | -- matches p.title alone), so the vector is title-only. It is still setweight'd |
| 78 | -- 'A' despite having nothing to outrank: `mnw_word_match_score` (migration 179) |
| 79 | -- reads title lexemes out of both vectors with the same `ts_filter(.., '{a}')`, |
| 80 | -- and an unweighted vector would hand it an empty array and score every project |
| 81 | -- zero. |
| 82 | projects ADD COLUMN IF NOT EXISTS search_tsv tsvector |
| 83 | GENERATED ALWAYS AS (setweight(to_tsvector('english', title), 'A')) STORED; |
| 84 |