Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p6cKW-00065v-LL for pgsql-general@arkaria.postgresql.org; Sat, 17 Dec 2022 18:54:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p6cJW-0003lR-0w for pgsql-general@arkaria.postgresql.org; Sat, 17 Dec 2022 18:53:38 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p6cJU-0003k3-9b for pgsql-general@lists.postgresql.org; Sat, 17 Dec 2022 18:53:37 +0000 Received: from out3-smtp.messagingengine.com ([66.111.4.27]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p6cJO-0000E4-HE for pgsql-general@lists.postgresql.org; Sat, 17 Dec 2022 18:53:35 +0000 Received: from compute5.internal (compute5.nyi.internal [10.202.2.45]) by mailout.nyi.internal (Postfix) with ESMTP id DDCDB5C0071 for ; Sat, 17 Dec 2022 13:53:26 -0500 (EST) Received: from imap42 ([10.202.2.92]) by compute5.internal (MEProxy); Sat, 17 Dec 2022 13:53:26 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=langille.org; h= cc:content-type:date:date:from:from:in-reply-to:message-id :mime-version:reply-to:sender:subject:subject:to:to; s=fm2; t= 1671303206; x=1671389606; bh=nyONkQqMbrYdSOAIdLaNkvjAiohwR0FmnZn e1cQk4xg=; b=Go7+0APH4/HZJo5o8fF6N2y/NaoCW4KErJAFGwKnjUlrfBbnYQ3 KcVMmZQRlivtd7W1RueqwLaMnsRVihskLiElJP1oh/5Q3yAbgCZPZrqCUNakkkeV t2ul3sCx2cAayN54p7kCcLVBK/zs5+w0t+LjGLHVAqciWwcr5Xt/IEueUFSVMS/x tUZjBX817OOJAMWHFweUPIDeX+JKLpFw+SdPEraimjLBncxrEYhu7iY9M5V8yu97 uKkU6wwlJTMebOGwZOJFHD4vHZb7SuFtlPaoeqsCWFpKSXbqZhoSQeBBfrPf8aKz izNRZZHKTgqW+8ptDASOfGMSQmRSeMd3UBw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:date:date:feedback-id :feedback-id:from:from:in-reply-to:message-id:mime-version :reply-to:sender:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm2; t=1671303206; x= 1671389606; bh=nyONkQqMbrYdSOAIdLaNkvjAiohwR0FmnZne1cQk4xg=; b=g fP+qyF2x4YdXj8CPAHBX/QiA+DQtASyuEjVdeIcJJaMUqU8Uy0HOIVlE8HjjeCbB dEgrRD9xURl3YZHId6i87U49dMXqU0soya4z9F1wD1K27a9Q595yD0EyeoXMtZtO 4nKHXzRqCbP4Qr3TnkPMLxP/YVPtkW491KynyP1wW+LvJc2lKyBismahOoBrN9YL fav6wI9/1p3NzLxqeTt8txrsKYv3MUOKi7YsyhzRcvZQ3fkskioW/g4pwgVTn+VL UNXLiK3mRcHDYi9oamQlhM4oTxiEYH6bCcUpe17eSYwBUpKbOd1STESLx1Yu9Ufw iXyfNuG5U+2rs/ZahJhgg== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrfeelgdduudejucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucenucfjughrpefofgggkfffhffvufgtsehttdertd erredtnecuhfhrohhmpedfffgrnhcunfgrnhhgihhllhgvfdcuoegurghnsehlrghnghhi lhhlvgdrohhrgheqnecuggftrfgrthhtvghrnhepuefghefghfdvhedvueduiefhhfelje efteetleegiedvvedvfedvhfethfejveehnecuffhomhgrihhnpehfrhgvshhhphhorhht shdrohhrghenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhroh hmpegurghnsehlrghnghhilhhlvgdrohhrgh X-ME-Proxy: Feedback-ID: ifbf9424e:Fastmail Received: by mailuser.nyi.internal (Postfix, from userid 501) id 6A59EBC0078; Sat, 17 Dec 2022 13:53:26 -0500 (EST) X-Mailer: MessagingEngine.com Webmail Interface User-Agent: Cyrus-JMAP/3.7.0-alpha0-1185-g841157300a-fm-20221208.002-g84115730 Mime-Version: 1.0 Message-Id: <2797dd29-aebb-43a2-9fd7-63bd77a944b2@app.fastmail.com> Date: Sat, 17 Dec 2022 13:53:05 -0500 From: "Dan Langille" To: pgsql-general@lists.postgresql.org Subject: tsvector not giving expected results on one host Content-Type: text/plain List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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