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

On Mon, Dec 4, 2017 at 9:39 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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:

Yeah, I agree with that. One similar leak has actually been fixed with
this commit, and the infrastructure of v10 has made this fix dead
simple:
commit: 0c25e9652461c08b5caef259a6af27a38707e07a
author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
date: Fri, 6 Oct 2017 14:28:42 -0400
Fix intra-query memory leakage in nodeProjectSet.c.
https://www.postgresql.org/message-id/20171005230321.28561.15927@wrigleys.postgresql.org

nodeProjectSet.c really makes tuple-level memory handling way easier
based on my studies of this code.
--
Michael