Content-Type: multipart/mixed; boundary="------------yXdJVjqoEhk44hW97Lr0H20l"
Message-ID: <e62d301f-6c98-43cc-a303-ebcafb1e51d2@postgrespro.ru>
Date: Mon, 11 Dec 2023 02:36:26 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: BUG #16925: ERROR: invalid DSA memory alloc request size
 1073741824 CONTEXT: parallel worker
Content-Language: en-US
From: Alena Rybakina <a.rybakina@postgrespro.ru>
To: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
References: <16925-ec96d83529d0d629@postgresql.org>
 <CA+hUKGKo5N65N2WG7ywO0Wf7p1gBduB-UJO1QzpQTN1xSxoROg@mail.gmail.com>
 <CA+hUKGKYggD1k4Btd4S7HFfXdGJfmvxhv15LtoXpKAxm-GFYDg@mail.gmail.com>
 <CA+hUKGJWKafbqQ3+uByP-Ydb4poX+0DPJCTh3bbcneJkbLKV1w@mail.gmail.com>
 <CA+hUKGLYXpGmbWVKjsCJuWahynGORy1Afwra3ZJyk6YWQ16Vaw@mail.gmail.com>
 <9277414b-dbce-4a32-8aff-642e399e23e5@postgrespro.ru>
 <412f9d60-9229-43d8-b6ed-4f55fb5ed6bd@postgrespro.ru>
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>,
 Thomas Munro <thomas.munro@gmail.com>
In-Reply-To: <412f9d60-9229-43d8-b6ed-4f55fb5ed6bd@postgrespro.ru>
Archived-At: 
 <https://www.postgresql.org/message-id/e62d301f-6c98-43cc-a303-ebcafb1e51d2%40postgrespro.ru>
Precedence: bulk

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

Hi! Thank you for your work on the subject.

On 11.12.2023 02:30, Alena Rybakina wrote:
>
> On 06.12.2023 07:46, Andrei Lepikhov wrote:
>> On 18/3/2021 16:21, Thomas Munro wrote:
>>> ===8<===
>>> shared_buffers=2GB
>>> fsync=off
>>> max_wal_size=10GB
>>> min_dynamic_shared_memory=2GB
>>> ===8<===
>>> create table bigger_than_it_looks as
>>>    select generate_series(1, 256000000) as id;
>>> alter table bigger_than_it_looks set (autovacuum_enabled = 'false');
>>> alter table bigger_than_it_looks set (parallel_workers = 1);
>>> analyze bigger_than_it_looks;
>>> update pg_class set reltuples = 5000000 where relname = 
>>> 'bigger_than_it_looks';
>>> ===8<===
>>> postgres=# set work_mem = '4.5GB';
>>> SET
>>> postgres=# explain analyze select count(*) from bigger_than_it_looks
>>> t1 join bigger_than_it_looks t2 using (id);
>>> ERROR:  invalid DSA memory alloc request size 1073741824
>>> CONTEXT:  parallel worker
>>> ===8<===
>>
>> This bug still annoyingly interrupts the queries of some clients. 
>> Maybe complete this work?
>> It is stable and reproduces on all PG versions. The case:
>> work_mem = '2GB'
>>
>> test table:
>> -----------
>> CREATE TABLE bigger_than_it_looks AS
>>   SELECT generate_series(1, 512E6) AS id;
>> ALTER TABLE bigger_than_it_looks SET (autovacuum_enabled = 'false');
>> ALTER TABLE bigger_than_it_looks SET (parallel_workers = 1);
>> ANALYZE bigger_than_it_looks;
>> UPDATE pg_class SET reltuples = 5000000
>> WHERE relname = 'bigger_than_it_looks';
>>
>> The parallel workers number impacts size of the allowed memory under 
>> the hash table and in that sense correlates with the work_mem value, 
>> needed for the bug reproduction (keep in mind also that 
>> hash_mem_multiplier has been changed recently).
>>
>> Query:
>> SELECT sum(a.id)
>> FROM bigger_than_it_looks a
>> JOIN bigger_than_it_looks b ON a.id =b.id
>> LEFT JOIN bigger_than_it_looks c ON b.id = c.id;
>>
>> Any query that needs Parallel Hash Join can be found here. The case 
>> here is as follows.
>> The first batch contains a lot of tuples (on increment, it has about 
>> 67mln tuples.). We calculate the number of buckets needed, 
>> approximately 134 mln (134217728). Remember, the size of 
>> dsa_pointer_atomic is 8 in my case, and it ends up with an overflow 
>> of the max number of DSA, which can be allocated (1073741823 bytes).
>> See the new patch in the attachment.
I've looked through your code and haven't seen any errors yet, but I 
think we could rewrite these lines of code as follows:

