From: Dan Langille <dan@langille.org>
To: pgsql-general@lists.postgresql.org
Subject: Re: tsvector not giving expected results on one host
Date: Sat, 17 Dec 2022 14:41:50 -0500
Message-ID: <9f89f00a-e400-4a9b-803b-f84999b6ffca@app.fastmail.com> (raw)
In-Reply-To: <2797dd29-aebb-43a2-9fd7-63bd77a944b2@app.fastmail.com>
References: <2797dd29-aebb-43a2-9fd7-63bd77a944b2@app.fastmail.com>
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=>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: dan@langille.org
Subject: Re: tsvector not giving expected results on one host
In-Reply-To: <9f89f00a-e400-4a9b-803b-f84999b6ffca@app.fastmail.com>
References: <2797dd29-aebb-43a2-9fd7-63bd77a944b2@app.fastmail.com> <9f89f00a-e400-4a9b-803b-f84999b6ffca@app.fastmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox