Re: TRAP: failed Assert("offsets[i] > offsets[i - 1]"), File: "tidstore.c"

On Tue, Apr 28, 2026 at 10:09 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> On Sun, Apr 26, 2026 at 2:06 AM Andrei Lepikhov <lepihov(at)gmail(dot)com> wrote:
> >
> > On 25/04/2026 01:23, Masahiko Sawada wrote:
> > > On Wed, Apr 22, 2026 at 10:23 AM Andrei Lepikhov <lepihov(at)gmail(dot)com> wrote:
> > >> Both tools are experimental and not meant for core use; they are only used to
> > >> trigger potential issues. In this case, I think the query picked a costly sorted
> > >> path, which led to the crash.
> > >
> > > Does this imply that array_agg() could return unsorted results
> > > depending on the plan the optimizer chooses? Or is such a path
> > > currently never selected by the optimizer?
> >
> > The array_agg() function does not sort its output. In theory, this means the
> > join could return results in any order, but in practice, I have not seen this
> > happen.
> >
> > >
> > > I’m asking because if this scenario never occurs with the current
> > > optimizer, it might make sense to apply the patch only to HEAD (i.e.,
> > > for PG20). On the other hand, backpatching to PG17 might be justified,
> > > given that DISTINCT does not guarantee sorted results in principle,
> > > and the fix could benefit extension development on stable branches.
> >
> > In stable versions, the planner's logic remains unchanged. So, it seems
> > reliable. However, backpatching could help extension developers a little bit.
> > Since this code fixes a real issue and does not break anything complex, I would
> > backpatch it. Still, I am fine with just committing it to master if you prefer.
> >
> > P.S.
> >
> > I looked into the issue further. The problem happens when the join sides are
> > shuffled. Here is what I found:
> >
> > EXPLAIN of the successful execution (unnecessary details stripped):
> >
> > Insert on pg_temp.hideblocks (cost=1.21..1.66 rows=0 width=0)
> > -> Subquery Scan on unnamed_subquery (cost=1.21..1.66 rows=5 width=8)
> > Output: unnamed_subquery.do_set_block_offsets
> > -> GroupAggregate (cost=1.21..1.61 rows=5 width=16)
> > Output: do_set_block_offsets("*VALUES*".column1,
> > (array_agg("*VALUES*_1".column1))::smallint[]), ...
> > Group Key: "*VALUES*".column1
> > -> Sort (cost=1.21..1.27 rows=25 width=12)
> > Output: "*VALUES*".column1, "*VALUES*_1".column1
> > Sort Key: "*VALUES*".column1
> > -> Nested Loop (cost=0.00..0.62 rows=25 width=12)
> > Output: "*VALUES*".column1, "*VALUES*_1".column1
> > -> Values Scan on "*VALUES*"
> > (cost=0.00..0.06 rows=5 width=8)
> > Output: "*VALUES*".column1
> > -> Values Scan on "*VALUES*_1"
> > (cost=0.00..0.06 rows=5 width=4)
> > Output: "*VALUES*_1".column1
> >
> > EXPLAIN that causes assertion:
> >
> > Insert on pg_temp.hideblocks (cost=1.03..1.48 rows=0 width=0)
> > -> Subquery Scan on unnamed_subquery (cost=1.03..1.48 rows=5 width=8)
> > Output: unnamed_subquery.do_set_block_offsets
> > -> GroupAggregate (cost=1.03..1.43 rows=5 width=16)
> > Output: do_set_block_offsets("*VALUES*".column1,
> > (array_agg("*VALUES*_1".column1))::smallint[]),...
> > Group Key: "*VALUES*".column1
> > -> Sort (cost=1.03..1.09 rows=25 width=12)
> > Output: "*VALUES*".column1, "*VALUES*_1".column1
> > Sort Key: "*VALUES*".column1
> > -> Nested Loop (cost=0.00..0.45 rows=25 width=12)
> > Output: "*VALUES*".column1, "*VALUES*_1".column1
> > -> Values Scan on "*VALUES*_1"
> > (cost=0.00..0.06 rows=5 width=4)
> > Output: "*VALUES*_1".column1
> > -> Materialize (cost=0.00..0.09 rows=5 width=8)
> > Output: "*VALUES*".column1
> > -> Values Scan on "*VALUES*"
> > (cost=0.00..0.06 rows=5 width=8)
> > Output: "*VALUES*".column1
> >
> > At the second case offsets have come to the aggregation without order that
> > highlighted the issue.
>
> Thank you for sharing the details.
>
> While the assertion failure is not observed during regular regression
> tests because the query is simple enough that the optimizer
> consistently chooses plans producing the sorted results, given that
> the DISTINCT without the ORDER BY doesn't guarantee to produce the
> sorted results in theory, I think it makes sense to apply the proposed
> patch. And, it would also make sense to backpatch to PG17, where
> tid_store was introduced, for extension development on back branches.
>
> I've attached the patches. I'm going to push them, barring any objections.
>

Pushed.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com