From: Dan Langille <dan@langille.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: tsvector not giving expected results on one host
Date: Sat, 17 Dec 2022 15:14:24 -0500
Message-ID: <e8c6d7f1-afcd-44e4-a0ff-c283c104aad7@app.fastmail.com> (raw)
In-Reply-To: <4006202.1671306931@sss.pgh.pa.us>
References: <2797dd29-aebb-43a2-9fd7-63bd77a944b2@app.fastmail.com>
<9f89f00a-e400-4a9b-803b-f84999b6ffca@app.fastmail.com>
<4006202.1671306931@sss.pgh.pa.us>
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)
The hosts on which this search works
freshports.devgit=# select websearch_to_tsquery('example');
websearch_to_tsquery
----------------------
'exampl'
(1 row)
On that same host:
[pg02 dan ~] % sudo grep -i default_text_search_config /var/db/postgres/data12/postgresql.conf
default_text_search_config = 'pg_catalog.english'
--
Dan Langille
dan@langille.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, tgl@sss.pgh.pa.us
Subject: Re: tsvector not giving expected results on one host
In-Reply-To: <e8c6d7f1-afcd-44e4-a0ff-c283c104aad7@app.fastmail.com>
References: <2797dd29-aebb-43a2-9fd7-63bd77a944b2@app.fastmail.com> <9f89f00a-e400-4a9b-803b-f84999b6ffca@app.fastmail.com> <4006202.1671306931@sss.pgh.pa.us> <e8c6d7f1-afcd-44e4-a0ff-c283c104aad7@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