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: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





view thread (9+ messages)  latest in thread

Message-ID: <6ad40e1f-09dc-4002-8f54-0e2657137aef@app.fastmail.com>
Permalink:  ../6ad40e1f-09dc-4002-8f54-0e2657137aef@app.fastmail.com/
Also on:    postgresql.org/message-id/6ad40e1f-09dc-4002-8f54-0e2657137aef@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: <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