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

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.

Regards,

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