Thread: Re: PostgreSQL 17.2 servers crashing due to segmentation faults on query execution
Re: PostgreSQL 17.2 servers crashing due to segmentation faults on query execution
From
Laurenz Albe
Date:
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'::timestampwithout 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'::timestampwithout time zone))
> -> Index Only Scan using t2_pkey on t2 t2b (cost=0.42..7.55
rows=1width=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