FIX + PATCH INCL: Postgres pg_tgrm search engine is broken in certain circumstances

[scottnursten-22]

The symptom

When searching and getting less than 5 results, we get

An unexpected error occurred.

in the interface and the docker logs show:

2025-10-03T11:51:51.940Z [MASTER] warn: Search Engine Error:
2025-10-03T11:51:51.940Z [MASTER] warn: SELECT word, word <-> $1 AS rank FROM "pagesWords" WHERE similarity(word, $2) > 0.2 ORDER BY rank LIMIT 5; - operator does not exist: text <-> unknown

The problem

The error operator does not exist: text <-> unknown occurs because:

Missing Extension: The PostgreSQL pg_trgm extension provides the <-> (distance) operator and similarity() function used for fuzzy text matching
Conditional Extension Creation: The extension was only created when the pagesWords table didn't exist, but the query always tries to use these functions
No Fallback: When the extension was missing, there was no graceful fallback mechanism

The Fix

I've made two key changes to server/modules/search/postgres/engine.js:

Always Ensure Extension: Moved the CREATE EXTENSION IF NOT EXISTS pg_trgm command outside the table existence check, so it runs every time the search engine initialises
Added Error Handling with Fallback: Added try-catch around the similarity query with a fallback to basic ILIKE matching

The patch

pg-trgm-search-fix.patch

@NGPixel - hope this helps and is in a useful format.