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:22:37 -0500
Message-ID: <6ad40e1f-09dc-4002-8f54-0e2657137aef@app.fastmail.com> (raw)
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>
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.
--
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: <6ad40e1f-09dc-4002-8f54-0e2657137aef@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> <6ad40e1f-09dc-4002-8f54-0e2657137aef@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