Message-ID: <117efd72-5692-4844-bc4e-c553d03d393e@gmail.com>
Date: Sun, 26 Apr 2026 11:06:47 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: TRAP: failed Assert("offsets[i] > offsets[i - 1]"), File:
 "tidstore.c"
To: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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>
Content-Language: en-US
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: <CAD21AoAORy3MmSxPEaRbE_BuwW3qyxsfLGj81YcOtn6gv2iJww@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/117efd72-5692-4844-bc4e-c553d03d393e%40gmail.com>
Precedence: bulk

On 25/04/2026 01:23, Masahiko Sawada wrote:
> On Wed, Apr 22, 2026 at 10:23 AM Andrei Lepikhov <lepihov@gmail.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.

-- 
regards, Andrei Lepikhov,
pgEdge