MIME-Version: 1.0
References: <CAPo1J60Vcu+5G0EvvAZtYgTn6U6ADij3aVJ8WFVz77jP+Bd_Tw@mail.gmail.com>
 <01df0ca4-cf32-448f-a2cb-e44a8e9f7fd6@element.io>
In-Reply-To: <01df0ca4-cf32-448f-a2cb-e44a8e9f7fd6@element.io>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Fri, 4 Jul 2025 10:38:31 -0400
Message-ID: <CANzqJaAW2kwAyeJzboUCsbCpCk9ERDF1aZrW9OEa+DSqpPQxjA@mail.gmail.com>
Subject: Re: Corrupt btree index includes rows that don't match
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000005e7eba06391b7342"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaAW2kwAyeJzboUCsbCpCk9ERDF1aZrW9OEa%2BDSqpPQxjA%40mail.gmail.com>
Precedence: bulk

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

On Fri, Jul 4, 2025 at 9:49=E2=80=AFAM Erik Johnston <erikj@element.io> wro=
te:

> 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 --heapallindexed
> matrix`, which returned nothing
> - 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 retur=
n
> 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 prima=
ry
> 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 o=
ne
> row. This makes it feel like the corruption is ongoing? Nothing should ha=
ve
> modified that state group in the interim (they are generally immutable).
>
> 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?
>
>
Are checksums enabled in the instance?

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, Jul 4, 2025 at 9:49=E2=80=AFAM Er=
ik Johnston &lt;<a href=3D"mailto:erikj@element.io">erikj@element.io</a>&gt=
; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockquote =
class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px sol=
id 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><br></div><span class=3D"gmail_signature=
_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=
=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.<div>Don&#39;t boil me=
, I&#39;m still alive.<br><div><div>&lt;Redacted&gt; lobster!</div></div></=
div></div></div></div>

--0000000000005e7eba06391b7342--