Re: tsvector not giving expected results on one host

On Sat, Dec 17, 2022, at 1:53 PM, Dan Langille wrote:
> Under PostgreSQL 12, I have a table using tsvector to search the column 
> pkgmessage.
>
> It looks like this (not all columns are shown).
>
>                                                                         
>             Table "public.ports"
>            Column           |           Type           | Collation | 
> Nullable |                                                   Default    
>                                                
>  pkgmessage                 | text                     |           |    
>       | 
>  pkgmessage_textsearchable  | tsvector                 |           |    
>       | generated always as (to_tsvector('english'::regconfig, 
> pkgmessage)) stored

 pkgmessage_textsearchable2 | tsvector                 |           |          | generated always as
(to_tsvector('english'::regconfig,translate(pkgmessage, '/'::text, ' '::text))) stored
 

I see the above should have been included as well.

>
> On several servers, it works fine, like this:
>
> freshports.devgit=# SELECT id as port_id, element_pathname(element_id)
>   FROM ports
>  WHERE pkgmessage_textsearchable2  @@ websearch_to_tsquery('example');
>  port_id |                           element_pathname                           
> ---------+----------------------------------------------------------------------
>   100421 | /ports/branches/2022Q1/dns/dnsmasq
>   100428 | /ports/branches/2022Q1/emulators/xsystem35
>    14686 | /ports/head/sysutils/lmon
> ... etc
>
> On the problem server, production, we get nothing.  Nada.
>
> freshports.org=> SELECT id as port_id, element_pathname(element_id)
>   FROM ports
>  WHERE pkgmessage_textsearchable2  @@ websearch_to_tsquery('example');
>  port_id | element_pathname 
> ---------+------------------
> (0 rows)
>
> freshports.org=> 
>
> However, ilike on the same database does find the matches:
>
> freshports.org=> SELECT id as port_id, element_pathname(element_id)
>   FROM ports
>  WHERE pkgmessage ilike '%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
> ... etc

Let's look at that first entry on the problem database:

freshports.org=> select pkgmessage_textsearchable from ports where id = 34126;

pkgmessage_textsearchable
             
 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 '/tmp':32 '1':10 '2':21 '3':33 '5':17 'client':6,36 'directori':25 'exampl':27 'follow':13,38 'instruct':40 'line':14
'load':19'loader.conf':16 'mount':22,28 'place':11 'pond':3,35,41 'requir':8 'run':34 'tmpfs':9,18,24,30,31 'use':2
'yes':20
(1 row)

freshports.org=> select pkgmessage_textsearchable2 from ports where id = 34126;

pkgmessage_textsearchable2
             
 

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 '1':10 '2':21 '3':33 '5':17 'client':6,36 'directori':25 'exampl':27 'follow':13,38 'instruct':40 'line':14 'load':19
'loader.conf':16'mount':22,28 'place':11 'pond':3,35,41 'requir':8 'run':34 'tmp':32 'tmpfs':9,18,24,30,31 'use':2
'yes':20
(1 row)

freshports.org=> 

From a database which runs this query with expected results:

freshports.devgit=# select pkgmessage_textsearchable2 from ports where id = 34126;

pkgmessage_textsearchable2
             
 

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 '1':10 '2':21 '3':33 '5':17 'client':6,36 'directori':25 'exampl':27 'follow':13,38 'instruct':40 'line':14 'load':19
'loader.conf':16'mount':22,28 'place':11 'pond':3,35,41 'requir':8 'run':34 'tmp':32 'tmpfs':9,18,24,30,31 'use':2
'yes':20
(1 row)

freshports.devgit=# select pkgmessage_textsearchable from ports where id = 34126;

pkgmessage_textsearchable
             
 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 '/tmp':32 '1':10 '2':21 '3':33 '5':17 'client':6,36 'directori':25 'exampl':27 'follow':13,38 'instruct':40 'line':14
'load':19'loader.conf':16 'mount':22,28 'place':11 'pond':3,35,41 'requir':8 'run':34 'tmpfs':9,18,24,30,31 'use':2
'yes':20
(1 row)

freshports.devgit=# 



If I run my query with 'exampl', it finds what I expected, including 'pond' from above

freshports.org=> SELECT id as port_id, element_pathname(element_id)
  FROM ports
 WHERE pkgmessage_textsearchable2  @@ websearch_to_tsquery('exampl');
 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

This is the same on both hosts:

freshports.org=> show  default_text_search_config ;
 default_text_search_config 
----------------------------
 pg_catalog.simple
-- 
  Dan Langille
  dan@langille.org