MIME-Version: 1.0
References: <b97f1850-fc7b-43c4-9b04-4e97bb9e7dc0@gmail.com>
 <CAD21AoBVEcC5stzLr80RgaWuBh0EoyRQys_aeOz0ceogMVREcQ@mail.gmail.com>
 <cb7a4bad-007a-4bbe-8e4b-8ce8109c6e93@gmail.com> <CAD21AoC86nuoxy4r6G3_Ysb2y3K+0sybznRkjVq=Yc4URZUN4g@mail.gmail.com>
 <119bd418-1d7a-42c7-9270-86f3b6696399@gmail.com> <CAD21AoAit-Qp9OriVbb9c_Qebi=Cgxz0AQJu+zxQpp=_1Lt2dQ@mail.gmail.com>
 <ea7bd70e-212d-42ae-9677-72f2f5493f3c@gmail.com> <CAD21AoCmEqiQVgJc34yGK7DSQj-p-zBVrm4-PfrwYHdNkJGt5g@mail.gmail.com>
 <9dece148-3e1d-4b0f-b798-574988fe76fe@gmail.com> <CAD21AoAORy3MmSxPEaRbE_BuwW3qyxsfLGj81YcOtn6gv2iJww@mail.gmail.com>
 <117efd72-5692-4844-bc4e-c553d03d393e@gmail.com> <CAD21AoB-Q=oaDzbk5r-HNHu1E0X8uj2zs62fXkEdLfVcjTkTjA@mail.gmail.com>
In-Reply-To: <CAD21AoB-Q=oaDzbk5r-HNHu1E0X8uj2zs62fXkEdLfVcjTkTjA@mail.gmail.com>
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Wed, 29 Apr 2026 09:11:32 -0700
Message-ID: <CAD21AoAWDL+aSzH7ciaUgOOL=v1WqTOdXCoOe0NCgerX01DL9Q@mail.gmail.com>
Subject: Re: TRAP: failed Assert("offsets[i] > offsets[i - 1]"), File: "tidstore.c"
To: Andrei Lepikhov <lepihov@gmail.com>
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAD21AoAWDL%2BaSzH7ciaUgOOL%3Dv1WqTOdXCoOe0NCgerX01DL9Q%40mail.gmail.com>
Precedence: bulk

On Tue, Apr 28, 2026 at 10:09=E2=80=AFAM Masahiko Sawada <sawada.mshk@gmail=
.com> wrote:
>
> On Sun, Apr 26, 2026 at 2:06=E2=80=AFAM Andrei Lepikhov <lepihov@gmail.co=
m> wrote:
> >
> > On 25/04/2026 01:23, Masahiko Sawada wrote:
> > > On Wed, Apr 22, 2026 at 10:23=E2=80=AFAM Andrei Lepikhov <lepihov@gma=
il.com> wrote:
> > >> Both tools are experimental and not meant for core use; they are onl=
y used to
> > >> trigger potential issues. In this case, I think the query picked a c=
ostly 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 mean=
s the
> > join could return results in any order, but in practice, I have not see=
n this
> > happen.
> >
> > >
> > > I=E2=80=99m asking because if this scenario never occurs with the cur=
rent
> > > 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 littl=
e 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 side=
s are
> > shuffled. Here is what I found:
> >
> > EXPLAIN of the successful execution (unnecessary details stripped):
> >
> > Insert on pg_temp.hideblocks  (cost=3D1.21..1.66 rows=3D0 width=3D0)
> >    ->  Subquery Scan on unnamed_subquery  (cost=3D1.21..1.66 rows=3D5 w=
idth=3D8)
> >          Output: unnamed_subquery.do_set_block_offsets
> >          ->  GroupAggregate  (cost=3D1.21..1.61 rows=3D5 width=3D16)
> >                Output: do_set_block_offsets("*VALUES*".column1,
> >                         (array_agg("*VALUES*_1".column1))::smallint[]),=
 ...
> >                Group Key: "*VALUES*".column1
> >                ->  Sort  (cost=3D1.21..1.27 rows=3D25 width=3D12)
> >                      Output: "*VALUES*".column1, "*VALUES*_1".column1
> >                      Sort Key: "*VALUES*".column1
> >                      ->  Nested Loop  (cost=3D0.00..0.62 rows=3D25 widt=
h=3D12)
> >                            Output: "*VALUES*".column1, "*VALUES*_1".col=
umn1
> >                            ->  Values Scan on "*VALUES*"
> >                                 (cost=3D0.00..0.06 rows=3D5 width=3D8)
> >                                  Output: "*VALUES*".column1
> >                            ->  Values Scan on "*VALUES*_1"
> >                                 (cost=3D0.00..0.06 rows=3D5 width=3D4)
> >                                  Output: "*VALUES*_1".column1
> >
> > EXPLAIN that causes assertion:
> >
> >  Insert on pg_temp.hideblocks  (cost=3D1.03..1.48 rows=3D0 width=3D0)
> >    ->  Subquery Scan on unnamed_subquery  (cost=3D1.03..1.48 rows=3D5 w=
idth=3D8)
> >          Output: unnamed_subquery.do_set_block_offsets
> >          ->  GroupAggregate  (cost=3D1.03..1.43 rows=3D5 width=3D16)
> >                Output: do_set_block_offsets("*VALUES*".column1,
> >                         (array_agg("*VALUES*_1".column1))::smallint[]),=
...
> >                Group Key: "*VALUES*".column1
> >                ->  Sort  (cost=3D1.03..1.09 rows=3D25 width=3D12)
> >                      Output: "*VALUES*".column1, "*VALUES*_1".column1
> >                      Sort Key: "*VALUES*".column1
> >                      ->  Nested Loop  (cost=3D0.00..0.45 rows=3D25 widt=
h=3D12)
> >                            Output: "*VALUES*".column1, "*VALUES*_1".col=
umn1
> >                            ->  Values Scan on "*VALUES*_1"
> >                                 (cost=3D0.00..0.06 rows=3D5 width=3D4)
> >                                  Output: "*VALUES*_1".column1
> >                            ->  Materialize  (cost=3D0.00..0.09 rows=3D5=
 width=3D8)
> >                                  Output: "*VALUES*".column1
> >                                  ->  Values Scan on "*VALUES*"
> >                                         (cost=3D0.00..0.06 rows=3D5 wid=
th=3D8)
> >                                        Output: "*VALUES*".column1
> >
> > At the second case offsets have come to the aggregation without order t=
hat
> > 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,

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