Re: Index corruption revealed after upgrade to 11.17, could date back to at least 11.12

On 10/28/22 16:24, Peter J. Holzer wrote:
> On 2022-10-28 17:23:02 +0300, Kristjan Mustkivi wrote:
>> By the way, index rebuild while completing successfully did not fix
>> the indexes - the data in the tables was still missing even after the
>> successful rebuild command.
>
> This is surprising. As I understand it, REINDEX scans the complete table
> and builds a new index. So after a REINDEX the index should be complete
> and usable.
>
> However, reading
> https://www.postgresql.org/docs/current/sql-reindex.html I'm unsure what
> happens if you have issued a REINDEX for a table, schema, etc. and
> rebuilding an index fails: Does it abort at that time or does it just
> issue a notice and continue to rebuild the other indexes? In the latter
> case it migh be easy to miss a problem.

Debian 9 appears to be glibc 2.24 and Debian 11 is glibc 2.31. Therefore
the issue here is almost certainly corrupt indexes due to the glibc changes.

Note that some forms of the problem can manifest in primary keys or
unique indexes that were valid in the original collation behavior, but
contained duplicate rows according to the new collation. Those indexes
would not be able to be reindexed without manual intervention.

I don't know offhand what happens if you concurrently reindex an entire
database and just a few indexes fail to rebuild, but I suspect as you
mentioned above the failure might easily be missed and invalid indexes
would be left behind. I think you will find both indisready and
indisvalid false for these indexes.

HTH,

--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com