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

On Thu, Oct 27, 2022 at 10:20 AM Kristjan Mustkivi <sonicmonkey(at)gmail(dot)com>
wrote:

> Dear community,
>
> Right after upgrading our postgres servers from 11.15 to 11.17 we
> started to encounter problems with data. Namely, when the query hit
> the index, it returned a single row; when the query hit a relation
> directly, it returned more than one row. Attempt to REINDEX revealed
> the underlying data had duplicates (unique index reindexing failed).
>
> Version facts:
> we started out with 11.12
> jan 2022 upgraded to 11.14
> mar 2022 to 11.15
> oct 2022 to 11.17
>
> We are not sure when this corruption actually happened. Could it be
> related to the indexing bugs reported in
> https://www.postgresql.org/docs/release/11.14/? And the condition only
> became known to us after 11.17 rollout which can perhaps be explained
> by the following: while 11.17 does not have any outstanding index
> related fixes, then https://www.postgresql.org/docs/release/11.15/
> mentions fix for index-only scans and so does
> https://www.postgresql.org/docs/release/11.16/.
>
> The bottom line is we would like to understand if the index corruption
> and its manifestation is explained by the above release fixes or is
> there something else that should be investigated further here with the
> help from the community.
>
> With best regards,
> --
> Kristjan Mustkivi
>
> Email: kristjan(dot)mustkivi(at)gmail(dot)com
>
>
> Hi Kristjan,
What if you construct a select statement containing the row id and the
column which has the problematic index into a new table. Then perform
queries on this table to test for uniqueness of the column on which the
problematic index was reported.

Allan.