Re: Strangeness with UNIQUE indexes and UTF-8

On Sun, Jun 6, 2021 at 5:19 PM Omar Kilani <omar(dot)kilani(at)gmail(dot)com> wrote:
>
> Hey Tom,
>
> The database was pg_dump'ed out of 10.4 and pg_restore'd into 11.2 on
> a RHEL 7.x machine.
>
> The only other upgrade has been to RHEL 8.x. So the locale data change
> might have changed something -- thanks for that information.

There is no might -- if you upgraded from RHEL 7 to RHEL 8 without
doing a reindex or a dump/reload there, you are pretty much guaranteed
to have corrupt text indexes from that. Regardless of PostgreSQL
versions, this was about the RHEL upgrade not the Postgres one.

> We've seen this issue on a different table before upgrading to RHEL
> 8.x, though. And only on that table, because it's user-generated and
> gets a bunch of Unicode data input into a UNIQUE index.

This indicates you may have more than one problem.

But that doesn't mean it's not both, sadly.

--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/