Re: tsvector not giving expected results on one host

On Sat, Dec 17, 2022, at 2:55 PM, Tom Lane wrote:
> "Dan Langille" <dan(at)langille(dot)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.

I think this shows why we are getting the results we see. Credit to ch on IRC for asking this question.

The problem host:

freshports.org=> select websearch_to_tsquery('example');
websearch_to_tsquery
----------------------
'example'
(1 row)

The hosts on which this search works

freshports.devgit=# select websearch_to_tsquery('example');
websearch_to_tsquery
----------------------
'exampl'
(1 row)

On that same host:

[pg02 dan ~] % sudo grep -i default_text_search_config /var/db/postgres/data12/postgresql.conf
default_text_search_config = 'pg_catalog.english'

--
Dan Langille
dan(at)langille(dot)org