On Thu, Oct 27, 2022 at 10:20 AM Kristjan Mustkivi <sonicmon...@gmail.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.mustk...@gmail.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.