Different query result, maybe bad index

On Wed, 2021-02-24 at 12:50 +0100, Václav Steiner wrote:

We have three servers running postgres 9.6, master and two slaves feeded by streaming replication.
On of those slaves we are getting different query results. The replica was recreated from scratch, but problem persists.

If set enable_indexscan='off' and set enable_bitmapscan='off' we get correct result.
It looks like problem with some index...?

Can anybody saw such behaviour?

If the problem persist when you rebuild the standby, I suspect that the servers
are running different C libraries or C library versions, so that the collations
with the same name behave slightly differently.

That could lead to the observed behavior with indexes on string data types.

Make sure to use the same C library version on both systems.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Hi,

On Wed, Feb 24, 2021 at 7:50 PM Václav Steiner <steiner.vaclav@gmail.com> wrote:

We have three servers running postgres 9.6, master and two slaves feeded by streaming replication.
On of those slaves we are getting different query results. The replica was recreated from scratch, but problem persists.

If set enable_indexscan='off' and set enable_bitmapscan='off' we get correct result.
It looks like problem with some index...?

Can anybody saw such behaviour?

Are those indexes on collatable data type? If yes, the most likely
explanation would be that both server have different underlying
library. If you're on GNU/Linux and one of the server has glibc 2.28
and the other doesn't, don't look any further. See for reference
https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade.html

I'll then send of course the table, index, etc. definition if necessary.

That would be helpful, including detail on the server itself.