Collected from the PG bugs email list.
|OS||Centos 7, Linux 3.10|
|Reported by||Rikard Pavelic|
Body of first available message related to this bug follows.
The following bug has been logged on the website: Bug reference: 16389 Logged by: Rikard Pavelic Email address: (redacted) PostgreSQL version: 12.2 Operating system: Centos 7, Linux 3.10 Description: Hi, we are testing upgrade from 11 to 12 and encountered some strange OOMs. This one was consistently happening on several instances, while it passed normally on many other ones: org.postgresql.util.PSQLException:ERROR: out of memory Detail: Failed on request of size 52 in memory context "ExecutorState". Where: "WITH source AS ( SELECT i, row_number() over () as ord, (t.old).amount_array AS old, (t.new).amount_array AS new, coalesce(array_upper((t.old).amount_array, 1), 0) AS old_count, coalesce(array_upper((t.new).amount_array, 1), 0) AS new_count, (SELECT array_agg(row(_r.pk1, _r.pk2)) FROM unnest((t.old).amount_array) _r) as old_pks, (SELECT array_agg(row(_r.pk1, _r.pk2)) FROM unnest((t.new).amount_array) _r) as new_pks, (SELECT array_agg(i) FROM generate_series(1, CASE WHEN coalesce(array_upper((t.old).amount_array, 1), 0) > coalesce(array_upper((t.new).amount_array, 1), 0) THEN array_upper((t.old).amount_array, 1) ELSE array_upper((t.new).amount_array, 1) END) i) as indexes FROM global_temp_input_table t WHERE NOT (t.old).amount_array IS NULL AND (t.new).amount_array IS NULL OR (t.old).amount_array IS NULL AND NOT (t.new).amount_array IS NULL OR NOT (t.old).amount_array IS NULL AND NOT (t.new).amount_array IS NULL AND (t.old).amount_array != (t.new).amount_array ) INSERT INTO global_temp_update SELECT i, index, old[index] AS old, CASE WHEN old_pks[index] = new_pks[index] THEN new[index] WHEN NOT COALESCE(old_pks[index] = ANY (new_pks), false) THEN null ELSE (select n from unnest(new) n where n.pk1 = old[index].pk1 AND n.pk2 = old[index].pk2) END AS changed, new[index] AS new, CASE WHEN old_pks[index] = new_pks[index] THEN false ELSE NOT new_pks[index] IS NULL AND NOT COALESCE(new_pks[index] = ANY (old_pks), false) END AS is_new FROM ( SELECT i as _i, ord as _ord, unnest((SELECT array_agg(i) FROM generate_series(1, CASE WHEN old_count > new_count THEN old_count ELSE new_count END) i)) as index FROM source s ) ix INNER JOIN source sq ON ix._i = sq.i AND ix._ord = sq.ord" The global input temp tables look like this CREATE UNLOGGED TABLE global_temp_input_table ( i integer, old data_source_view, new data_source_view ); where data_source_view is an view with an array of composites (amount_array) which gets unnested and compared here. While the query is not optimal, the OOMs stopped after we put MATERIALIZED on top (which does not seem necessary as it's used twice). Anyway, it's not an immediate problem for us anymore, as we've rewritten it into a saner form, but it hints at some underlying issue with recent CTE optimization. Regards, Rikard
|2020-04-25 07:08:06+00||PG Bug reporting form||BUG #16389: OOM on CTE since version 12|
|2020-04-25 13:13:02+00||Tomas Vondra||Re: BUG #16389: OOM on CTE since version 12|