Re: BUG #17361: Unique index constraint inconsistence

Thank you very much. It looks like this is exactly my problem.
My database is very old, and was created many years ago.
OS upgrades were twice ubuntu 16 -> 18 -> 20.
Database also upgraded twice 9.x -> 10.x -> 12.x.

If I understand correctly, will the 'REINEX SYSTEM' command help to avoid
such problems in the future OS upgrades?

вт, 11 янв. 2022 г. в 03:08, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> > Indexes:
> > "parts_stock_stat_pkey" PRIMARY KEY, btree (id)
> > "parts_stock_stat_lower_stock_type_id_idx" UNIQUE, btree
> (lower(name),
> > stock_type_id)
>
> > Following reindex command failed:
> > db=# reindex table parts_stock_stat;
> > ERROR: could not create unique index
> > "parts_stock_stat_lower_stock_type_id_idx"
> > DETAIL: Key (lower(name), stock_type_id)=(ha_hr/50, 4) is duplicated.
>
> How long have you had this database? If it's older than your last
> OS upgrade, it's possible that the index has become corrupt as a
> result of OS-level collation changes. See
>
> https://wiki.postgresql.org/wiki/Locale_data_changes
>
> regards, tom lane
>