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