Re: tsvector not giving expected results on one host

On Sat, Dec 17, 2022, at 3:14 PM, Dan Langille wrote:
> 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)

Ahh, this explains the differences and as to why it works where it shouldn't?

freshports.org=> select setting, source from pg_settings where name = 'default_text_search_config';
setting | source
-------------------+---------
pg_catalog.simple | default
(1 row)

>
> The hosts on which this search works
>
> freshports.devgit=# select websearch_to_tsquery('example');
> websearch_to_tsquery
> ----------------------
> 'exampl'
> (1 row)

freshports.devgit=# select setting, source from pg_settings where name = 'default_text_search_config';
setting | source
--------------------+--------------------
pg_catalog.english | configuration file
(1 row)

At least now I know what I can play with to get all hosts in sync.

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