|
thank you, I will look into the work-around !
From: Tom Lane <tgl@xxxxxxxxxxxxx>
Sent: Monday, 4 December 2017 11:39 AM To: Yuri Budilov Cc: rob stone; pgsql-general@xxxxxxxxxxxxxxxxxxxx Subject: Re: JSON out of memory error on PostgreSQL 9.6.x Yuri Budilov <yuri.budilov@xxxxxxxxxxx> writes:
> The out-of-memory error happens if I also retrieve another JSON Column like so: > CREATE TABLE csnbi_stg.junk4 > AS > SELECT > json_rmq->>'totalSize' as totalSize, -- this plus array below causes out of memory error > json_array_elements(json_rmq -> 'orders'::text) AS orders > FROM csnbi_stg.stg_rmq_prod_test_json_raw_jmcn stg_rmq_json_raw Ah. The problem here is that "json_rmq->>'totalSize'" leaks some memory on each execution, and it's executed again for each row produced by the json_array_elements() SRF, and the memory can't be reclaimed until we've finished the full output cycle for the SRF. So the leakage (which is more or less of the size of the JSON value, I think) accumulates across 150K executions in this example. This is fixed as of v10. It seems impractical to do anything about it in previous release branches, although you could reformulate your query to avoid it by not having any other _expression_ evaluations occurring in the same tlist as the SRF. Something like this should work: SELECT totalSize, json_array_elements(json_rmq -> 'orders'::text) AS orders FROM (SELECT json_rmq->>'totalSize' as totalSize, json_rmq FROM csnbi_stg.stg_rmq_prod_test_json_raw_jmcn stg_rmq_json_raw WHERE ... OFFSET 0) ss; regards, tom lane |
