Re: pg_upgrading to 10.1 corrupts (varchar,varchar) UNIQUE indices

rihad wrote:
> On 12/22/2017 05:09 PM, Laurenz Albe wrote:
> > rihad wrote:
> > > Hi there, this is a reproducible error. We recently pg_upgraded our
> > > production database to 10.1 from 9.6.6. The upgrade runs fine with the
> > > suggestion to analyze all data.
> >
> > [...]
> >
> > > Trying to find data using the specified indices fails to find matching rows:
> >
> > [...]
> >
> > > reindexing the table fixes the issue:
> >
> > Did you also upgrade the operating system or at least libc?
>
> It looks like it.
>
> $ ll /lib/libc.so.7
> -r--r--r-- 1 root wheel 1647104 Jun 19 2017 /lib/libc.so.7
>
> this never was a major/minor OS upgrade, just whatever FreeBSD patch
> releases included.
>
> > Then the problem could come from a modified collation.
> > Compare https://www.postgresql.org/message-id/CB4D1C6BAA80CF146CB0D4F2%40eje.credativ.lan
>
> de (German) was just an example, the inability to find matching rows
> affected other languages like fr or even en, too.
> It does look like a collation issue, though.

That would be the best thing, because it would mean that there is no
PostgreSQL bug or hardware problem, and REINDEX will fix the problem.

Yours,
Laurenz Albe