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.
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.
I'm not saying the index isn't corrupt as in something's not wrong
with it. I'm saying that during normal Postgres operation the index
has somehow got itself into this state, and I'm fairly sure it's not
the hardware.
Thanks again.
Regards,
Omar
On Sun, Jun 6, 2021 at 8:08 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Omar Kilani <omar.kilani@gmail.com> writes:
> > This is a very old database (2004) that has moved forward via pg_upgrade. I
> > think we did a pg_dump and pg_restore every time we hit some major
> > incompatibility like float vs integer date times.
>
> If it's that old, it's likely also survived multiple OS upgrades.
> It seems clear that this index has been corrupt for awhile, and
> I'm wondering whether the corruption was brought on by an OS
> locale change. There's useful info at
>
> https://wiki.postgresql.org/wiki/Locale_data_changes
>
> regards, tom lane