pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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





view thread (9+ messages)  latest in thread

Message-ID: <e8c6d7f1-afcd-44e4-a0ff-c283c104aad7@app.fastmail.com>
Permalink:  ../e8c6d7f1-afcd-44e4-a0ff-c283c104aad7@app.fastmail.com/
Also on:    postgresql.org/message-id/e8c6d7f1-afcd-44e4-a0ff-c283c104aad7@app.fastmail.com

 · 

reply

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