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 1p6d4a-0008In-Vb for pgsql-general@arkaria.postgresql.org; Sat, 17 Dec 2022 19:42:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p6d4Z-0000Zt-MJ for pgsql-general@arkaria.postgresql.org; Sat, 17 Dec 2022 19:42:15 +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 1p6d4Z-0000Zj-At for pgsql-general@lists.postgresql.org; Sat, 17 Dec 2022 19:42:15 +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 1p6d4W-0000Zi-KJ for pgsql-general@lists.postgresql.org; Sat, 17 Dec 2022 19:42:14 +0000 Received: from compute5.internal (compute5.nyi.internal [10.202.2.45]) by mailout.nyi.internal (Postfix) with ESMTP id B25B45C004D for ; Sat, 17 Dec 2022 14:42:10 -0500 (EST) Received: from imap42 ([10.202.2.92]) by compute5.internal (MEProxy); Sat, 17 Dec 2022 14:42:10 -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:in-reply-to :message-id:mime-version:references:reply-to:sender:subject :subject:to:to; s=fm2; t=1671306130; x=1671392530; bh=qGHA0z79aR zmlpFM+x0MDG9LittxxaHPZijDDPxCZg0=; b=dgCZtwNE65cQMzHpk5+UyYvXPM LEBIMnQc55FyjPe8CLfCoRdQfcAhcH2erCpp6oV9Zpfp7U0MNxXTInYvAGgL0qDw RpsVCGkr+w1bylu91sxhSj/ecCPCyovpttMFPVLlsimr6qbeECB+sjpU0m80rrL+ NK7sY+p2IlqrbzRDJfUHIKuV/JdbbDGJS1V9iL7Uz5CCTv3ZCUokCOksUKv8gLEZ nC3qlb0sLDxTes5M9mX0mLtbPXTMgB+weE143pkmpQTHGRcoPoOYJbpYflA4dRTY B2y+bNFIUxuXw33hiLGSViCISsOiv4tnuMkpWQLZpwsyvUs0k/1GVgEHrOTg== 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: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=1671306130; x=1671392530; bh=qGHA0z79aRzmlpFM+x0MDG9Littx xaHPZijDDPxCZg0=; b=FfU6RN1564wJK2RCzlGITBxrghkSjkOS6MbZ0TIyUH7d 1GWOp4BR1R4MvLBTnSI6R2Dog/UbeobHCjxRZeMeq9VcKjROQUaNLZ7shy1+QtD6 s72OG+YyUmYufBuGzPsArkJDj5ZNQ1AqggB02EHQzFT2rn/46XCQzw0v4NwHFCCI 234X0yf7z0Ln56IEg8AdyOMEVP1mFLG/cu68rNVQP3YDGSqvh4+zqO9212Ndrne8 XLqMjRGVi60vH0eBYUrknHuxPBUTxU8P9Gpe+KCV5Q5XuZCkIPwSSfhc4LV5d+oA LCHd3h3s4JJVY2vZgA2EbnfcSyunbPXnKyMv4+FYwg== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrfeelgdduvdeiucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucenucfjughrpefofgggkfgjfhffhffvufgtsehttd ertderredtnecuhfhrohhmpedfffgrnhcunfgrnhhgihhllhgvfdcuoegurghnsehlrghn ghhilhhlvgdrohhrgheqnecuggftrfgrthhtvghrnhepveelleeuueejieetuddtiedvff efvdetgfelleehleeuieeijeeiteevkedvfeevnecuffhomhgrihhnpehfrhgvshhhphho rhhtshdrohhrghenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfh hrohhmpegurghnsehlrghnghhilhhlvgdrohhrgh X-ME-Proxy: Feedback-ID: ifbf9424e:Fastmail Received: by mailuser.nyi.internal (Postfix, from userid 501) id 85F10BC0078; Sat, 17 Dec 2022 14:42:10 -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: <9f89f00a-e400-4a9b-803b-f84999b6ffca@app.fastmail.com> In-Reply-To: <2797dd29-aebb-43a2-9fd7-63bd77a944b2@app.fastmail.com> References: <2797dd29-aebb-43a2-9fd7-63bd77a944b2@app.fastmail.com> Date: Sat, 17 Dec 2022 14:41:50 -0500 From: "Dan Langille" To: 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 1:53 PM, Dan Langille wrote: > 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 pkgmessage_textsearchable2 | tsvector | | | generated always as (to_tsvector('english'::regconfig, translate(pkgmessage, '/'::text, ' '::text))) stored I see the above should have been included as well. > > 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 Let's look at that first entry on the problem database: freshports.org=> select pkgmessage_textsearchable from ports where id = 34126; pkgmessage_textsearchable ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- '/tmp':32 '1':10 '2':21 '3':33 '5':17 'client':6,36 'directori':25 'exampl':27 'follow':13,38 'instruct':40 'line':14 'load':19 'loader.conf':16 'mount':22,28 'place':11 'pond':3,35,41 'requir':8 'run':34 'tmpfs':9,18,24,30,31 'use':2 'yes':20 (1 row) freshports.org=> select pkgmessage_textsearchable2 from ports where id = 34126; pkgmessage_textsearchable2 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- '1':10 '2':21 '3':33 '5':17 'client':6,36 'directori':25 'exampl':27 'follow':13,38 'instruct':40 'line':14 'load':19 'loader.conf':16 'mount':22,28 'place':11 'pond':3,35,41 'requir':8 'run':34 'tmp':32 'tmpfs':9,18,24,30,31 'use':2 'yes':20 (1 row) freshports.org=>