1

In my setup I have one main and two replicas PostgreSQL-13 servers. On the main and one of the replica servers I have the following (normal) situation when running queries:

SELECT id, field FROM table WHERE table.field = 'some.string';

   id   |    field 
--------+-----------------
 555555 | some.string
(1 row)

SELECT id, field FROM table WHERE table.field LIKE 'some.string';

   id   |    field 
--------+-----------------
 555555 | some.string
(1 row)

But on the second replica server, I have the following situation, which I can't call normal:

SELECT id, field FROM table WHERE table.field = 'some.string';

   id   |    field 
--------+-----------------
(0 rows)

SELECT id, field FROM table WHERE table.field LIKE 'some.string';

   id   |    field 
--------+-----------------
(0 rows)

SELECT id, field FROM table WHERE table.field LIKE '%some.string';

   id   |    field 
--------+-----------------
 555555 | some.string
(1 row)

SELECT id, field FROM table WHERE table.field LIKE 'some.string%';

   id   |    field 
--------+-----------------
 555555 | some.string
(1 row)

Servers are in sync, sync state and lsn number are the same. I tried to rebuild index for this field (it is unique) on the main server and it did not help. Compared all collations and locales on the systems and could not find any difference. Stuck now. Want to drop index and create, but still not sure it may help.

Dexterite
  • 113
  • 1
  • 4
  • 2
    Maybe different versions of glibc? https://wiki.postgresql.org/wiki/Locale_data_changes –  Mar 24 '22 at 08:37
  • @a_horse_with_no_name omg it was so simple, thanks a lot, how I could forget about glibc :( – Dexterite Mar 24 '22 at 08:50
  • While I understand why the Postgres team decided to depend on glibc, I think it is time to seriously rethink that usage/implementation –  Mar 24 '22 at 09:25

1 Answers1

0

We have forgotten to take into account the change in sorting in 2.28 version of glibc. Upgrade of the main server solves the issue.

Dexterite
  • 113
  • 1
  • 4