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 1p6daC-0001H5-PH for pgsql-general@arkaria.postgresql.org; Sat, 17 Dec 2022 20:14:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p6daA-0001fm-Ug for pgsql-general@arkaria.postgresql.org; Sat, 17 Dec 2022 20:14:54 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p6da9-0001fd-HY for pgsql-general@lists.postgresql.org; Sat, 17 Dec 2022 20:14:54 +0000 Received: from out3-smtp.messagingengine.com ([66.111.4.27]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p6da6-00034z-BC for pgsql-general@lists.postgresql.org; Sat, 17 Dec 2022 20:14:52 +0000 Received: from compute5.internal (compute5.nyi.internal [10.202.2.45]) by mailout.nyi.internal (Postfix) with ESMTP id D46FF5C00AE; Sat, 17 Dec 2022 15:14:48 -0500 (EST) Received: from imap42 ([10.202.2.92]) by compute5.internal (MEProxy); Sat, 17 Dec 2022 15:14:48 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=langille.org; h= cc:cc:content-type:date:date:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:sender:subject :subject:to:to; s=fm2; t=1671308088; x=1671394488; bh=mJEoOjVXQ/ di7RXa69OOlGVromwLqKcWCo8+Fruq56U=; b=wpwe3rto8uRfHSxKRyR6vz//rE dAiEarJFTsRu1x7HWJp/QC4F6QX1iignIvM3rBl6oguN5513ptTwaimj04izzq4f XY0Ohls2zKEXfH/SFz9hVh65uBKoxfuRR1L5sVZN5mA7w+mlfG/9vJyhA9NZ8y63 9H1OfLX6GDvLCcoUKvcis/gLTowW2/oKJlq7KH0LYCAdIxNh/XnD8ryEWugliK9h wsoc2lvNe6XvFLgpFyAs5UpmyP1UlrEt++Wnids8mxVtsQ9LxWqoNF5sRbQG435j /9jv16t7Og7s5SWkGehJioSH/lyNRTN6NopGBChAXEI9xNkuOaPz4C/B4vCA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-type:date:date:feedback-id :feedback-id:from:from:in-reply-to:in-reply-to:message-id :mime-version:references: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=1671308088; x=1671394488; bh=mJEoOjVXQ/di7RXa69OOlGVromwL qKcWCo8+Fruq56U=; b=c5B7hcL8OGSTz2/SIK+A5gHlb6fr0j71tBCAAG/3GMBp cku/3MQFwnX/bvWbDNyvrDkk3Z5Y6c3NMFZIZ5NmcGbD46qCzUloPmQ+M1AUFGIS NYBNmhRYsHLqsPrX5ODmDhIKkco6V/uTWtQx6/giHwyUuIYVBpKgGoyHI2mNvoZ5 46UhAtFOQ4vHworVcBqtaSXEQDImPUouhojJvlNztiTDiXKKvDkB0qw9+3Pbx+Xv X4NUWIZvZwRgWQmHdiaY6kbz87WkM3ZmghXBBEG7nKiG7hgizTmm5tJHcROdBoRZ qy9BZ769nQeUvLB8XNfxgcVuuoCAOplwXLfqeAqxfA== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrfeelgddufeefucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucenucfjughrpefofgggkfgjfhffhffvvefutgesth dtredtreertdenucfhrhhomhepfdffrghnucfnrghnghhilhhlvgdfuceouggrnheslhgr nhhgihhllhgvrdhorhhgqeenucggtffrrghtthgvrhhnpeehiefhudetffeuvddvveelle ekvdejhfdukeelhfehgfffheffgfffkeelleejffenucffohhmrghinhepfhhrvghshhhp ohhrthhsrdhorhhgnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilh hfrhhomhepuggrnheslhgrnhhgihhllhgvrdhorhhg X-ME-Proxy: Feedback-ID: ifbf9424e:Fastmail Received: by mailuser.nyi.internal (Postfix, from userid 501) id 90F12BC0078; Sat, 17 Dec 2022 15:14:48 -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: 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> Date: Sat, 17 Dec 2022 15:14:24 -0500 From: "Dan Langille" To: "Tom Lane" Cc: pgsql-general@lists.postgresql.org Subject: Re: 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 On Sat, Dec 17, 2022, at 2:55 PM, Tom Lane wrote: > "Dan Langille" 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