Re: BUG #16925: ERROR: invalid DSA memory alloc request size 1073741824 CONTEXT: parallel worker

From: Andrei Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru>
To: Thomas Munro <thomas(dot)munro(at)gmail(dot)com>, PostgreSQL mailing lists <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Cc: "a(dot)rybakina" <a(dot)rybakina(at)postgrespro(dot)ru>
Subject: Re: BUG #16925: ERROR: invalid DSA memory alloc request size 1073741824 CONTEXT: parallel worker
Date: 2023-12-06 04:46:08
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-bugs

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';
> 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).

FROM bigger_than_it_looks a
JOIN bigger_than_it_looks b ON
LEFT JOIN bigger_than_it_looks c ON =;

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.

Andrei Lepikhov
Postgres Professional

Attachment Content-Type Size
0001-Bugfix.-Guard-total-number-of-hash-table-buckets.patch text/plain 1.4 KB

In response to


Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2023-12-06 04:49:39 BUG #18230: Redundant comparison of a local variable 'tzp' address with a NULL value at dt_common.c
Previous Message Kyotaro Horiguchi 2023-12-06 02:32:35 Re: BUG #18213: Standby's repeatable read isolation level transaction encountered a "nonrepeatable read" problem