-                    dbuckets = ceil(dtuples / NTUP_PER_BUCKET);
-                    dbuckets = Min(dbuckets, max_buckets);
-                    new_nbuckets = (int) dbuckets;
-                    new_nbuckets = Max(new_nbuckets, 1024);
+                    dbuckets = Min(ceil(dtuples / NTUP_PER_BUCKET), 
max_buckets);
+                    new_nbuckets = Max((int) dbuckets, 1024);

I have attached a diff file with the proposed changes to this email.

-- 
Regards,
Alena Rybakina
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

--------------yXdJVjqoEhk44hW97Lr0H20l
Content-Type: text/plain; charset=UTF-8; name="code_refactoring.diff.txt"
Content-Disposition: attachment; filename="code_refactoring.diff.txt"
Content-Transfer-Encoding: base64

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2V4ZWN1dG9yL25vZGVIYXNoLmMgYi9zcmMvYmFj
a2VuZC9leGVjdXRvci9ub2RlSGFzaC5jCmluZGV4IDljMWFkN2Q3YjQ1Li43NmE5M2JlMjBk
MiAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvZXhlY3V0b3Ivbm9kZUhhc2guYworKysgYi9z
cmMvYmFja2VuZC9leGVjdXRvci9ub2RlSGFzaC5jCkBAIC0xMTc3LDEwICsxMTc3LDggQEAg
RXhlY1BhcmFsbGVsSGFzaEluY3JlYXNlTnVtQmF0Y2hlcyhIYXNoSm9pblRhYmxlIGhhc2h0
YWJsZSkKIAkJCQkJICogYXJyYXkuCiAJCQkJCSAqLwogCQkJCQlkdHVwbGVzID0gKG9sZF9i
YXRjaDAtPm50dXBsZXMgKiAyLjApIC8gbmV3X25iYXRjaDsKLQkJCQkJZGJ1Y2tldHMgPSBj
ZWlsKGR0dXBsZXMgLyBOVFVQX1BFUl9CVUNLRVQpOwotCQkJCQlkYnVja2V0cyA9IE1pbihk
YnVja2V0cywgbWF4X2J1Y2tldHMpOwotCQkJCQluZXdfbmJ1Y2tldHMgPSAoaW50KSBkYnVj
a2V0czsKLQkJCQkJbmV3X25idWNrZXRzID0gTWF4KG5ld19uYnVja2V0cywgMTAyNCk7CisJ
CQkJCWRidWNrZXRzID0gTWluKGNlaWwoZHR1cGxlcyAvIE5UVVBfUEVSX0JVQ0tFVCksIG1h
eF9idWNrZXRzKTsKKwkJCQkJbmV3X25idWNrZXRzID0gTWF4KChpbnQpIGRidWNrZXRzLCAx
MDI0KTsKIAkJCQkJbmV3X25idWNrZXRzID0gcGdfbmV4dHBvd2VyMl8zMihuZXdfbmJ1Y2tl
dHMpOwogCQkJCQlkc2FfZnJlZShoYXNodGFibGUtPmFyZWEsIG9sZF9iYXRjaDAtPmJ1Y2tl
dHMpOwogCQkJCQloYXNodGFibGUtPmJhdGNoZXNbMF0uc2hhcmVkLT5idWNrZXRzID0KCg==


--------------yXdJVjqoEhk44hW97Lr0H20l--