Date: Fri, 12 Aug 2022 23:05:06 +0800
From: Zhang Mingli <zmlpostgres@gmail.com>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Message-ID: <beb64ca0-91e2-44ac-bf4a-7ea36275ec02@Spark>
In-Reply-To: <9746d4e1-a008-44a3-bce9-170b4721e862@Spark>
References: <9746d4e1-a008-44a3-bce9-170b4721e862@Spark>
Subject: [patch]HashJoin crash
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="62f66c27_23f9c13c_1b7"
Archived-At: 
 <https://www.postgresql.org/message-id/beb64ca0-91e2-44ac-bf4a-7ea36275ec02%40Spark>
Precedence: bulk

--62f66c27_23f9c13c_1b7
Content-Type: multipart/alternative; boundary="62f66c27_4f4ef005_1b7"

--62f66c27_4f4ef005_1b7
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi,

I got a coredump when using hash join on a Postgres derived Database(Gree=
nplum DB).

And I find a way to reproduce it on Postgres.

Root cause:

In ExecChooseHashTableSize(), commit b154ee63bb uses func pg=5Fnextpower2=
=5Fsize=5Ft
whose param must not be 0.

=60=60=60
sbuckets =3D pg=5Fnextpower2=5Fsize=5Ft(hash=5Ftable=5Fbytes / bucket=5Fs=
ize);

=60=60=60

There is a potential risk that hash=5Ftable=5Fbytes < bucket=5Fsize in so=
me corner cases.

Reproduce sql:

=60=60=60
--create a wide enough table to reproduce the bug
DO language 'plpgsql'
=24=24
DECLARE var=5Fsql text :=3D 'CREATE TABLE t=5F1600=5Fcolumns('
=C2=A0=7C=7C string=5Fagg('field' =7C=7C i::text =7C=7C ' varchar(255)', =
',') =7C=7C ');'
=C2=A0=46ROM generate=5Fseries(1,1600) As i;
BEGIN
=C2=A0EXECUTE var=5Fsql;
END;
=24=24 ;

create table j1(field1 text);
set work=5Fmem =3D 64;
set hash=5Fmem=5Fmultiplier =3D 1;
set enable=5Fnestloop =3D off;
set enable=5Fmergejoin =3D off;

explain select * from j1 inner join t=5F1600=5Fcolumns using(field1);

server closed the connection unexpectedly
=C2=A0This probably means the server terminated abnormally
=C2=A0before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded

=60=60=60

Part of=C2=A0=C2=A0core dump file:

=60=60=60
=230 =5F=5Fpthread=5Fkill=5Fimplementation (no=5Ftid=3D0, signo=3D6, thre=
adid=3D139769161559104) at ./nptl/pthread=5Fkill.c:44
=231 =5F=5Fpthread=5Fkill=5Finternal (signo=3D6, threadid=3D1397691615591=
04) at ./nptl/pthread=5Fkill.c:78
=232 =5F=5FGI=5F=5F=5Fpthread=5Fkill (threadid=3D139769161559104, signo=3D=
signo=40entry=3D6) at ./nptl/pthread=5Fkill.c:89
=233 0x00007f1e8b3de476 in =5F=5FGI=5Fraise (sig=3Dsig=40entry=3D6) at ..=
/sysdeps/posix/raise.c:26
=234 0x00007f1e8b3c47f3 in =5F=5FGI=5Fabort () at ./stdlib/abort.c:79
=235 0x0000558cc8884062 in ExceptionalCondition (conditionName=3D0x558cc8=
a21570 =22num > 0 && num <=3D PG=5FUINT64=5FMAX / 2 + 1=22,
=C2=A0errorType=3D0x558cc8a21528 =22=46ailedAssertion=22, fileName=3D0x55=
8cc8a21500 =22../../../src/include/port/pg=5Fbitutils.h=22, lineNumber=3D=
165) at assert.c:69
=236 0x0000558cc843bb16 in pg=5Fnextpower2=5F64 (num=3D0) at ../../../src=
/include/port/pg=5Fbitutils.h:165
=237 0x0000558cc843d13a in ExecChooseHashTableSize (ntuples=3D100, tupwid=
th=3D825086, useskew=3Dtrue, try=5Fcombined=5Fhash=5Fmem=3Dfalse, paralle=
l=5Fworkers=3D0,
=C2=A0space=5Fallowed=3D0x7ffdcfa01598, numbuckets=3D0x7ffdcfa01588, numb=
atches=3D0x7ffdcfa0158c, num=5Fskew=5Fmcvs=3D0x7ffdcfa01590) at nodeHash.=
c:835
=60=60=60

This patch fixes it easily:

=60=60=60
- sbuckets =3D pg=5Fnextpower2=5Fsize=5Ft(hash=5Ftable=5Fbytes / bucket=5F=
size);
+ if (hash=5Ftable=5Fbytes < bucket=5Fsize)
+=C2=A0 =C2=A0sbuckets =3D 1;
+ else
+=C2=A0 =C2=A0sbuckets =3D pg=5Fnextpower2=5Fsize=5Ft(hash=5Ftable=5Fbyte=
s / bucket=5Fsize);
=60=60=60

Or, we could report an error/hit message to tell users to increase work=5F=
mem/hash=5Fmem=5Fmultiplier.

And I think let it work is better.

The issue exists on master, 15, 14, 13.

Regards,
Zhang Mingli

--62f66c27_4f4ef005_1b7
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

<html xmlns=3D=22http://www.w3.org/1999/xhtml=22>
<head>
<title></title>
</head>
<body>
<div name=3D=22messageBodySection=22>
<div dir=3D=22auto=22>Hi,<br />
<br />
I got a coredump when using hash join on a Postgres derived Database(Gree=
nplum DB).<br />
<br />
And I find a way to reproduce it on Postgres.<br />
<br />
Root cause:<br />
<br />
In ExecChooseHashTableSize(), commit b154ee63bb uses func pg=5Fnextpower2=
=5Fsize=5Ft&=23160;<br />
whose param must not be 0.<br />
<br />
=60=60=60<br />
sbuckets =3D pg=5Fnextpower2=5Fsize=5Ft(hash=5Ftable=5Fbytes / bucket=5Fs=
ize);<br />
<br />
=60=60=60<br />
<br />
There is a potential risk that hash=5Ftable=5Fbytes &lt; bucket=5Fsize in=
 some corner cases.<br />
