Content-Type: multipart/mixed; boundary="------------kkMPt50Z74hU0nJDHIuOZsYi"
Message-ID: <3a1ac6f1-ac30-4ed4-8d0d-beb8a5aca7e5@gmail.com>
Date: Mon, 14 Oct 2024 16:16:11 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Reference to - BUG #18349: ERROR: invalid DSA memory alloc
 request size 1811939328, CONTEXT: parallel worker
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Thomas Munro <thomas.munro@gmail.com>, Craig Milhiser
 <craig@milhiser.com>, pgsql-bugs@lists.postgresql.org
References: 
 <CA+wnhO1OfgXbmXgC4fv_uu=OxcDQuHvfoQ4k0DFeB0Qqd-X-rQ@mail.gmail.com>
 <CA+hUKG+YZpOyP-XXT65o9hx=oGF+9-BJzjqzRXcdvWV=ZrJbTA@mail.gmail.com>
 <CA+wnhO2NDZFWB1TrZga-uLzpfSDjKH5Axa2d2+1Q76JSsS7yMg@mail.gmail.com>
 <CA+wnhO0S2kxJ2_M57uJXRocMpcDtz6N4RkP-3Xy=pFs1wp7hmg@mail.gmail.com>
 <CA+hUKGLPKWamfKjtEkPLvRDSeOqoZZ0wDfU65d-hF-1upL8iGw@mail.gmail.com>
 <CA+wnhO3kT7mxbadbteppSZVcs3FRPvGL5xTY2DbrWSGohmhyVw@mail.gmail.com>
 <6785e9e8-4c64-41b0-9e72-c13d1822b599@gmail.com>
 <CA+wnhO0VdTP9OVdZP9bmkavma2WkE-pYpP1yW8yDwRrJ97j-2g@mail.gmail.com>
 <0c3b0e1b-55d1-42b6-8c3c-4392479187db@gmail.com>
 <CA+wnhO2sHcUOfi6_gvaoPMk=2DsKUOWkDjdUtGxJOguQOXDvOQ@mail.gmail.com>
 <cc488d1c-6477-4e20-8bd0-cf152a2e59bf@gmail.com>
 <CA+wnhO3t7xqCiDmGduxgg1Mv5M5R-GeQd0kzVcJKkUFkj91Wbw@mail.gmail.com>
 <CA+hUKGLYx2GGd8BggND5QnyyJKbf1r+Q0cEYWrN+miXzaxwGNA@mail.gmail.com> <9!
 6a328e9-4fbf-433b-a8d7-071da18e12c4@gmail.com>
 <3581840.1728887169@sss.pgh.pa.us>
