BUG #15225: [XX000] ERROR: invalid DSA memory alloc request size1073741824 / Where: parallel worker - Mailing list pgsql-bugs
| From | PG Bug reporting form |
|---|---|
| Subject | BUG #15225: [XX000] ERROR: invalid DSA memory alloc request size1073741824 / Where: parallel worker |
| Date | |
| Msg-id | 152802081668.26724.16985037679312485972@wrigleys.postgresql.org Whole thread Raw |
| Responses |
Re: BUG #15225: [XX000] ERROR: invalid DSA memory alloc request size1073741824 / Where: parallel worker
(Thomas Munro <thomas.munro@enterprisedb.com>)
|
| List | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 15225
Logged by: Frits Jalvingh
Email address: jal@etc.to
PostgreSQL version: 11beta1
Operating system: Ubuntu 18.04 64bit
Description:
Running the following:
explain (analyze, costs, verbose, buffers, timing, summary, format xml)
select
coalesce(tijd.tijdkey,'Unknown') as calender_id
, coalesce(eenheid_pe.id_s,-1) as eenheid_id
, sum(prijscomponent_pe.bedrag)::numeric(23,2) as kg00225
from tijd
cross join s_h_eenheid_ssm eenheid_pe
inner join l_prijscomponent_eenheid l_prijscomponent_eenheid_ps
on eenheid_pe.id_h_eenheid = l_prijscomponent_eenheid_ps.id_h_eenheid
inner join s_l_prijscomponent_eenheid_ssm
s_l_prijscomponent_eenheid_ssm_ps
on l_prijscomponent_eenheid_ps.id_l =
s_l_prijscomponent_eenheid_ssm_ps.id_l
and s_l_prijscomponent_eenheid_ssm_ps.dv_start_dts <= tijd.begindatum
and s_l_prijscomponent_eenheid_ssm_ps.dv_end_dts > tijd.begindatum
inner join s_h_prijscomponent_ssm prijscomponent_ps
on l_prijscomponent_eenheid_ps.id_h_prijscomponent =
prijscomponent_ps.id_h_prijscomponent
inner join s_h_eenheid_ssm eenheid_ps
on eenheid_pe.id_h_eenheid = eenheid_ps.id_h_eenheid
inner join l_prijscomponent_eenheid l_prijscomponent_eenheid_pe
on eenheid_pe.id_h_eenheid = l_prijscomponent_eenheid_pe.id_h_eenheid
inner join s_l_prijscomponent_eenheid_ssm
s_l_prijscomponent_eenheid_ssm_pe
on l_prijscomponent_eenheid_pe.id_l =
s_l_prijscomponent_eenheid_ssm_pe.id_l
and s_l_prijscomponent_eenheid_ssm_pe.dv_start_dts <= tijd.einddatum
and s_l_prijscomponent_eenheid_ssm_pe.dv_end_dts > tijd.einddatum
inner join s_h_prijscomponent_ssm prijscomponent_pe
on l_prijscomponent_eenheid_pe.id_h_prijscomponent =
prijscomponent_pe.id_h_prijscomponent
where eenheid_pe.dv_start_dts <= tijd.einddatum
and eenheid_pe.dv_end_dts > tijd.einddatum
and prijscomponent_ps.dv_start_dts <= tijd.einddatum
and prijscomponent_ps.dv_end_dts > tijd.einddatum
and eenheid_ps.dv_start_dts <= tijd.einddatum
and eenheid_ps.dv_end_dts > tijd.einddatum
and prijscomponent_pe.dv_start_dts <= tijd.einddatum
and prijscomponent_pe.dv_end_dts > tijd.einddatum
and ((prijscomponent_pe.soort = 'NET')
and (prijscomponent_ps.begindatum <= Tijd.begindatum and
(prijscomponent_ps.einddatum is null or prijscomponent_ps.einddatum >=
Tijd.begindatum))
and (eenheid_ps.inBezitbegindatum <= Tijd.einddatum and
(eenheid_ps.inBeziteinddatum is null or eenheid_ps.inBeziteinddatum >=
Tijd.einddatum)
and (eenheid_ps.bezitSoort = 'EIG' or (eenheid_ps.bezitSoort = 'BEH' and
eenheid_ps.bezitDetailsoort = 'BEC')))
and (eenheid_pe.inExploitatiedatum <= Tijd.einddatum and
(eenheid_pe.uitExploitatiedatum is null or eenheid_pe.uitExploitatiedatum >=
Tijd.begindatum))
)
group by
coalesce(tijd.tijdkey,'Unknown')
, coalesce(eenheid_pe.id_s,-1)
;
causes an abort after just a few seconds:
[XX000] ERROR: invalid DSA memory alloc request size 1073741824
Where: parallel worker
I have changed the default postgresql.conf file as follows:
work_mem = 2GB
shared_buffers=1GB
huge_pages = try
max_worker_processes = 8 # (change requires restart)
max_parallel_maintenance_workers = 2 # taken from max_parallel_workers
max_parallel_workers_per_gather = 2 # taken from max_parallel_workers
max_parallel_workers = 8
synchronous_commit = off
commit_delay = 100000
max_wal_size = 8GB
min_wal_size = 80MB
random_page_costs = 2.0
default_statistics_target = 500
My machine is an Ubuntu 18.04 64 bit up-to-date installation with Postgres
11 beta installed through the pgdg repo. psql -V reports:
psql (PostgreSQL) 11beta1 (Ubuntu 11~beta1-2.pgdg18.04+1)
The machine has an I7-4790K cpu with 32GB of memory.
The query does run successfully without the explain plan part.
Just running explain (analyse) also dies (but without the "in parallel
worker" part), as does explain (analyze, costs false, verbose false, buffers
false, timing false).
The execution plan of the query is:
[
{
"Plan": {
"Node Type": "Aggregate",
"Strategy": "Sorted",
"Partial Mode": "Finalize",
"Parallel Aware": false,
"Startup Cost": 1258419.25,
"Total Cost": 1258607.65,
"Plan Rows": 1462,
"Plan Width": 58,
"Group Key": ["(COALESCE(tijd.tijdkey, 'Unknown'::text))",
"(COALESCE(eenheid_pe.id_s, '-1'::integer))"],
"Plans": [
{
"Node Type": "Gather Merge",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 1258419.25,
"Total Cost": 1258573.54,
"Plan Rows": 1218,
"Plan Width": 68,
"Workers Planned": 2,
"Plans": [
{
"Node Type": "Aggregate",
"Strategy": "Sorted",
"Partial Mode": "Partial",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 1257419.22,
"Total Cost": 1257432.93,
"Plan Rows": 609,
"Plan Width": 68,
"Group Key": ["(COALESCE(tijd.tijdkey, 'Unknown'::text))",
"(COALESCE(eenheid_pe.id_s, '-1'::integer))"],
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 1257419.22,
"Total Cost": 1257420.75,
"Plan Rows": 609,
"Plan Width": 40,
"Sort Key": ["(COALESCE(tijd.tijdkey, 'Unknown'::text))",
"(COALESCE(eenheid_pe.id_s, '-1'::integer))"],
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": true,
"Join Type": "Inner",
"Startup Cost": 1132841.04,
"Total Cost": 1257391.06,
"Plan Rows": 609,
"Plan Width": 40,
"Inner Unique": false,
"Hash Cond": "(prijscomponent_pe.id_h_prijscomponent =
l_prijscomponent_eenheid_pe.id_h_prijscomponent)",
"Join Filter": "((prijscomponent_pe.dv_start_dts <=
tijd.einddatum) AND (prijscomponent_pe.dv_end_dts > tijd.einddatum))",
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": true,
"Relation Name": "s_h_prijscomponent_ssm",
"Alias": "prijscomponent_pe",
"Startup Cost": 0.00,
"Total Cost": 122485.93,
"Plan Rows": 541285,
"Plan Width": 24,
"Filter": "(soort = 'NET'::text)"
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": true,
"Startup Cost": 1132594.30,
"Total Cost": 1132594.30,
"Plan Rows": 19739,
"Plan Width": 44,
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": true,
"Join Type": "Inner",
"Startup Cost": 1065869.72,
"Total Cost": 1132594.30,
"Plan Rows": 19739,
"Plan Width": 44,
"Inner Unique": false,
"Hash Cond":
"(s_l_prijscomponent_eenheid_ssm_pe.id_l =
l_prijscomponent_eenheid_pe.id_l)",
"Join Filter":
"((s_l_prijscomponent_eenheid_ssm_pe.dv_start_dts <= tijd.einddatum) AND
(s_l_prijscomponent_eenheid_ssm_pe.dv_end_dts > tijd.einddatum))",
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": true,
"Relation Name":
"s_l_prijscomponent_eenheid_ssm",
"Alias":
"s_l_prijscomponent_eenheid_ssm_pe",
"Startup Cost": 0.00,
"Total Cost": 58323.93,
"Plan Rows": 1944093,
"Plan Width": 24
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": true,
"Startup Cost": 1063649.10,
"Total Cost": 1063649.10,
"Plan Rows": 177650,
"Plan Width": 48,
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": true,
"Join Type": "Inner",
"Startup Cost": 1001869.55,
"Total Cost": 1063649.10,
"Plan Rows": 177650,
"Plan Width": 48,
"Inner Unique": false,
"Hash Cond":
"(l_prijscomponent_eenheid_pe.id_h_eenheid = eenheid_pe.id_h_eenheid)",
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": true,
"Relation Name":
"l_prijscomponent_eenheid",
"Alias":
"l_prijscomponent_eenheid_pe",
"Startup Cost": 0.00,
"Total Cost": 53749.04,
"Plan Rows": 1944104,
"Plan Width": 12
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": true,
"Startup Cost": 1001843.22,
"Total Cost": 1001843.22,
"Plan Rows": 2106,
"Plan Width": 52,
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship":
"Outer",
"Parallel Aware": true,
"Join Type": "Inner",
"Startup Cost": 875758.08,
"Total Cost": 1001843.22,
"Plan Rows": 2106,
"Plan Width": 52,
"Inner Unique": false,
"Hash Cond":
"(prijscomponent_ps.id_h_prijscomponent =
l_prijscomponent_eenheid_ps.id_h_prijscomponent)",
"Join Filter":
"((prijscomponent_ps.dv_start_dts <= tijd.einddatum) AND
(prijscomponent_ps.dv_end_dts > tijd.einddatum) AND
(prijscomponent_ps.begindatum <= tijd.begindatum) AND
((prijscomponent_ps.einddatum IS NULL) OR (prijscomponent_ps.einddatum >=
tijd.begindatum)))",
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship":
"Outer",
"Parallel Aware": true,
"Relation Name":
"s_h_prijscomponent_ssm",
"Alias":
"prijscomponent_ps",
"Startup Cost": 0.00,
"Total Cost": 117615.34,
"Plan Rows": 1948234,
"Plan Width": 28
},
{
"Node Type": "Hash",
"Parent Relationship":
"Inner",
"Parallel Aware": true,
"Startup Cost":
874012.21,
"Total Cost": 874012.21,
"Plan Rows": 139670,
"Plan Width": 60,
"Plans": [
{
"Node Type": "Hash
Join",
"Parent Relationship":
"Outer",
"Parallel Aware":
true,
"Join Type":
"Inner",
"Startup Cost":
800541.50,
"Total Cost":
874012.21,
"Plan Rows": 139670,
"Plan Width": 60,
"Inner Unique":
false,
"Hash Cond":
"(s_l_prijscomponent_eenheid_ssm_ps.id_l =
l_prijscomponent_eenheid_ps.id_l)",
"Join Filter":
"((s_l_prijscomponent_eenheid_ssm_ps.dv_start_dts <= tijd.begindatum) AND
(s_l_prijscomponent_eenheid_ssm_ps.dv_end_dts > tijd.begindatum))",
"Plans": [
{
"Node Type": "Seq
Scan",
"Parent
Relationship": "Outer",
"Parallel Aware":
true,
"Relation Name":
"s_l_prijscomponent_eenheid_ssm",
"Alias":
"s_l_prijscomponent_eenheid_ssm_ps",
"Startup Cost":
0.00,
"Total Cost":
58323.93,
"Plan Rows":
1944093,
"Plan Width": 24
},
{
"Node Type":
"Hash",
"Parent
Relationship": "Inner",
"Parallel Aware":
true,
"Startup Cost":
784828.56,
"Total Cost":
784828.56,
"Plan Rows":
1257035,
"Plan Width":
64,
"Plans": [
{
"Node Type":
"Hash Join",
"Parent
Relationship": "Outer",
"Parallel
Aware": true,
"Join Type":
"Inner",
"Startup
Cost": 716394.59,
"Total Cost":
784828.56,
"Plan Rows":
1257035,
"Plan Width":
64,
"Inner
Unique": false,
"Hash Cond":
"(l_prijscomponent_eenheid_ps.id_h_eenheid = eenheid_pe.id_h_eenheid)",
"Plans": [
{
"Node
Type": "Seq Scan",
"Parent
Relationship": "Outer",
"Parallel
Aware": true,
"Relation
Name": "l_prijscomponent_eenheid",
"Alias":
"l_prijscomponent_eenheid_ps",
"Startup
Cost": 0.00,
"Total
Cost": 53749.04,
"Plan
Rows": 1944104,
"Plan
Width": 12
},
{
"Node
Type": "Hash",
"Parent
Relationship": "Inner",
"Parallel
Aware": true,
"Startup
Cost": 716131.57,
"Total
Cost": 716131.57,
"Plan
Rows": 21041,
"Plan
Width": 52,
"Plans":
[
{
"Node
Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join
Type": "Inner",
"Startup Cost": 700308.75,
"Total
Cost": 716131.57,
"Plan
Rows": 21041,
"Plan
Width": 52,
"Inner
Unique": false,
"Hash
Cond": "(eenheid_ps.id_h_eenheid = eenheid_pe.id_h_eenheid)",
"Join
Filter": "((eenheid_ps.dv_start_dts <= tijd.einddatum) AND
(eenheid_ps.dv_end_dts > tijd.einddatum) AND (eenheid_ps.inbezitbegindatum
<= tijd.einddatum) AND ((eenheid_ps.inbeziteinddatum IS NULL) OR
(eenheid_ps.inbeziteinddatum >= tijd.einddatum)))",
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": true,
"Relation Name": "s_h_eenheid_ssm",
"Alias": "eenheid_ps",
"Startup Cost": 0.00,
"Total Cost": 2695.46,
"Plan Rows": 32541,
"Plan Width": 28,
"Filter": "((bezitsoort = 'EIG'::text) OR ((bezitsoort = 'BEH'::text) AND
(bezitdetailsoort = 'BEC'::text)))"
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 687268.69,
"Total Cost": 687268.69,
"Plan Rows": 1043205,
"Plan Width": 48,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 0.00,
"Total Cost": 687268.69,
"Plan Rows": 1043205,
"Plan Width": 48,
"Inner Unique": false,
"Join Filter": "((eenheid_pe.dv_start_dts <= tijd.einddatum) AND
(eenheid_pe.dv_end_dts > tijd.einddatum) AND (eenheid_pe.inexploitatiedatum
<= tijd.einddatum) AND ((eenheid_pe.uitexploitatiedatum IS NULL) OR
(eenheid_pe.uitexploitatiedatum >= tijd.begindatum)))",
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "s_h_eenheid_ssm",
"Alias": "eenheid_pe",
"Startup Cost": 0.00,
"Total Cost": 2679.19,
"Plan Rows": 55319,
"Plan Width": 32
},
{
"Node Type": "Materialize",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 0.00,
"Total Cost": 18.25,
"Plan Rows": 550,
"Plan Width": 40,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "tijd",
"Alias": "tijd",
"Startup Cost": 0.00,
"Total Cost": 15.50,
"Plan Rows": 550,
"Plan Width": 40
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
},
"JIT": {
"Functions": 108,
"Inlining": true,
"Optimization": true
}
}
]
pgsql-bugs by date: