Re: PostgreSQL 17.2 servers crashing due to segmentation faults on query execution

On Mon, 2025-01-27 at 14:55 +0000, Victor Castro Amigo wrote:
> We are encountering crashes on our PostgreSQL 17.2 servers (both on Windows and Linux)
> while executing certain queries from one of our applications.
>
>
> We have created a minimal reproducible example (with a full backup and data attached),
> and it appears the issue is related to unions of columns with different, but auto-coercible,
> types such asbigint and numeric(19,0).
>
> create table t1 (
> id int primary key,
> code int
> );
>
> create table t2 (
> id bigint primary key,
> date timestamp,
> t1_id int references t1(id)
> );
>
> create index t2_idx on t2(t1_id, date);
>
> select t1.code, t2a.id as tid
> from t2 t2b
> inner join t2 t2a on t2b.id = t2a.id
> inner join t1 on t2a.t1_id = t1.id
> where t2a.t1_id = 280686 and t2a.date >= '2025-01-01'
> group by t1.id, t1.code, t2a.id
> union
> (select 0, cast(0 as numeric(19,0)));
>
> The execution plan for this query is as follows:
>
> Unique  (cost=1515.74..1516.84 rows=146 width=36)
>   ->  Sort  (cost=1515.74..1516.11 rows=146 width=36)
>         Sort Key: "*SELECT* 1".code, (("*SELECT* 1".tid)::numeric)
>         ->  Append  (cost=1507.58..1510.49 rows=146 width=36)
>               ->  Subquery Scan on "*SELECT* 1"  (cost=1507.58..1509.75 rows=145 width=36)
>                     ->  Sort  (cost=1507.58..1507.94 rows=145 width=16)
>                           Sort Key: t1.code, t2a.id USING <
>                           ->  Group  (cost=1501.65..1502.37 rows=145 width=16)
>                                 Group Key: t2a.id
>                                 ->  Sort  (cost=1501.65..1502.01 rows=145 width=16)
>                                       Sort Key: t2a.id
>                                       ->  Nested Loop  (cost=6.33..1496.44 rows=145 width=16)
>                                             ->  Seq Scan on t1  (cost=0.00..3.35 rows=1 width=8)
>                                                   Filter: (id = 280686)
>                                             ->  Nested Loop  (cost=6.33..1491.64 rows=145 width=12)
>                                                   ->  Bitmap Heap Scan on t2 t2a  (cost=5.91..396.21 rows=145 width=12)
>                                                         Recheck Cond: ((t1_id = 280686) AND (date >= '2025-01-01 00:00:00'::timestamp without time zone))
>                                                         ->  Bitmap Index Scan on t2_idx  (cost=0.00..5.87 rows=145 width=0)
>                                                               Index Cond: ((t1_id = 280686) AND (date >= '2025-01-01 00:00:00'::timestamp without time zone))
>                                                   ->  Index Only Scan using t2_pkey on t2 t2b  (cost=0.42..7.55 rows=1 width=8)
>                                                         Index Cond: (id = t2a.id)
>               ->  Result  (cost=0.00..0.01 rows=1 width=24)
>
> The issue seems to occur when executing plans with the Sort operation (indicated in bold) and the"USING <" annotation.

This seems to be the same problem as bug 18764:
https://www.postgresql.org/message-id/flat/18764-63ad667ea26e877a%40postgresql.org

Strangely enough, your test case does not fail on my 17.2.

17.3 will contain a fix.

Yours,
Lauernz Albe