Re: BUG #19911: Window aggregate over ROWS n FOLLOWING: "unexpected end of tuplestore" after spill

From: Henson Choi <assam258(at)gmail(dot)com>
To: tharakan(at)gmail(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #19911: Window aggregate over ROWS n FOLLOWING: "unexpected end of tuplestore" after spill
Date: 2026-11-18 01:05:20
Message-ID: CAAAe_zCRzOjsr2y_WG149sY52xusNo2KuLQGgw0vta0uMvVpmw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Subject: Re: BUG #19911: Window aggregate over ROWS n FOLLOWING:
"unexpected end of tuplestore" after spill

Hi Robins,

Thanks for the report. I can reproduce it on master, and the attached
patch fixes it. Below are the cause and fix, why this kind of bug
keeps turning up in WindowAgg, and two follow-up ideas for master.

1. Cause and fix

On the first row, WindowAgg reads rows up to the frame head, 2000 rows
ahead. The tuplestore spills to disk partway through, but the read
still stops there, so the frame head is the next row, not yet stored.
Moving the aggregate's mark to the frame head then leaves its read
pointer at the end of the stored rows, and still selected.

The next fetch first reads the rest of the partition, as it always
does once the store has spilled. A selected pointer at the end moves
along with the rows being written, so the pointer ends up two rows
further on, while the row WindowAgg remembers for it stays the same.
The next move is computed from that stale row and runs off the end.

The patch takes the pointer's row from the number of stored rows
whenever the pointer is at the end, before computing the move.

2. Why this keeps happening, and a mitigation

tuplestore has many users besides WindowAgg: Material, CTE scan,
recursive union and worktable scan, function and table function scans,
SRFs in materialize mode (SQL functions, PL/pgSQL RETURN NEXT, PL/Perl
and PL/Tcl return_next), portals and holdable cursors, trigger
transition tables, hash join and walreceiver queries, among others.
Its read pointers only move relative to where they are (forward or
back, skip, rewind); nothing fetches row N directly.

A window clause also needs random access: row N of the frame, a mark
that moves ahead. WindowAgg emulates it by remembering which row each
window function's and aggregate's pointer is on, while rows are added
to the store on demand from many places, both before reads and in
checks that only ask whether a row exists. Adding rows moves the
selected pointer sitting at the end, but neither side knows about the
other, which is what broke here. This makes the current approach
fragile to maintain.

As a mitigation, the code that manages these positions and limits
could move into a layer between the WindowAgg executor and tuplestore
that offers, in effect, seek by row number on top of the relative
moves: fetch a row, check whether row N or the end of the partition
exists, set a mark, make sure rows up to N are stored, and drop rows
no longer needed, plus pointer setup and partition reset. It would
sit on the existing tuplestore and is meant to behave the same. With
the bookkeeping in one place, including keeping positions right while
rows are added, this kind of defect should be easier to avoid.

3. Performance

Once the layer exists, the storage under it can change without
touching the executor. It could become a store dedicated to the
WindowAgg executor, in which each of several read pointers keeps its
own window of rows in memory and blocks are indexed by row number.

Today, after a spill every read pointer shares one temporary file with
a one-block buffer, and old rows are only dropped while the store is
in memory. Tuples are written to the file one after another with
varying lengths, and nothing maps a row number to a file offset, so
the only way to reach a row N rows away is to read every tuple in
between. When reads jump between distant positions, each row can mean
re-reading the file between them. Cases that do this:

- an aggregate over a long moving frame, whose read pointer walks back
to the frame head for every row
- a frame that starts at an offset FOLLOWING, as in this report
- lead() or lag() with a large offset
- first_value() or nth_value() over a long frame, whose pointer sits
at the frame head while the current row is read at the tail

With blocks indexed by row number, a jump becomes a direct read
instead of a walk, and with a window per pointer, alternating pointers
stop evicting each other's block. In memory, the store would keep
only those windows rather than everything from the oldest pointer to
the end.

The row pattern recognition patch under review adds navigation
operations in DEFINE that read rows near the row being tested (PREV,
NEXT, LAST) or near the match start (FIRST), which can sit far from
the row being tested. They add a read pointer whose fetches jump
between rows far apart, so they would gain from the same store.

The patch is for all supported branches. The ideas in sections 2 and
3 are master-only, and I would start a separate -hackers thread for
them if there is interest.

Regards,
Henson

Attachment Content-Type Size
v1-0001-Fix-stale-read-position-in-window_gettupleslot-af.patch application/x-patch 3.3 KB

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Tender Wang 2026-11-18 01:42:41 Re: BUG #19912: MERGE NOT MATCHED BY SOURCE sees stale source values after concurrent update (EPQ)
Previous Message David Rowley 2026-11-17 23:54:23 Re: PG 18 JIT regression: int4range hash breaks multi-key GROUP BY