pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dan Langille <dan@langille.org>
To: pgsql-general@lists.postgresql.org
Subject: tsvector not giving expected results on one host
Date: Sat, 17 Dec 2022 13:53:05 -0500
Message-ID: <2797dd29-aebb-43a2-9fd7-63bd77a944b2@app.fastmail.com> (raw)

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

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

Both database are SQL_ASCII with the same Collate and Ctype settings.

Before composing this email, I ran "reindex table concurrently ports;" on the problem database. It did not change the results.

prod, has problem

                                     List of databases
      Name      |  Owner   | Encoding  |   Collate   |    Ctype    |   Access privileges   
----------------+----------+-----------+-------------+-------------+-----------------------
 freshports.org | postgres | SQL_ASCII | C           | C           | 


dev, no issues:

[pg02 dan ~] % psql -l
                                        List of databases
            Name             |    Owner     | Encoding  | Collate | Ctype |   Access privileges   
-----------------------------+--------------+-----------+---------+-------+-----------------------
 freshports.devgit           | postgres     | SQL_ASCII | C       | C     | 

Any ideas as to what to search please?

Oh, one difference. All the working-as-expected databases are self-hosted on FreeBSD. The problem database is on AWS RDS.
-- 
  Dan Langille
  dan@langille.org





view thread (9+ messages)  latest in thread

Message-ID: <2797dd29-aebb-43a2-9fd7-63bd77a944b2@app.fastmail.com>
Permalink:  ../2797dd29-aebb-43a2-9fd7-63bd77a944b2@app.fastmail.com/
Also on:    postgresql.org/message-id/2797dd29-aebb-43a2-9fd7-63bd77a944b2@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
  Subject: Re: tsvector not giving expected results on one host
  In-Reply-To: <2797dd29-aebb-43a2-9fd7-63bd77a944b2@app.fastmail.com>
  References: <2797dd29-aebb-43a2-9fd7-63bd77a944b2@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