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 1kdiJr-0000eS-Mo for pgsql-bugs@arkaria.postgresql.org; Fri, 13 Nov 2020 23:17:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kdiJp-0004CI-2t for pgsql-bugs@arkaria.postgresql.org; Fri, 13 Nov 2020 23:17:25 +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 1kdiH2-0003nm-Q5 for pgsql-bugs@lists.postgresql.org; Fri, 13 Nov 2020 23:14:32 +0000 Received: from mail-pg1-x536.google.com ([2607:f8b0:4864:20::536]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kdiGy-0001fp-NH for pgsql-bugs@lists.postgresql.org; Fri, 13 Nov 2020 23:14:32 +0000 Received: by mail-pg1-x536.google.com with SMTP id f38so8335955pgm.2 for ; Fri, 13 Nov 2020 15:14:28 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=message-id:subject:from:to:date:user-agent:mime-version :content-transfer-encoding; bh=Ot766hjSUyAj8SmNey5KJJXr1daDWcYwws7qCsb/uIM=; b=fVtHYsnwvjNJfOxpVRJEwtwOjexcmMaOZhilQgnwqV4r0BUZjUGMmUygAvvziQBdCX N4vjJ1e0wGPyh0aWWRBB7mL5Dj+uaxStCABxgHxLmDsRebJmnjVzkxJQH9r2pWYAZFE2 x2eAjdhMTFA2hht9wkmbTDCmIkDQuHjJRdm4mfVMYsTg6VoUVWwFL+P3xsp3wDH0sKso d7deCsx9ZtM1bxVhTMwAtzQpuBMdqIiEw7SNUkQWYA+gY478teuf3lImBSfq/D48HKcB OKUaUllPXMTxzPDkg2tJvOcZT9xpVYll25x8vA405bNtyVgKxcEbyqHqIgulJ5duPosj ENGA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:message-id:subject:from:to:date:user-agent :mime-version:content-transfer-encoding; bh=Ot766hjSUyAj8SmNey5KJJXr1daDWcYwws7qCsb/uIM=; b=UwlgIJskZ0yCOss9hc/XVexovP20SGWx9efwkKKKgBX/a2yz+KGyeIA84ngvCJ4a1U /PY/SxcqsN06Si2MJ2zFd1IEtkq9JQzTnGJWlha7mhBngk/s55+81+O7xStwCj/yB7t1 07bzqy0AzymjeitPfdbhYnh1OH0M+IhpuFcGXeIiFSkvJG/f0XwKaW1i5LEFH8dgstZ4 hjZ9Y2IRMrK/UDe5Xfdf8bxQi0wJY3BLugS63tr7MND1o0ABdyoatxitFsHc5UQptmez gIbwf9M5aPZzofmeXNXuw6EpZ452f+838mc42zE9fS8DDasegJwK3BDSlVmkzk0XeCy9 gE5w== X-Gm-Message-State: AOAM5306kK6oTLwueNhG9X7tnKDyePK8hYej8X/Yks5tIJ6ggW8/4BTp JSGHkHmqa2D8b0VGEmHwxuLTU35JvVx5BQ== X-Google-Smtp-Source: ABdhPJxaDHRtXvNTKTMSr7OKZe7dfLnlqfObVqG7HGkljaWxgEtUIJIL512BV2m//+2GA03dvKGNtw== X-Received: by 2002:a63:df58:: with SMTP id h24mr3774144pgj.206.1605309265622; Fri, 13 Nov 2020 15:14:25 -0800 (PST) Received: from vg-db (cpe-108-184-74-100.socal.res.rr.com. [108.184.74.100]) by smtp.gmail.com with ESMTPSA id j25sm10507852pfa.199.2020.11.13.15.14.24 for (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Fri, 13 Nov 2020 15:14:24 -0800 (PST) Message-ID: <9bd869ddbddc71721de1d9d6a2710a1cfec5e98b.camel@gmail.com> Subject: PG 9.6.20 -- query misbehaves in replica From: Ernesto =?ISO-8859-1?Q?Hern=E1ndez-Novich?= To: pgsql-bugs@lists.postgresql.org Date: Fri, 13 Nov 2020 15:14:23 -0800 Content-Type: text/plain; charset="UTF-8" User-Agent: Evolution 3.30.5-1.1 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Hello, 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. We've tried: 1. REINDEX table / VACUUM ANALYZE table -- nothing changed. 2. REINDEX DATABASE / REINDEX SYSTEM on N0 -- nothing changed. 3. VACUUM FULL ANALYZE on N0 -- nothing changed. 3. Re-creating replicas B (and D) from scratch (remove $PGDATA, `pg_basebackup`, use replication slot) -- nothing changed. 4. Creating a NEW replica X (9.6.20 / Debian 10) on a DIFFERENT machine. First, using N1 as master, then using N0 as master. Nothing changes. The query NEVER works on replicas B, D, nor the NEW X. Looks like a the 9.6.20 over Debian 10 is the culprit, but I have nothing else to work on. Suggestions? -- Ernesto Hernández-Novich - @iamemhn - Unix: Live free or die! Geek by nature, Linux by choice, Debian of course. If you can't aptitude it, it isn't useful or doesn't exist. GPG Key Fingerprint = 0064 ADF5 EB5C DE16 99C1 6C56 F2A3 86B5 A757 E5A1