Re: JSON out of memory error on PostgreSQL 9.6.x

Yuri Budilov <yuri(dot)budilov(at)hotmail(dot)com> 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