Subject: [SP-]GiST IOS visibility bug (was: Why doens't GiST require super-exclusive lock)

From: Matthias van de Meent <[email-masked]>
Date: Fri, 25 Apr 2025 23:13:14 +0200

Hi,

In the original thread [0] we established that GIST and SP-GiST
support index-only scans, but don't have sufficient interlocking to
prevent dead tuples from being revived for their scan by vacuum. As
that thread already contains a lot of detail, I won't go into the
specifics here, but the gist (heh) of it is as follows:

(SP-)GiST index scans don't interlock with their index vacuum in a way
that guarantees that their index-only scans only return TIDs that are
known to be valid TIDs when they're returned by amgettuple, thus
allowing index vacuum to clean TIDs up and let VACUUM mark their pages
ALL_VISIBLE before the tids were returned by the index-only scan.

The keen-eyed reader may have noticed that this issue is very similar
to the issue fixed with 459e7bf8, and that's correct. Both are cases
where the visibility map was accessed to determine the visibility of a
TID of which we didn't know for sure it was still a valid TID on that
heap page.

While the thread in [0] contains a very thorough fix, and (IMO) some
good additions to how index-only scans work with table AMs, the patch
currently proposed there changes things in non-backpatchable ways. So,
attached is a set of 4 patches designed for backpatching a fix.

The patches work similar to [0]'s, but with less invasive code changes
and no meaningful ABI breaks:
index vacuum's lock requirements are increased to super-exclusive
(cleanup) locks. Additionally, index-only scans will now do visibility
checks of each tuple they want to return, before the pin on the index
page that contains those tuples is released.
The visibility check is done on a tuple-by-tuple basis, through a new
function table_index_vischeck_tuple (so as to not totally break the
semantic barrier between indexes and tables regarding visibility
checks). In the patchset of [0] I added a table AM routine to allow
tableAMs to extend this, but that's not backportable, hence this
separate thread for discussing a backpatchable bugfix.
The one exposed ABI change is done by adding a single-byte
"visrecheck" field in an alignment area in IndexScanDescData - which
historically has been the place to put new fields required for
backported bugfixes. The size of the struct doesn't change, and there
should be no backward or forward compatibility issues for extensions
that might make use of this new field as long as they don't assume the
value of the field when they haven't set it themselves.

Kind regards,

Matthias van de Meent
Neon (https://neon.tech)

[0] https://postgr.es/m/flat/CAH2-Wz=PqOziyRSrnN5jAtfXWXY7-BJcHz9S355LH8Dt=[email-masked]

---

From: Álvaro Herrera <[email-masked]>
Date: Tue, 3 Feb 2026 23:39:20 +0100

On 2025-Apr-25, Matthias van de Meent wrote:

> prevent dead tuples from being revived for their scan by vacuum.

Hello,

This commitfest entry
https://commitfest.postgresql.org/patch/5721/
is stale.  I would have brought it forward because it's tagged as a bug
fix, but the patches look a bit intimidating to consider it a
backpatchable bug fix.  So, what's the status here?

Thanks

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/

---

From: Matthias van de Meent <[email-masked]>
Date: Fri, 5 Jun 2026 10:28:12 +0200

> backpatchable bug fix.  So, what's the status here?

Sorry for the delay. I've finally gotten back to this patch, as it's
the simpler option vs the patch in the original thread, even if it may
have a higher performance impact for GIST and SP-GIST IOS scans.

Attached is v02, which is a simple rebase with some language and
wrapping changes in comments, as well as some fixes for assert-enabled
builds.

Kind regards,

Matthias van de Meent
Databricks (https://www.databricks.com)