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(at)langille(dot)org