Re: DO with a large amount of statements get stuck with high memory consumption

On Mon, Jul 18, 2016 at 9:43 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> > BTW, while the fix does address the cleanup performance issue, it's
> > still the case that anonymous code blocks burn up lots of resident
> > memory (my 315k example I tested with ate around 8gb IIRC) when run
> > like this. My question is, if the pl/pgsql code block is anonymous
> > and not in some kind of a loop, why bother caching the plan at all?
>
> Nobody got around to it. Also, as you note, it's not as simple as
> "don't cache if in a DO block". You'd need to track whether you were
> inside any sort of looping construct. Depending on how difficult
> that turned out to be, it might add overhead to regular functions
> that we don't want.

Agreed. And from the structures themselves it is not really easy to detect
if inside of a loop, the toplevel, while, for and if all use the same
statement
block and call exec_stmts(), which in turn calls exec_stmt() for each
element in that list. It is not impossible to add a flag, set at PL compile
time, to that element and check it every time, the statement is executed.
But such a change definitely needs more testing and probably won't
qualify for backpatching.

In the meantime, would it be appropriate to backpatch the double linking
of memory context children at this time? I believe it has had plenty of
testing in the 9.6 cycle to be sure it didn't break anything.

Regards, Jan

--
Jan Wieck
Senior Postgres Architect
http://pgblog.wi3ck.info