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 1p6dse-0002C4-FC for pgsql-general@arkaria.postgresql.org; Sat, 17 Dec 2022 20:34:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p6dsd-00073h-2E for pgsql-general@arkaria.postgresql.org; Sat, 17 Dec 2022 20:33:59 +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 1p6dsc-00073Y-OH for pgsql-general@lists.postgresql.org; Sat, 17 Dec 2022 20:33:58 +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 1p6dsa-000192-FG for pgsql-general@lists.postgresql.org; Sat, 17 Dec 2022 20:33:58 +0000 Received: from compute5.internal (compute5.nyi.internal [10.202.2.45]) by mailout.nyi.internal (Postfix) with ESMTP id 5A9855C0092; Sat, 17 Dec 2022 15:33:55 -0500 (EST) Received: from imap42 ([10.202.2.92]) by compute5.internal (MEProxy); Sat, 17 Dec 2022 15:33:55 -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=1671309235; x=1671395635; bh=kYvRbX/nfK Gpgm9pGdjPsDvSNOM/+YwyuES6SkTKuCw=; b=EhMbLZXuplHsl8t6uhNRNVybac EQOdw28i3i3jAb2iIIqMYVOJxfiD64cJahYaiiKbzA/M9HWCOToaK+DLtsG86lwc Zw5KEgdBZstAccX40DEogTaFZP97D6WAYorcDH9c++ONBqlh3EGX1JIQOVZRZHTj 8e57OPMPhK+WqbemUiJbRVys0Ib1E9hu7DsDLbRUW6HAMDURy8Oucq5S48zC8aMD J3EHq0rJhK/h1mMMjMZnaLphptPT1D16hTurS8o5MMpeffKZC8cU48yH0LrU8MXu AnVTEVB2wfCCbhrhCxO3e8B9J3tT+VGB6YvFT0amHzhGnmxL+zWOq+FiGksw== 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=1671309235; x=1671395635; bh=kYvRbX/nfKGpgm9pGdjPsDvSNOM/ +YwyuES6SkTKuCw=; b=BHOa1eb6336XHCXVd98PUiB6Ro5+8EqVAbr/49oPxyL4 +saJAXm7pTSnlKNqm8k0pFevquTGplaKEwUmHp7IKAvtM3Ov3/tPKeIcsfv38Zs3 RsGql1VgarLPD6vAqf+SwLpjR6AHqtgjbbYCsM5cMSgjysDBt3ysCL5VGDZhU7JR W/OkfZgbAdqq9gCVZNyDxbzEDtYXJISj+0dWJIfyZFyL+Y2EcZG9LupJzYlEWW77 95X1crpRCzkR59wo5PHnvGnTQL1MHpHBPubsCfFpP04oEvrSmMCk9wtCusP9O3Mc YPsbtI8w8rCJGoolZJU1Yl05U87d1EXhUDpddshtZQ== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrfeelgddufeejucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucenucfjughrpefofgggkfgjfhffhffvvefutgesth dtredtreertdenucfhrhhomhepfdffrghnucfnrghnghhilhhlvgdfuceouggrnheslhgr nhhgihhllhgvrdhorhhgqeenucggtffrrghtthgvrhhnpeehiefhudetffeuvddvveelle ekvdejhfdukeelhfehgfffheffgfffkeelleejffenucffohhmrghinhepfhhrvghshhhp ohhrthhsrdhorhhgnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilh hfrhhomhepuggrnheslhgrnhhgihhllhgvrdhorhhg X-ME-Proxy: Feedback-ID: ifbf9424e:Fastmail Received: by mailuser.nyi.internal (Postfix, from userid 501) id C7AB5BC0078; Sat, 17 Dec 2022 15:33:54 -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: <5cd18dcb-c526-4c1f-ac4f-c56cfbdcd55e@app.fastmail.com> 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> <6ad40e1f-09dc-4002-8f54-0e2657137aef@app.fastmail.com> Date: Sat, 17 Dec 2022 15:33:33 -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 3:22 PM, Dan Langille wrote: > 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" 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. Here we go, on the problem database, create a new field, based on simple, not english. ALTER TABLE public.ports ADD COLUMN pkgmessage_textsearchable3 tsvector generated always as (to_tsvector('simple'::regconfig, translate(pkgmessage, '/'::text, ' '::text))) stored; Index it: CREATE INDEX ports_pkgmessage_textsearchable3_idx ON public.ports USING gin (pkgmessage_textsearchable3) TABLESPACE pg_default; CREATE INDEX query it: freshports.org=> SELECT id as port_id, element_pathname(element_id) FROM ports WHERE pkgmessage_textsearchable3 @@ websearch_to_tsquery('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 46842 | /ports/branches/2018Q1/mail/postfix-sasl 51532 | /ports/branches/2019Q1/graphics/drm-legacy-kmod Success. Thank you Mr Lane. -- Dan Langille dan@langille.org