From: Ernesto Hernández-Novich <emhnemhn@gmail.com>
To: pgsql-bugs@lists.postgresql.org
Subject: PG 9.6.20 -- query misbehaves in replica
Date: Fri, 13 Nov 2020 15:14:23 -0800
Message-ID: <9bd869ddbddc71721de1d9d6a2710a1cfec5e98b.camel@gmail.com> (raw)
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
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: emhnemhn@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: PG 9.6.20 -- query misbehaves in replica
In-Reply-To: <9bd869ddbddc71721de1d9d6a2710a1cfec5e98b.camel@gmail.com>
* 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