String comparison fails for some cases after migration
Hello all,
We moved our PostgreSQL database from one hosting provider to another using
pgbackrest. In the new environment, some comparison operations were
failing. The issue was fixed by running an update. But I am trying to find
out what would have happened.
select * from accounts where email = 'someemail@gmail.com'; -- failed for
some email ids even though there were records.
select * from accounts where lower(trim(email)) <> email; -- fetched no
records.
select * from accounts where email::bytea = 'someemail@gmail.com'::bytea; -
worked for those records where comparison was failing.
update accounts set email = trim(lower(email)); -- fixed the issue.
Source database was PG 13.2, target 13.3.
Any suggestions will be appreciated - not sure if other tables/columns are
affected.
Best Regards,
Jayadevan
Hi,
On Wed, Oct 27, 2021 at 2:12 PM Jayadevan M <maymala.jayadevan@gmail.com> wrote:
We moved our PostgreSQL database from one hosting provider to another using pgbackrest. In the new environment, some comparison operations were failing. The issue was fixed by running an update. But I am trying to find out what would have happened.
select * from accounts where email = 'someemail@gmail.com'; -- failed for some email ids even though there were records.
select * from accounts where lower(trim(email)) <> email; -- fetched no records.
select * from accounts where email::bytea = 'someemail@gmail.com'::bytea; - worked for those records where comparison was failing.
update accounts set email = trim(lower(email)); -- fixed the issue.
Source database was PG 13.2, target 13.3.
Any suggestions will be appreciated - not sure if other tables/columns are affected.
Most likely you had a different version of the glibc or ICU libraries
on the new system, which lead to your indexes on collatable datatypes
partially corrupted. See https://wiki.postgresql.org/wiki/Collations
for more details.
You can issue a REINDEX for each impacted index, or a database-wide REINDEX.
Jayadevan M <maymala.jayadevan@gmail.com> writes:
We moved our PostgreSQL database from one hosting provider to another using
pgbackrest. In the new environment, some comparison operations were
failing. The issue was fixed by running an update. But I am trying to find
out what would have happened.
Did the underlying OS version change? If so, see
https://wiki.postgresql.org/wiki/Locale_data_changes
regards, tom lane
On Wed, Oct 27, 2021 at 11:49 AM Julien Rouhaud <rjuju123@gmail.com> wrote:
Most likely you had a different version of the glibc or ICU libraries
on the new system, which lead to your indexes on collatable datatypes
partially corrupted. See https://wiki.postgresql.org/wiki/Collations
for more details.You can issue a REINDEX for each impacted index, or a database-wide
REINDEX.
Tried creating a new env with the same approach - just reindexing solved
the problem. Thanks a lot.
Regards,
Jayadevan