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

Hi Allan,

We use dockerized postgres. So the upgrade is just replacing the old
minor version pg docker image with new minor version docker image and
that's it. Of course, I am checking the release notes to see if there
is anything to pay attention to particularly.

I do apologize, but I do not understand the value of doing that select
juggling. I have been searching for and fixing the problematic rows
with ctid (and xmin, xmax  to help establish the ones to remove) and
this has been effective in indicating the discrepancy between actual
data in the table and the corruption in the e.g Primary Key index.
Also, playing around with enable_indexscan, enable_bitmapscan,
enable_seqscan prove the same problem with an index. After deleting
the offending rows by ctid, REINDEX-ing is possible. (As these are
production systems, some of the relations had to be fixed right away.)

This case is most likely to do with some bug and the release notes for
11.14, .15 and .16 seem to explain the current situation. What would
be good to know is that this particular bug has been fixed and is not
one yet to be uncovered.

Best regards,

Kristjan

On Thu, Oct 27, 2022 at 10:41 AM Allan Kamau <kamauallan@gmail.com> wrote:
>
>
>
> On Thu, Oct 27, 2022 at 10:26 AM Allan Kamau <kamauallan@gmail.com> wrote:
>>
>>
>>
>> On Thu, Oct 27, 2022 at 10:20 AM Kristjan Mustkivi <sonicmonkey@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.mustkivi@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
newtable. Then perform queries on this table to test for uniqueness of the column on which the problematic index was
reported.
>>
>> Allan.
>
>
> How was the data "transfer" between upgrades done? Was it by dump and restore?
> If you have the 11.15 instance running having the data, you may do the selection of the row id and the specific
columnwhich the index is based into a new table and perform queries on this too to determine uniqueness of the values
therein.Likewise do the same for the 11.17 version. 
>
> Is it possible to build and install PG 15 from source on a different directory (using --prefix ) then perform pg_dump
usingthe binaries of this installation into a directory. Then configure PG 15 installation to listen on a different
TCP/IPport to the one you are currently using with 11.17 instance. Once started, test to see if the index anomaly is
presentin the PG 15 instance. Alternatively you may use the PG 15 docker image and docker to start a PG 15 docker
containerfor your tests instead of having to build and install PG 15 for this test. 
>
> -Allan
>
>
>


--
Kristjan Mustkivi

Email: kristjan.mustkivi@gmail.com