Content-Language: en-AU
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: <3581840.1728887169@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3a1ac6f1-ac30-4ed4-8d0d-beb8a5aca7e5%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------kkMPt50Z74hU0nJDHIuOZsYi
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 10/14/24 13:26, Tom Lane wrote:
> Andrei Lepikhov <lepihov@gmail.com> writes:
>> My explanation (correct if I'm wrong):
>> OUTER JOINs allow NULLs to be in a hash table. At the same time, a hash
>> value for NULL is 0, and it goes to the batch==0.
>> If batch number 0 gets overfilled, the
>> ExecParallelHashIncreaseNumBatches routine attempts to increase the
>> number of batches - but nothing happens. The initial batch is still too
>> big, and the number of batches doubles up to the limit.
> 
> Interesting point.  If memory serves (I'm too tired to actually look)
> the planner considers the statistical most-common-value when
> estimating whether an unsplittable hash bucket is likely to be too
> big.  It does *not* think about null values ... but it ought to.
As I see it, it is just an oversight in the resizing logic: batch 0 
doesn't change the estimated_size value at all - I think because it 
doesn't matter for this batch - it can't be treated as exhausted by 
definition. Because of that, parallel HashJoin doesn't detect extreme 
skew, caused  by duplicates in this batch. NULLS is just our luck - they 
correspond to hash value 0 and fall into this batch.
See the attachment for a sketch of the solution.

> 
> However, this does not explain why PHJ would be more subject to
> the problem than non-parallel HJ.
Good question! I rarely touch this part of the code and maybe don't see 
whole picture. But as I see it, HJ is designed differently: 
repartitioning machinery is based on overall hash table size and number 
of tuples and has nothing similar to 'batch 0' or parallel batches. Hash 
table size is calculated for each batch and can't cause this bug.

BTW, Can we also resolve here the long-living corner case with "invalid 
DSA memory alloc request size" [1]? Just because we have clear 
reproduction ...

[1] 
https://www.postgresql.org/message-id/7d763a6d-fad7-49b6-beb0-86f99ce4a6eb%40postgrespro.ru

-- 
regards, Andrei Lepikhov

--------------kkMPt50Z74hU0nJDHIuOZsYi
Content-Type: text/x-patch; charset=UTF-8;
 name="0001-Consider-extreme-skew-in-batch-0-during-Parallel-Has.patch"
Content-Disposition: attachment;
 filename*0="0001-Consider-extreme-skew-in-batch-0-during-Parallel-Has.pa";
 filename*1="tch"
Content-Transfer-Encoding: base64

RnJvbSA4N2I4MjM4MjFhYmQ0ZWY1MzIxNGJhNTk1NGQ4ZTgwNjlhMWM0MGZjIE1vbiBTZXAg
MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiAiQW5kcmVpIFYuIExlcGlraG92IiA8bGVwaWhvdkBn
bWFpbC5jb20+CkRhdGU6IE1vbiwgMTQgT2N0IDIwMjQgMTU6NDg6MDEgKzA3MDAKU3ViamVj
dDogW1BBVENIXSBDb25zaWRlciBleHRyZW1lIHNrZXcgaW4gYmF0Y2ggMCBkdXJpbmcgUGFy
YWxsZWwgSGFzaCBKb2luCiBleGVjdXRpb24uCgpCYXRjaCAwIGRvZXNuJ3QgbWFpbnRhaW4g
dGhlIGVzdGltYXRlZF9zaXplIHZhbHVlLiBJbiB0aGlzIGNhc2UsIGV4dHJlbWUgc2tldwpj
YXVzZWQgYnkgbWFzc2l2ZSBkdXBsaWNhdGVzIHNob3VsZCBiZSBkZXRlY3RlZCBieSB0aGUg
c2FtZSBudW1iZXIgb2YgdHVwbGVzCmxlZnQgaW4gdGhpcyBiYXRjaCBhZnRlciB0aGUgcmVz
aXppbmcuCgpUaGlzIGJ1ZyBkb2Vzbid0IGV4aXN0IGluIG5vbi1wYXJhbGxlbCBISiBiZWNh
dXNlIHRoaXMgYmF0Y2ggcmVwYXJ0aXRpb25pbmcKcHJvY2VkdXJlIGlzIGJhc2VkIG9uIHRo
ZSBoYXNoIHRhYmxlIHNpemUgYW5kIG51bWJlciBvZiB0dXBsZXMgaW4gdGhlIGJhdGNoLgot
LS0KIHNyYy9iYWNrZW5kL2V4ZWN1dG9yL25vZGVIYXNoLmMgfCA5ICsrKysrKystLQogMSBm
aWxlIGNoYW5nZWQsIDcgaW5zZXJ0aW9ucygrKSwgMiBkZWxldGlvbnMoLSkKCmRpZmYgLS1n
aXQgYS9zcmMvYmFja2VuZC9leGVjdXRvci9ub2RlSGFzaC5jIGIvc3JjL2JhY2tlbmQvZXhl
Y3V0b3Ivbm9kZUhhc2guYwppbmRleCA1NzBhOTBlYmUxLi4zMTkwMDZkNjlhIDEwMDY0NAot
LS0gYS9zcmMvYmFja2VuZC9leGVjdXRvci9ub2RlSGFzaC5jCisrKyBiL3NyYy9iYWNrZW5k
L2V4ZWN1dG9yL25vZGVIYXNoLmMKQEAgLTEyNDIsMTEgKzEyNDIsMTYgQEAgRXhlY1BhcmFs
bGVsSGFzaEluY3JlYXNlTnVtQmF0Y2hlcyhIYXNoSm9pblRhYmxlIGhhc2h0YWJsZSkKIAog
CQkJCQlpZiAoYmF0Y2gtPnNwYWNlX2V4aGF1c3RlZCB8fAogCQkJCQkJYmF0Y2gtPmVzdGlt
YXRlZF9zaXplID4gcHN0YXRlLT5zcGFjZV9hbGxvd2VkKQorCQkJCQkJc3BhY2VfZXhoYXVz
dGVkID0gdHJ1ZTsKKworCQkJCQkvKgorCQkJCQkgKiBCYXRjaCAwIGRvZXNuJ3QgbWFpbnRh
aW4gZXN0aW1hdGVkX3NpemUsIHNvIGl0IHNob3VsZCBiZQorCQkJCQkgKiB0cmVhdGVkIGFz
IGEgc3BlY2lhbCBjYXNlLgorCQkJCQkgKi8KKwkJCQkJaWYgKHNwYWNlX2V4aGF1c3RlZCB8
fCBpID09IDApCiAJCQkJCXsKIAkJCQkJCWludAkJCXBhcmVudDsKIAotCQkJCQkJc3BhY2Vf
ZXhoYXVzdGVkID0gdHJ1ZTsKLQogCQkJCQkJLyoKIAkJCQkJCSAqIERpZCB0aGlzIGJhdGNo
IHJlY2VpdmUgQUxMIG9mIHRoZSB0dXBsZXMgZnJvbSBpdHMKIAkJCQkJCSAqIHBhcmVudCBi
YXRjaD8gIFRoYXQgd291bGQgaW5kaWNhdGUgdGhhdCBmdXJ0aGVyCi0tIAoyLjM5LjUKCg==


--------------kkMPt50Z74hU0nJDHIuOZsYi--