Content-Type: multipart/mixed; boundary="------------t0ph7bTMSLB5ZGSO5Ff0hCJg"
Message-ID: <9277414b-dbce-4a32-8aff-642e399e23e5@postgrespro.ru>
Date: Wed, 6 Dec 2023 11:46:08 +0700
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
To: Thomas Munro <thomas.munro@gmail.com>,
 PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Cc: "a.rybakina" <a.rybakina@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>
From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
In-Reply-To: 
 <CA+hUKGLYXpGmbWVKjsCJuWahynGORy1Afwra3ZJyk6YWQ16Vaw@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/9277414b-dbce-4a32-8aff-642e399e23e5%40postgrespro.ru>
Precedence: bulk

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

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.

-- 
regards,
Andrei Lepikhov
Postgres Professional

--------------t0ph7bTMSLB5ZGSO5Ff0hCJg
Content-Type: text/plain; charset=UTF-8;
 name="0001-Bugfix.-Guard-total-number-of-hash-table-buckets.patch"
Content-Disposition: attachment;
 filename*0="0001-Bugfix.-Guard-total-number-of-hash-table-buckets.patch"
Content-Transfer-Encoding: base64

RnJvbSA5ZmI2MTAxMjZjNjA3YTZjZWQ5NzFkNzc0MTI3ZjIyNTVhZDBiYmY4IE1vbiBTZXAg
MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiAiQW5kcmV5IFYuIExlcGlraG92IiA8YS5sZXBpa2hv
dkBwb3N0Z3Jlc3Byby5ydT4KRGF0ZTogV2VkLCA2IERlYyAyMDIzIDEwOjUyOjA1ICswNzAw
ClN1YmplY3Q6IFtQQVRDSF0gQnVnZml4LiBHdWFyZCB0b3RhbCBudW1iZXIgb2YgaGFzaCB0
YWJsZSBidWNrZXRzLgoKSXQgbXVzdCBiZSBhcnJhbmdlZCB3aXRoIGFuIG1heCBudW1iZXIs
IGNvcnJlc3BvbmRpbmcgdG8gbWF4aW11bSBwb3NzaWJsZQphbGxvY2F0ZWQgc2l6ZSBvZiBE
U0EgbWVtb3J5LgotLS0KIHNyYy9iYWNrZW5kL2V4ZWN1dG9yL25vZGVIYXNoLmMgfCA2ICsr
KystLQogMSBmaWxlIGNoYW5nZWQsIDQgaW5zZXJ0aW9ucygrKSwgMiBkZWxldGlvbnMoLSkK
CmRpZmYgLS1naXQgYS9zcmMvYmFja2VuZC9leGVjdXRvci9ub2RlSGFzaC5jIGIvc3JjL2Jh
Y2tlbmQvZXhlY3V0b3Ivbm9kZUhhc2guYwppbmRleCBlNzJmMDk4NmMyLi5mZGJhN2M4NWQz
IDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9leGVjdXRvci9ub2RlSGFzaC5jCisrKyBiL3Ny
Yy9iYWNrZW5kL2V4ZWN1dG9yL25vZGVIYXNoLmMKQEAgLTExNTUsNiArMTE1NSw5IEBAIEV4
ZWNQYXJhbGxlbEhhc2hJbmNyZWFzZU51bUJhdGNoZXMoSGFzaEpvaW5UYWJsZSBoYXNodGFi
bGUpCiAJCQkJCWRvdWJsZQkJZHR1cGxlczsKIAkJCQkJZG91YmxlCQlkYnVja2V0czsKIAkJ
CQkJaW50CQkJbmV3X25idWNrZXRzOworCQkJCQljb25zdCB1aW50MzIgbWF4X2J1Y2tldHMg
PQorCQkJCQkJCXBnX3ByZXZwb3dlcjJfMzIoKHVpbnQzMikKKwkJCQkJCQkJKE1heEFsbG9j
U2l6ZSAvIHNpemVvZihkc2FfcG9pbnRlcl9hdG9taWMpKSk7CiAKIAkJCQkJLyoKIAkJCQkJ
ICogV2UgcHJvYmFibHkgYWxzbyBuZWVkIGEgc21hbGxlciBidWNrZXQgYXJyYXkuICBIb3cg
bWFueQpAQCAtMTE2OCw4ICsxMTcxLDcgQEAgRXhlY1BhcmFsbGVsSGFzaEluY3JlYXNlTnVt
QmF0Y2hlcyhIYXNoSm9pblRhYmxlIGhhc2h0YWJsZSkKIAkJCQkJICovCiAJCQkJCWR0dXBs
ZXMgPSAob2xkX2JhdGNoMC0+bnR1cGxlcyAqIDIuMCkgLyBuZXdfbmJhdGNoOwogCQkJCQlk
YnVja2V0cyA9IGNlaWwoZHR1cGxlcyAvIE5UVVBfUEVSX0JVQ0tFVCk7Ci0JCQkJCWRidWNr
ZXRzID0gTWluKGRidWNrZXRzLAotCQkJCQkJCQkgICBNYXhBbGxvY1NpemUgLyBzaXplb2Yo
ZHNhX3BvaW50ZXJfYXRvbWljKSk7CisJCQkJCWRidWNrZXRzID0gTWluKGRidWNrZXRzLCBt
YXhfYnVja2V0cyk7CiAJCQkJCW5ld19uYnVja2V0cyA9IChpbnQpIGRidWNrZXRzOwogCQkJ
CQluZXdfbmJ1Y2tldHMgPSBNYXgobmV3X25idWNrZXRzLCAxMDI0KTsKIAkJCQkJbmV3X25i
dWNrZXRzID0gcGdfbmV4dHBvd2VyMl8zMihuZXdfbmJ1Y2tldHMpOwotLSAKMi40My4wCgo=


--------------t0ph7bTMSLB5ZGSO5Ff0hCJg--