<br />
Reproduce sql:<br />
<br />
=60=60=60<br />
--create a wide enough table to reproduce the bug<br />
DO language 'plpgsql'<br />
=24=24<br />
DECLARE var=5Fsql text :=3D 'CREATE TABLE t=5F1600=5Fcolumns('<br />
&=23160;=7C=7C string=5Fagg('field' =7C=7C i::text =7C=7C ' varchar(255)'=
, ',') =7C=7C ');'<br />
&=23160;=46ROM generate=5Fseries(1,1600) As i;<br />
BEGIN<br />
&=23160;EXECUTE var=5Fsql;<br />
END;<br />
=24=24 ;<br />
<br />
create table j1(field1 text);<br />
set work=5Fmem =3D 64;<br />
set hash=5Fmem=5Fmultiplier =3D 1;<br />
set enable=5Fnestloop =3D off;<br />
set enable=5Fmergejoin =3D off;<br />
<br />
explain select * from j1 inner join t=5F1600=5Fcolumns using(field1);<br =
/>
<br />
server closed the connection unexpectedly<br />
&=23160;This probably means the server terminated abnormally<br />
&=23160;before or while processing the request.<br />
The connection to the server was lost. Attempting reset: Succeeded<br />
<br />
=60=60=60<br />
<br />
Part of&=23160;&=23160;core dump file:<br />
<br />
=60=60=60<br />
=230 =5F=5Fpthread=5Fkill=5Fimplementation (no=5Ftid=3D0, signo=3D6, thre=
adid=3D139769161559104) at ./nptl/pthread=5Fkill.c:44<br />
=231 =5F=5Fpthread=5Fkill=5Finternal (signo=3D6, threadid=3D1397691615591=
04) at ./nptl/pthread=5Fkill.c:78<br />
=232 =5F=5FGI=5F=5F=5Fpthread=5Fkill (threadid=3D139769161559104, signo=3D=
signo=40entry=3D6) at ./nptl/pthread=5Fkill.c:89<br />
=233 0x00007f1e8b3de476 in =5F=5FGI=5Fraise (sig=3Dsig=40entry=3D6) at ..=
/sysdeps/posix/raise.c:26<br />
=234 0x00007f1e8b3c47f3 in =5F=5FGI=5Fabort () at ./stdlib/abort.c:79<br =
/>
=235 0x0000558cc8884062 in ExceptionalCondition (conditionName=3D0x558cc8=
a21570 =22num &gt; 0 &amp;&amp; num &lt;=3D PG=5FUINT64=5FMAX / 2 + 1=22,=
<br />
&=23160;errorType=3D0x558cc8a21528 =22=46ailedAssertion=22, fileName=3D0x=
558cc8a21500 =22../../../src/include/port/pg=5Fbitutils.h=22, lineNumber=3D=
165) at assert.c:69<br />
=236 0x0000558cc843bb16 in pg=5Fnextpower2=5F64 (num=3D0) at ../../../src=
/include/port/pg=5Fbitutils.h:165<br />
=237 0x0000558cc843d13a in ExecChooseHashTableSize (ntuples=3D100, tupwid=
th=3D825086, useskew=3Dtrue, try=5Fcombined=5Fhash=5Fmem=3Dfalse, paralle=
l=5Fworkers=3D0,<br />
&=23160;space=5Fallowed=3D0x7ffdcfa01598, numbuckets=3D0x7ffdcfa01588, nu=
mbatches=3D0x7ffdcfa0158c, num=5Fskew=5Fmcvs=3D0x7ffdcfa01590) at nodeHas=
h.c:835<br />
=60=60=60<br />
<br />
This patch fixes it easily:<br />
<br />
=60=60=60<br />
- sbuckets =3D pg=5Fnextpower2=5Fsize=5Ft(hash=5Ftable=5Fbytes / bucket=5F=
size);<br />
+ if (hash=5Ftable=5Fbytes &lt; bucket=5Fsize)<br />
+&=23160; &=23160;sbuckets =3D 1;<br />
+ else<br />
+&=23160; &=23160;sbuckets =3D pg=5Fnextpower2=5Fsize=5Ft(hash=5Ftable=5F=
bytes / bucket=5Fsize);<br />
=60=60=60<br />
<br />
Or, we could report an error/hit message to tell users to increase work=5F=
mem/hash=5Fmem=5Fmultiplier.<br />
<br />
And I think let it work is better.<br />
<br />
The issue exists on master, 15, 14, 13.<br /></div>
</div>
<div name=3D=22messageSignatureSection=22><br />
<div class=3D=22match=46ont=22>Regards,
<div dir=3D=22auto=22>Zhang Mingli</div>
</div>
</div>
</body>
</html>

--62f66c27_4f4ef005_1b7--

--62f66c27_23f9c13c_1b7
Content-Type: application/octet-stream
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename="vn-0001-Fix-HashJoin-crash.patch"

RnJvbSA0NjQyYmMyZDc0NmJmNDJhMzdkYmQ5NmI1NTE5ZDI3NjVmMjZmODJiIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBNaW5nbGkgWmhhbmcgPGF2YW1pbmdsaUBnbWFpbC5jb20+CkRh
dGU6IEZyaSwgMTIgQXVnIDIwMjIgMjI6MzU6MjkgKzA4MDAKU3ViamVjdDogW1BBVENIIHZuXSBG
aXggSGFzaEpvaW4gY3Jhc2guCgpJbiBFeGVjQ2hvb3NlSGFzaFRhYmxlU2l6ZSgpLCBjb21taXQg
YjE1NGVlNjNiYiB1c2VzIGZ1bmMgcGdfbmV4dHBvd2VyMl9zaXplX3QKd2hvc2UgcGFyYW0gbXVz
dCBub3QgYmUgMC4KClRoZXJlIGlzIGEgcG90ZW50aWFsIHJpc2sgdGhhdCBoYXNoX3RhYmxlX2J5
dGVzIDwgYnVja2V0X3NpemUgaW4gc29tZSBjb3JuZXIgY2FzZXMuCgpSZXByb2R1Y2Ugc3FsOgoK
LS1jcmVhdGUgYSB3aWRlIGVub3VnaCB0YWJsZSB0byByZXByb2R1Y2UgdGhlIGJ1ZwpETyBsYW5n
dWFnZSAncGxwZ3NxbCcKJCQKREVDTEFSRSB2YXJfc3FsIHRleHQgOj0gJ0NSRUFURSBUQUJMRSB0
XzE2MDBfY29sdW1ucygnCiB8fCBzdHJpbmdfYWdnKCdmaWVsZCcgfHwgaTo6dGV4dCB8fCAnIHZh
cmNoYXIoMjU1KScsICcsJykgfHwgJyk7JwogRlJPTSBnZW5lcmF0ZV9zZXJpZXMoMSwxNjAwKSBB
cyBpOwpCRUdJTgogRVhFQ1VURSB2YXJfc3FsOwpFTkQ7CiQkIDsKCmNyZWF0ZSB0YWJsZSBqMShm
aWVsZDEgdGV4dCk7CnNldCB3b3JrX21lbSA9IDY0OwpzZXQgaGFzaF9tZW1fbXVsdGlwbGllciA9
IDE7CnNldCBlbmFibGVfbmVzdGxvb3AgPSBvZmY7CnNldCBlbmFibGVfbWVyZ2Vqb2luID0gb2Zm
OwoKZXhwbGFpbiBzZWxlY3QgKiBmcm9tIGoxIGlubmVyIGpvaW4gdF8xNjAwX2NvbHVtbnMgdXNp
bmcoZmllbGQxKTsKCnNlcnZlciBjbG9zZWQgdGhlIGNvbm5lY3Rpb24gdW5leHBlY3RlZGx5CiBU
aGlzIHByb2JhYmx5IG1lYW5zIHRoZSBzZXJ2ZXIgdGVybWluYXRlZCBhYm5vcm1hbGx5CiBiZWZv
cmUgb3Igd2hpbGUgcHJvY2Vzc2luZyB0aGUgcmVxdWVzdC4KVGhlIGNvbm5lY3Rpb24gdG8gdGhl
IHNlcnZlciB3YXMgbG9zdC4gQXR0ZW1wdGluZyByZXNldDogU3VjY2VlZGVkCi0tLQogc3JjL2Jh
Y2tlbmQvZXhlY3V0b3Ivbm9kZUhhc2guYyAgICAgICAgIHwgIDUgKysrKy0KIHNyYy90ZXN0L3Jl
Z3Jlc3MvZXhwZWN0ZWQvam9pbl9oYXNoLm91dCB8IDI4ICsrKysrKysrKysrKysrKysrKysrKysr
KysKIHNyYy90ZXN0L3JlZ3Jlc3Mvc3FsL2pvaW5faGFzaC5zcWwgICAgICB8IDI0ICsrKysrKysr
KysrKysrKysrKysrKwogMyBmaWxlcyBjaGFuZ2VkLCA1NiBpbnNlcnRpb25zKCspLCAxIGRlbGV0
aW9uKC0pCgpkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQvZXhlY3V0b3Ivbm9kZUhhc2guYyBiL3Ny
Yy9iYWNrZW5kL2V4ZWN1dG9yL25vZGVIYXNoLmMKaW5kZXggMTIzMDc5YzE2Yy4uMjY4YjZlZTlk
ZCAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvZXhlY3V0b3Ivbm9kZUhhc2guYworKysgYi9zcmMv
YmFja2VuZC9leGVjdXRvci9ub2RlSGFzaC5jCkBAIC04MzIsNyArODMyLDEwIEBAIEV4ZWNDaG9v
c2VIYXNoVGFibGVTaXplKGRvdWJsZSBudHVwbGVzLCBpbnQgdHVwd2lkdGgsIGJvb2wgdXNlc2tl
dywKIAkJICogb3ZlcmhlYWQgZm9yIHRoZSBoYXNoIGNvZGUsIHBvaW50ZXIgdG8gdGhlIG5leHQg
dHVwbGUsIGV0Yy4KIAkJICovCiAJCWJ1Y2tldF9zaXplID0gKHR1cHNpemUgKiBOVFVQX1BFUl9C
VUNLRVQgKyBzaXplb2YoSGFzaEpvaW5UdXBsZSkpOwotCQlzYnVja2V0cyA9IHBnX25leHRwb3dl
cjJfc2l6ZV90KGhhc2hfdGFibGVfYnl0ZXMgLyBidWNrZXRfc2l6ZSk7CisJCWlmIChoYXNoX3Rh
YmxlX2J5dGVzIDwgYnVja2V0X3NpemUpCisJCQlzYnVja2V0cyA9IDE7CisJCWVsc2UKKwkJCXNi
dWNrZXRzID0gcGdfbmV4dHBvd2VyMl9zaXplX3QoaGFzaF90YWJsZV9ieXRlcyAvIGJ1Y2tldF9z
aXplKTsKIAkJc2J1Y2tldHMgPSBNaW4oc2J1Y2tldHMsIG1heF9wb2ludGVycyk7CiAJCW5idWNr
ZXRzID0gKGludCkgc2J1Y2tldHM7CiAJCW5idWNrZXRzID0gcGdfbmV4dHBvd2VyMl8zMihuYnVj
a2V0cyk7CmRpZmYgLS1naXQgYS9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL2pvaW5faGFzaC5v
dXQgYi9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL2pvaW5faGFzaC5vdXQKaW5kZXggM2VjMDdi
YzFhZi4uYjEzYjgyNGY3ZiAxMDA2NDQKLS0tIGEvc3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC9q
b2luX2hhc2gub3V0CisrKyBiL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvam9pbl9oYXNoLm91
dApAQCAtMTAzMSwzICsxMDMxLDMxIEBAIFdIRVJFCiAoMSByb3cpCiAKIFJPTExCQUNLOworLS0g
VmVyaWZ5IHRoYXQgaGFzaCBqb2luIG9uIHdpZGUgdGFibGUgd2l0aCBsaXR0bGUgbWVtb3J5Lgor
QkVHSU47CistLSBjcmVhdGUgYSB3aWRlIGVub3VnaCB0YWJsZSB0byByZXByb2R1Y2UgdGhlIGJ1
Zy4KK0RPIGxhbmd1YWdlICdwbHBnc3FsJworJCQKK0RFQ0xBUkUgdmFyX3NxbCB0ZXh0IDo9ICdD
UkVBVEUgVEFCTEUgdF8xNjAwX2NvbHVtbnMoJworIHx8IHN0cmluZ19hZ2coJ2ZpZWxkJyB8fCBp
Ojp0ZXh0IHx8ICcgdmFyY2hhcigyNTUpJywgJywnKSB8fCAnKTsnCisgRlJPTSBnZW5lcmF0ZV9z
ZXJpZXMoMSwxNjAwKSBBcyBpOworQkVHSU4KKyBFWEVDVVRFIHZhcl9zcWw7CitFTkQ7CiskJCA7
CitjcmVhdGUgdGFibGUgajEoZmllbGQxIHRleHQpOworc2V0IGxvY2FsIHdvcmtfbWVtID0gNjQ7
CitzZXQgbG9jYWwgaGFzaF9tZW1fbXVsdGlwbGllciA9IDE7CitzZXQgbG9jYWwgZW5hYmxlX25l
c3Rsb29wID0gb2ZmOworc2V0IGxvY2FsIGVuYWJsZV9tZXJnZWpvaW4gPSBvZmY7CitleHBsYWlu
IChjb3N0cyBvZmYpIHNlbGVjdCAqIGZyb20gajEgaW5uZXIgam9pbiB0XzE2MDBfY29sdW1ucyB1
c2luZyhmaWVsZDEpOworICAgICAgICAgICAgICAgICAgICAgICAgUVVFUlkgUExBTiAgICAgICAg
ICAgICAgICAgICAgICAgIAorLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLQorIEhhc2ggSm9pbgorICAgSGFzaCBDb25kOiAoKHRfMTYwMF9j
b2x1bW5zLmZpZWxkMSk6OnRleHQgPSBqMS5maWVsZDEpCisgICAtPiAgU2VxIFNjYW4gb24gdF8x
NjAwX2NvbHVtbnMKKyAgIC0+ICBIYXNoCisgICAgICAgICAtPiAgU2VxIFNjYW4gb24gajEKKyg1
IHJvd3MpCisKK1JPTExCQUNLOwpkaWZmIC0tZ2l0IGEvc3JjL3Rlc3QvcmVncmVzcy9zcWwvam9p
bl9oYXNoLnNxbCBiL3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL2pvaW5faGFzaC5zcWwKaW5kZXggNzdk
YmMxODJkNS4uYmQ0MTcyZDU0MSAxMDA2NDQKLS0tIGEvc3JjL3Rlc3QvcmVncmVzcy9zcWwvam9p
bl9oYXNoLnNxbAorKysgYi9zcmMvdGVzdC9yZWdyZXNzL3NxbC9qb2luX2hhc2guc3FsCkBAIC01
NTYsMyArNTU2LDI3IEBAIFdIRVJFCiAgICAgQU5EIGhqdGVzdF8xLmEgPD4gaGp0ZXN0XzIuYjsK
IAogUk9MTEJBQ0s7CisKKy0tIFZlcmlmeSB0aGF0IGhhc2ggam9pbiBvbiB3aWRlIHRhYmxlIHdp
dGggbGl0dGxlIG1lbW9yeS4KK0JFR0lOOworLS0gY3JlYXRlIGEgd2lkZSBlbm91Z2ggdGFibGUg
dG8gcmVwcm9kdWNlIHRoZSBidWcuCitETyBsYW5ndWFnZSAncGxwZ3NxbCcKKyQkCitERUNMQVJF
IHZhcl9zcWwgdGV4dCA6PSAnQ1JFQVRFIFRBQkxFIHRfMTYwMF9jb2x1bW5zKCcKKyB8fCBzdHJp
bmdfYWdnKCdmaWVsZCcgfHwgaTo6dGV4dCB8fCAnIHZhcmNoYXIoMjU1KScsICcsJykgfHwgJyk7
JworIEZST00gZ2VuZXJhdGVfc2VyaWVzKDEsMTYwMCkgQXMgaTsKK0JFR0lOCisgRVhFQ1VURSB2
YXJfc3FsOworRU5EOworJCQgOworCitjcmVhdGUgdGFibGUgajEoZmllbGQxIHRleHQpOworCitz
ZXQgbG9jYWwgd29ya19tZW0gPSA2NDsKK3NldCBsb2NhbCBoYXNoX21lbV9tdWx0aXBsaWVyID0g
MTsKK3NldCBsb2NhbCBlbmFibGVfbmVzdGxvb3AgPSBvZmY7CitzZXQgbG9jYWwgZW5hYmxlX21l
cmdlam9pbiA9IG9mZjsKKworZXhwbGFpbiAoY29zdHMgb2ZmKSBzZWxlY3QgKiBmcm9tIGoxIGlu
bmVyIGpvaW4gdF8xNjAwX2NvbHVtbnMgdXNpbmcoZmllbGQxKTsKKworUk9MTEJBQ0s7ClwgTm8g
bmV3bGluZSBhdCBlbmQgb2YgZmlsZQotLSAKMi4zNC4xCgo=

--62f66c27_23f9c13c_1b7--