Re: tsvector not giving expected results on one host - Mailing list pgsql-general

"Dan Langille" <dan@langille.org> writes:
>  pkgmessage_textsearchable2 | tsvector                 |           |          | generated always as
(to_tsvector('english'::regconfig,translate(pkgmessage, '/'::text, ' '::text))) stored 

That is not likely to play well with this:

> freshports.org=> show  default_text_search_config ;
>  default_text_search_config
> ----------------------------
>  pg_catalog.simple

because "english" and "simple" will stem words differently.

regression=# select websearch_to_tsquery('english', 'example');
 websearch_to_tsquery
----------------------
 'exampl'
(1 row)

regression=# select websearch_to_tsquery('simple', 'example');
 websearch_to_tsquery
----------------------
 'example'
(1 row)

If what is in your tsvector is 'exampl', then only the first of
these will match.  So IMO the question is not "why is it failing
on prod?", it's "how the heck did it work on the other machine?".
You won't get nice results if websearch_to_tsquery is using a
different TS configuration than to_tsvector did.

            regards, tom lane