From: Tom Lane <tgl@sss.pgh.pa.us>
To: Erik Johnston <erikj@element.io>
cc: pgsql-general@lists.postgresql.org
Subject: Re: Corrupt btree index includes rows that don't match
In-reply-to: <CAPo1J60aoiwv=1HViWSiFEx9vpoj69cOfujCJRhy4Zjyw+QFaw@mail.gmail.com>
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>
Comments: In-reply-to Erik Johnston <erikj@element.io>
	message dated "Fri, 04 Jul 2025 15:59:55 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <250319.1751643701.1@sss.pgh.pa.us>
Date: Fri, 04 Jul 2025 11:41:41 -0400
Message-ID: <250320.1751643701@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/250320.1751643701%40sss.pgh.pa.us>
Precedence: bulk

Erik Johnston <erikj@element.io> writes:
> 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.

I wonder if this behavior could be related to this 14.18 fix:

https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=4934d3875

It probably isn't, because AFAICS that would only lead to transiently
wrong answers --- but maybe you have a workload that occasionally hits
that bug in the context of a query that will update and re-insert the
recently-dead tuples?  Still a bit far-fetched though, and if the
index is actually corrupt this doesn't explain how it got that way.

I'm more inclined to just say "once a btree index is out of order it
can do some very strange things, both during inserts and searches".
If you had some evidence about when and how the index became corrupt,
it'd be worth studying that, but it sounds like you don't.

			regards, tom lane