Horton

Re: Some read stream improvements

Thomas Munro <thomas.munro@gmail.com>

View thread View on postgr.es
From: Thomas Munro <thomas.munro@gmail.com>
To: Andres Freund <andres@anarazel.de>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2025-03-18T07:11:41Z
Lists: pgsql-hackers 
On Tue, Mar 18, 2025 at 5:56 AM Andres Freund <andres@anarazel.de> wrote:
> So one thing is that the pin count differs by 1 at the start of the scan. No
> idea why.
>
> I still don't know what drives the difference between freebsd and the rest,
> but IIUC the reason this fails is just that we are holding too many buffers
> pinned, due to some buffers being pinned outside of read_stream.c.

I couldn't reproduce this on my local FreeBSD box, but I think I see
one part of the problem: the cursor a few lines up has a stream with a
higher distance holding a couple of pins.  Not sure how the local
buffer pool got into a state that caused that if it isn't doing the
same on other machines, but anyway, if I read the test right you
intend to pin strictly one page per cursor, so I tried saying so
explicitly:

-       query = format($q$DECLARE %I CURSOR FOR SELECT ctid FROM
test_temp WHERE ctid >= '( %s, 1)'::tid $q$, cursorname, i);
+       query = format($q$DECLARE %I CURSOR FOR SELECT ctid FROM
test_temp WHERE ctid between '(%s, 1)'::tid and '(%s, 9999)'::tid $q$,
cursorname, i, i);

That passed on the FreeBSD CI task.

Commits

  1. read_stream: Fix overflow hazard with large shared buffers

  2. Support buffer forwarding in StartReadBuffers().

  3. Support buffer forwarding in read_stream.c.

  4. Simplify distance heuristics in read_stream.c.

  5. Improve read_stream.c advice for dense streams.

  6. Respect changing pin limits in read_stream.c.

  7. Improve buffer manager API for backend pin limits.

  8. Remove arbitrary cap on read_stream.c buffer queue.