'Equal sign (=) and LIKE return different results on main and replica postgresql

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.

sqlpostgresql-13


Solution 1:[1]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Dexterite

Related Questions