Re: Strangeness with UNIQUE indexes and UTF-8

Hmmm.

Is it possible that in some version of 11.x, the corrupt index stopped
"working"? As in, yes, it may have been corrupt but still returned
data on version 11.y, whereas on version 11.z it's no longer working
and returns nothing?

David mentions that change in 11.11...?

I guess I can try some older versions of 11.x on this cluster for
completeness' sake.

Regards,
Omar

On Sun, Jun 6, 2021 at 8:18 AM 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.
>
> 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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >
> > Omar Kilani <omar(dot)kilani(at)gmail(dot)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