Re: tsvector not giving expected results on one host

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

Here we go, on the problem database, create a new field, based on simple, not english.

ALTER TABLE public.ports
    ADD COLUMN pkgmessage_textsearchable3 tsvector generated always as (to_tsvector('simple'::regconfig,
translate(pkgmessage,'/'::text, ' '::text))) stored;
 

Index it:

CREATE INDEX ports_pkgmessage_textsearchable3_idx
    ON public.ports USING gin
    (pkgmessage_textsearchable3)
    TABLESPACE pg_default;
CREATE INDEX

query it:

freshports.org=> SELECT id as port_id, element_pathname(element_id)
  FROM ports
 WHERE pkgmessage_textsearchable3  @@ websearch_to_tsquery('example');
 port_id |                           element_pathname                           
---------+----------------------------------------------------------------------
   34126 | /ports/head/security/pond
   74559 | /ports/branches/2015Q3/emulators/linux_base-c6
   60310 | /ports/branches/2020Q4/www/gitlab-ce
   38345 | /ports/head/www/gitlab
   46842 | /ports/branches/2018Q1/mail/postfix-sasl
   51532 | /ports/branches/2019Q1/graphics/drm-legacy-kmod

Success. Thank you Mr Lane.

-- 
  Dan Langille
  dan@langille.org