MIME-Version: 1.0
References: <CAPo1J60Vcu+5G0EvvAZtYgTn6U6ADij3aVJ8WFVz77jP+Bd_Tw@mail.gmail.com>
 <01df0ca4-cf32-448f-a2cb-e44a8e9f7fd6@element.io> <CANzqJaAW2kwAyeJzboUCsbCpCk9ERDF1aZrW9OEa+DSqpPQxjA@mail.gmail.com>
 <CAPo1J60aoiwv=1HViWSiFEx9vpoj69cOfujCJRhy4Zjyw+QFaw@mail.gmail.com> <b52c4231-cae6-4a74-a614-4961935380ec@element.io>
In-Reply-To: <b52c4231-cae6-4a74-a614-4961935380ec@element.io>
From: Peter Geoghegan <pg@bowt.ie>
Date: Wed, 9 Jul 2025 14:31:25 -0400
Message-ID: <CAH2-WzkbD3+=4NQJ9obiQZ6YfB0PHPwVEsrsOrTz2_5xk9V3Lg@mail.gmail.com>
Subject: Re: Corrupt btree index includes rows that don't match
To: Erik Johnston <erikj@element.io>
Cc: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAH2-WzkbD3%2B%3D4NQJ9obiQZ6YfB0PHPwVEsrsOrTz2_5xk9V3Lg%40mail.gmail.com>
Precedence: bulk

On Wed, Jul 9, 2025 at 1:02=E2=80=AFPM Erik Johnston <erikj@element.io> wro=
te:
> To recap: the situation is that, looking at our backup from 2025-06-26 vi=
a pageinspect, we have btree index rows which point to either non-existent =
heap TIDs, or to heap TIDs with data which does not correspond to the index=
 row. In fact it looks like we have entire index pages which point only to =
non-existent heap TIDs.

This is a generic symptom of corruption. You can see this sort of
thing whenever (say) the storage lies about fsync having flushed
everything to disk. The index might still contain TIDs that point to
heap pages that existed before the crash, that didn't survive crash
recovery. It's quite likely that those same TIDs will be used for
wholly unrelated logical rows when the application inserts a little
more data.

> Empirically, and surprisingly to us, when one does a SELECT from an index=
 entry that points to a non-existent TID, the index entry is quietly ignore=
d.
>
> We therefore suspect that this index corruption has been present for some=
 time (possibly years); more recently those non-existent heap TIDs have bee=
n recycled, and that is when we have noticed the effects of the problem.

That sounds plausible.

--=20
Peter Geoghegan