MIME-Version: 1.0
References: <CAPo1J60Vcu+5G0EvvAZtYgTn6U6ADij3aVJ8WFVz77jP+Bd_Tw@mail.gmail.com>
 <01df0ca4-cf32-448f-a2cb-e44a8e9f7fd6@element.io> <CANzqJaAW2kwAyeJzboUCsbCpCk9ERDF1aZrW9OEa+DSqpPQxjA@mail.gmail.com>
In-Reply-To: <CANzqJaAW2kwAyeJzboUCsbCpCk9ERDF1aZrW9OEa+DSqpPQxjA@mail.gmail.com>
From: Erik Johnston <erikj@element.io>
Date: Fri, 4 Jul 2025 15:59:55 +0100
Message-ID: <CAPo1J60aoiwv=1HViWSiFEx9vpoj69cOfujCJRhy4Zjyw+QFaw@mail.gmail.com>
Subject: Re: Corrupt btree index includes rows that don't match
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000fe5f6706391bbf1b"
Archived-At: <https://www.postgresql.org/message-id/CAPo1J60aoiwv%3D1HViWSiFEx9vpoj69cOfujCJRhy4Zjyw%2BQFaw%40mail.gmail.com>
Precedence: bulk

--000000000000fe5f6706391bbf1b
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Fri, 4 Jul 2025, 15:38 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:

> On Fri, Jul 4, 2025 at 9:49=E2=80=AFAM Erik Johnston <erikj@element.io> w=
rote:
>
>> Hi, a quick update:
>>
>> - We have discovered that the corruption was present from before libicu
>> update.
>> - We ran `pg_amcheck --index state_groups_state_type_idx --heapallindexe=
d
>> matrix`, which returned nothing
>> - We believe that means that (and matches what we see sampling) the inde=
x
>> has gained extra entries, i.e. that for a given state group it does retu=
rn
>> all the relevant rows in the table *plus* extra rows.
>>
>> We are also seeing old state groups starting to point at rows that have
>> only just been inserted. For example, querying for 353864583 on the prim=
ary
>> it returns that row plus four rows that have been inserted today, but on
>> the backup from last week an index only scan for 353864583 only returns =
one
>> row. This makes it feel like the corruption is ongoing? Nothing should h=
ave
>> modified that state group in the interim (they are generally immutable).
>>
>> This naively feels like when inserting a new row we sometimes add the ro=
w
>> to the index twice: once pointing from the correct state group to the ne=
w
>> row, and once from an old state group to the new row?
>>
>>
> Are checksums enabled in the instance?
>

Alas not.

We've also now found that the index on the backup does in fact point to
those ctids after all, but they are marked as dead. So at some point
between then and when we inserted the new row at that ctid today those
entries were marked undead.

--=20


Copyright =C2=A9 2025 Element - All rights reserved. The Element name, logo=
=20
and device are registered trademarks of New Vector Ltd. Registered number:=
=20
10873661. Registered in England and Wales. Registered address: 10 Queen=20
Street Place, London, United Kingdom, EC4R 1AG.

This message is intended=20
for the addressee only and may contain private and confidential information=
=20
or material which may be privileged. If this message has come to you in=20
error please delete it immediately and do not copy it or show it to any=20
other person.

--000000000000fe5f6706391bbf1b
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"auto"><br><br><div class=3D"gmail_quote" dir=3D"auto"><div dir=
=3D"ltr" class=3D"gmail_attr">On Fri, 4 Jul 2025, 15:38 Ron Johnson, &lt;<a=
 href=3D"mailto:ronljohnsonjr@gmail.com" rel=3D"noreferrer noreferrer" targ=
et=3D"_blank">ronljohnsonjr@gmail.com</a>&gt; wrote:<br></div><blockquote c=
lass=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;=
padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr">On Fri, Jul 4, 2025 at =
9:49=E2=80=AFAM Erik Johnston &lt;<a href=3D"mailto:erikj@element.io" rel=
=3D"noreferrer noreferrer noreferrer" target=3D"_blank">erikj@element.io</a=
>&gt; wrote:</div><div class=3D"gmail_quote"><blockquote class=3D"gmail_quo=
te" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204=
);padding-left:1ex"><u></u>

 =20
   =20
 =20
  <div>
    <p>Hi, a quick update:</p>
    <p>- We have discovered that the corruption was present from before
      libicu update.<br>
      - We ran `pg_amcheck --index state_groups_state_type_idx
      --heapallindexed matrix`, which returned nothing<br>
      - We believe that means that (and matches what we see sampling)
      the index has gained extra entries, i.e. that for a given state
      group it does return all the relevant rows in the table <i>plus</i>
      extra rows.<br>
    </p>
    <p>We are also seeing old state groups starting to point at rows
      that have only just been inserted. For example, querying for
      353864583 on the primary it returns that row plus four rows that
      have been inserted today, but on the backup from last week an
      index only scan for 353864583 only returns one row. This makes it
      feel like the corruption is ongoing? Nothing should have modified
      that state group in the interim (they are generally immutable).</p>
    <p>This naively feels like when inserting a new row we sometimes add
      the row to the index twice: once pointing from the correct state
      group to the new row, and once from an old state group to the new
      row?<br>
    </p>
    <p></p></div></blockquote></div><div><br></div><div>Are checksums enabl=
ed in=C2=A0the instance?</div><div></div></div></blockquote></div><div dir=
=3D"auto"><br></div><div dir=3D"auto">Alas not.=C2=A0</div><div dir=3D"auto=
"><br></div><div dir=3D"auto">We&#39;ve also now found that the index on th=
e backup does in fact point to those ctids after all, but they are marked a=
s dead. So at some point between then and when we inserted the new row at t=
hat ctid today those entries were marked undead.=C2=A0</div></div>

<br>
<p dir=3D"ltr"><span style=3D"color:gray"><font size=3D"1"><span style=3D"f=
ont-family:Verdana"><span style=3D"font-weight:400;font-style:normal;text-d=
ecoration:none">Copyright =C2=A9 2025 Element - All rights reserved. The El=
ement name, logo and device are registered trademarks of New Vector Ltd. Re=
gistered number: 10873661. Registered in England and Wales. Registered addr=
ess: 10 Queen Street Place, London, United Kingdom, EC4R 1AG.</span></span>=
</font></span></p><p dir=3D"ltr"><span style=3D"color:gray"><font size=3D"1=
"><span style=3D"font-family:Verdana"><span style=3D"font-weight:400;font-s=
tyle:normal;text-decoration:none">This message is intended for the addresse=
e only and may contain private and confidential information or material whi=
ch may be privileged. If this message has come to you in error please delet=
e it immediately and do not copy it or show it to any other person.</span><=
/span></font></span></p>
--000000000000fe5f6706391bbf1b--