pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andres Freund <andres@anarazel.de>
To: Ernesto Hernández-Novich <emhnemhn@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: PG 9.6.20 -- query misbehaves in replica
Date: Fri, 13 Nov 2020 16:03:00 -0800
Message-ID: <20201114000300.jf3myo7ohzuxfpum@alap3.anarazel.de> (raw)
In-Reply-To: <9bd869ddbddc71721de1d9d6a2710a1cfec5e98b.camel@gmail.com>
References: <9bd869ddbddc71721de1d9d6a2710a1cfec5e98b.camel@gmail.com>

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





view thread (6+ messages)  latest in thread

Message-ID: <20201114000300.jf3myo7ohzuxfpum@alap3.anarazel.de>
Permalink:  ../20201114000300.jf3myo7ohzuxfpum@alap3.anarazel.de/
Also on:    postgresql.org/message-id/20201114000300.jf3myo7ohzuxfpum@alap3.anarazel.de

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: andres@anarazel.de, emhnemhn@gmail.com, pgsql-bugs@lists.postgresql.org
  Subject: Re: PG 9.6.20 -- query misbehaves in replica
  In-Reply-To: <20201114000300.jf3myo7ohzuxfpum@alap3.anarazel.de>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox