From: Tom Lane <tgl@sss.pgh.pa.us>
To: dqetool@126.com
Cc: pgsql-bugs@lists.postgresql.org, David Rowley <dgrowleyml@gmail.com>
Subject: Re: BUG #18764: server closed the connection unexpectedly
Date: Fri, 03 Jan 2025 11:56:57 -0500
Message-ID: <446342.1735923417@sss.pgh.pa.us> (raw)
In-Reply-To: <18764-63ad667ea26e877a@postgresql.org>
References: <18764-63ad667ea26e877a@postgresql.org>
PG Bug reporting form <noreply@postgresql.org> writes:
> CREATE TABLE t0 (c1 INT, c2 DECIMAL);
> INSERT INTO t0 VALUES (0, NULL);
> INSERT INTO t0 VALUES (8, NULL);
> SELECT c2 AS ca2, c2 AS ca3 FROM t0 UNION SELECT DISTINCT ca8 AS ca5, ca7 AS
> ca6 FROM (SELECT c1 AS ca7, c2 AS ca8 FROM t0) AS ta1 JOIN (SELECT c1 AS
> ca10, c1 AS ca11 FROM t0) AS ta2 ON TRUE; -- server closed the connection
> unexpectedly
Thanks for the report! It's crashing here:
#0 pg_detoast_datum (datum=0x0) at fmgr.c:1834
#1 0x0000000000951603 in DatumGetNumeric (X=0)
at ../../../../src/include/postgres.h:314
#2 numeric_fast_cmp (x=0, y=8, ssup=<optimized out>) at numeric.c:2301
#3 0x0000000000a19019 in ApplySortComparator (datum1=0, isNull1=false,
datum2=8, isNull2=false, ssup=0x1d92f60)
at ../../../../src/include/utils/sortsupport.h:224
#4 0x0000000000a1acc5 in comparetup_heap_tiebreak (a=0x1d98a78, b=0x1d98a90,
state=0x1d92b10) at tuplesortvariants.c:1133
#5 0x0000000000a1ab1b in comparetup_heap (a=0x1d98a78, b=0x1d98a90,
state=0x1d92b10) at tuplesortvariants.c:1086
#6 0x0000000000a133e7 in qsort_tuple (data=0x1d98a78, n=2,
compare=0xa1aa99 <comparetup_heap>, arg=0x1d92b10)
at ../../../../src/include/lib/sort_template.h:316
#7 0x0000000000a17cfe in tuplesort_sort_memtuples (state=0x1d92b10)
at tuplesort.c:2721
#8 0x0000000000a15529 in tuplesort_performsort (state=0x1d92b10)
at tuplesort.c:1382
#9 0x0000000000722b13 in ExecSort (pstate=0x1d61db0) at nodeSort.c:160
#10 0x00000000006f717f in ExecScanFetch (
recheckMtd=0x724f30 <SubqueryRecheck>, accessMtd=0x724f40 <SubqueryNext>,
node=0x1d61c10) at execScan.c:131
I think what is happening is that the executor thinks the sort column
is numeric but it's actually integer. If you EXPLAIN VERBOSE the
query you see
...
-> Subquery Scan on "*SELECT* 2"
Output: "*SELECT* 2".ca5, "*SELECT* 2".ca6
-> Sort
Output: t0_1.c2, t0_1.c1
Sort Key: t0_1.c2, t0_1.c1 USING <
-> HashAggregate
Output: t0_1.c2, t0_1.c1
...
That "USING <" annotation is unexpected. I'm pretty sure it's
there because the sort operator doesn't match the default btree
opclass of the column type, which would be the case if we were
trying to use numeric_lt to sort the integer "c1" column.
git bisect'ing shows that the failure started here:
66c0185a3d14bbbf51d0fc9d267093ffec735231 is the first bad commit
commit 66c0185a3d14bbbf51d0fc9d267093ffec735231
Author: David Rowley <drowley@postgresql.org>
Date: Mon Mar 25 14:31:14 2024 +1300
Allow planner to use Merge Append to efficiently implement UNION
But it may be that this is a pre-existing problem that just happened
to be exposed by the change in plan shape following that commit.
Needs more digging.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, dqetool@126.com, dgrowleyml@gmail.com
Subject: Re: BUG #18764: server closed the connection unexpectedly
In-Reply-To: <446342.1735923417@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox