Re: TID recycling race during nbtree index-only scans that run on a standby

On Mon, Jun 22, 2026 at 7:38 AM Matthias van de Meent
<boekewurm+postgres@gmail.com> wrote:
> On Thu, 18 Jun 2026 at 00:17, Peter Geoghegan <pg@bowt.ie> wrote:
> All that, for queries active on that standby, right? This isn't an
> issue about the primary, but rather one about WAL replay not currently
> providing the right interlocks?

Yes, it is. The old pin scan code made hot standby follow the same
locking protocol as the primary; we no longer do that, which leaves
these gaps.

> I have a few thoughts:
>
> There are two bugs here, both arriving from the same issue of removing
> dead items from the page without pin interlock:
> 1. page splits move cached dead items away from the page, allowing
> them to be cleaned up from another page without the original page
> (which may still have pins) being involved, and
> 2. replay of XLOG_BTREE_DELETE doesn't take a cleanup lock (which
> provides the "pin interlock") on the page.

FWIW I don't think defining this as two bugs is useful.

> For the second case, obviously, we should use a cleanup lock for
> XLOG_BTREE_DELETE.
> For the first case, this probably also would be handled if we took a
> cleanup lock on the original page during replay - the operations is
> removing tuples from that page, and we don't yet know if they're going
> to be recycled soon (though that is unlikely given page split's
> efforts to avoid keeping dead tuples on the page, it is possible). See
> attached (on top of your v1-0001).
>
> Alternatively, we could hold normal read locks on the page on
> replica's IOS while the scan needs that page. That'd risk getting
> really bad performance in the normal case, though, where page
> splits/cleanup happen much more rarely than insertions; while those
> inserts also require interlocked access with page reads.

Honestly, none of these options seem appealing. I don't have a better
idea, either.  :-(

> PS. This once again reminds me, that with all the new buffer pin
> -holding systems in AIO we probably should have a 'hurry up; I need to
> access this buffer and you're blocking my very important job' signal
> system.  Cursors (and other queries) that hold on to IOS' required
> pins only because it decided VM checks should happen very lazily
> should then be able to run those VM checks immediately on receiving
> that signal, instead of having to wait for the query state to continue
> on past the current page.

I've sometimes thought that a system like that could make sense for
aggressive VACUUMs that block indefinitely on a heap pin.

With the amgetbatch interface, this usually wouldn't make a difference
because the cursor scan would already be at the point where it eagerly
drops every index leaf page (and eagerly fills each page's/batch's
visibility info from the visibility map to make that safe). You'd have
to be quite unlucky for it to still happen.

-- 
Peter Geoghegan