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 1kdj2L-0002Lb-Sd for pgsql-bugs@arkaria.postgresql.org; Sat, 14 Nov 2020 00:03:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kdj2K-00043U-QO for pgsql-bugs@arkaria.postgresql.org; Sat, 14 Nov 2020 00:03:24 +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 1kdj2I-00043M-UO for pgsql-bugs@lists.postgresql.org; Sat, 14 Nov 2020 00:03:24 +0000 Received: from [66.111.4.26] (helo=out2-smtp.messagingengine.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kdj2A-0002JI-3C for pgsql-bugs@lists.postgresql.org; Sat, 14 Nov 2020 00:03:22 +0000 Received: from compute1.internal (compute1.nyi.internal [10.202.2.41]) by mailout.nyi.internal (Postfix) with ESMTP id 81F3B5C00A4; Fri, 13 Nov 2020 19:03:02 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute1.internal (MEProxy); Fri, 13 Nov 2020 19:03:02 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=anarazel.de; h= date:from:to:cc:subject:message-id:references:mime-version :content-type:content-transfer-encoding:in-reply-to; s=fm3; bh=C 3g/4U8K6l5xx2nnaZKSuHRGgMaHwFVRNqcFaAp3IGA=; b=ptXe4hM8AnojnRmpB Mwonqmgy4g4fEcJP3xtLx567ejfGJ4ToZbLebku/NakRQnI44x4CY70Zjt1I9bt1 P+AadK8Yzl+oFrZs6K4k5STN6hUfqiTF4/OxP12eIu9X1NcNgMXVXM/Z58ejZcSS i3QLrYZBty8nuR1R7Ga5GUrLkaPx9dRk8Ozgs4sDnyOl4T/TPhyCLc8WEyUXcJNn aYsShWd6GsLyKYV4yXUXJAQ2m7bxmcqNTVK/6oaurqD1vkSpyM35Iab1aghlOAF0 K/XjWmA9sD25VZCLGid/zV0rVwpczyCy9xwiEgWuDHlxEEpvfpthfN1fFyDf0qEJ bw6ww== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :date:from:in-reply-to:message-id:mime-version:references :subject:to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender :x-sasl-enc; s=fm1; bh=C3g/4U8K6l5xx2nnaZKSuHRGgMaHwFVRNqcFaAp3I GA=; b=DNd/SSYT1UJpd3jy+A/5DTwP/FWSuWiku3GlXc3TAcujgV5JSHlNULchY QVmY/BHvbcCaTyYmXsRfybSN87XQ4syxLaO4PY1dNBw6rZm1ta1n2CPArvEFC+zq RkpIxDZoYKytxY9OZhWtflNnjHT2zL9KRP6ihP3yurkPbPnGArZxAr71YoadmMhJ n/H2i60ExLoq4PKN/CWoTVBzeAS3zrocCnUhGmKRcoUJNMK1h+0Hu8IkkKUjUBBr WVveKBsarVNQn9Yjp4jNToklkjy7iPKcq08IS+zrX1r6SY3sz8yMS4IRyXhDekCz FkiZpt3B08wy26jiUPi3aR6xbuOEQ== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedujedruddviedgudeiucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucenucfjughrpeffhffvuffkfhggtggugfgjsehtke ertddttddunecuhfhrohhmpeetnhgurhgvshcuhfhrvghunhguuceorghnughrvghssegr nhgrrhgriigvlhdruggvqeenucggtffrrghtthgvrhhnpeefhfduheelueeivefgvedtie fhtdeitedvvedtgedujedvtdehvdejhfehuefgfeenucffohhmrghinhepphhoshhtghhr vghsqhhlrdhorhhgnecukfhppeeijedrudeitddrvddujedrvdehtdenucevlhhushhtvg hrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrnhgurhgvshesrghnrghr rgiivghlrdguvg X-ME-Proxy: Received: from intern.anarazel.de (c-67-160-217-250.hsd1.ca.comcast.net [67.160.217.250]) by mail.messagingengine.com (Postfix) with ESMTPA id D2CFA3064AA7; Fri, 13 Nov 2020 19:03:01 -0500 (EST) Date: Fri, 13 Nov 2020 16:03:00 -0800 From: Andres Freund To: Ernesto =?utf-8?Q?Hern=C3=A1ndez-Novich?= Cc: pgsql-bugs@lists.postgresql.org Subject: Re: PG 9.6.20 -- query misbehaves in replica Message-ID: <20201114000300.jf3myo7ohzuxfpum@alap3.anarazel.de> References: <9bd869ddbddc71721de1d9d6a2710a1cfec5e98b.camel@gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <9bd869ddbddc71721de1d9d6a2710a1cfec5e98b.camel@gmail.com> X-Host-Lookup-Failed: Reverse DNS lookup failed for 66.111.4.26 (deferred) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Hi, On 2020-11-13 15:14:23 -0800, Ernesto Hernández-Novich wrote: > We have a PG 9.6.19/9.6.20 streaming replication set using PGDG > packages over Debian 9 and 10. We started experiencing an interesting > issue yesterday. This is the layout; all arrows are stream replication > built using `pg_basebackup` over replication slots. > > Master N0 (9.6.19 / Debian 9) -> Replica A (9.6.19 / Debian 9) > | | > | +-----------------> Replica B (9.6.20 / Debian 10) > | > v > Replica N1 (9.6.20 / Debian 9) -> Replica C (9.6.20 / Debian 9) > | > +------------------> Replica D (9.6.20 / Debian 10) > > We have a query that is a simple `select * from table where pk = 'fixed > value'`. It fetches a *single* row vía a PK Index Scan using `=` on a > TEXT value, no implicit conversions. Said query used to work fine > across the replication set. > > After updating from 9.6.19 to 9.6.20, we noticed the query was not > working on D. It did NOT bring any rows. If the query is attempted > using LIKE or ~ (with a left-anchored pattern), the correct row is > fetched BUT using a Sequential Scan. We checked B, same behavior. > However, the query works normally (single row, PK IDX scan) on N0, A, > N1, and C. That likely is related to the collation definitions differing between the systems. Note that the systems where it doesn't work are Debian 10, whereas the rest is Debian 9. See https://wiki.postgresql.org/wiki/Locale_data_changes Greetings, Andres Freund