Re: Memory exhaustion due to temporary tables?

Thomas Carroll <tomfecarroll(at)yahoo(dot)com> writes:
> On Monday, December 10, 2018, 5:50:22 PM EST, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Also, as mentioned upthread, it'd be interesting to see if there's
>> a memory context dump showing up in your server log.

> There are many memory context dumps, and they are long (well over 100 lines apiece).  To me these are unfamiliar and hard to read, but I tried to pick one that referenced all the same tables used in the function.  I put one such dump (188 lines) here:
> https://drive.google.com/file/d/1QI4nffdZByIehb_-GULOagI_dKpKElg_/view

Thanks. Here's the smoking gun:

CacheMemoryContext: 221241400 total in 38 blocks; 152192 free (90 chunks); 221089208 used

This looks like something is leaking memory directly in CacheMemoryContext
(not in any of its child contexts). Can you confirm that all the memory
context dumps in your logs are showing similarly very-large total space
values for CacheMemoryContext?

This doesn't seem to be related to temp table usage, at least not
directly. (As a cross-check, I created and dropped 10000 temp tables in a
session, and the process's VM footprint didn't budge.) My guess is that
something else you are doing is tickling the leak.

You said you'd been able to reproduce this problem outside production.
Any chance you could boil that down to a sharable test case? I'd bet
long odds that it's not related to your data particularly, but rather
to your schema or something your function is doing, so you probably
could reproduce it with some dummy data (and, perhaps, not even very
much of that; repeating some DDL action many times is probably what
is making the leak grow to something noticeable).

If I had to guess at this point, I'd wonder if it's something to do
with partitioned tables. That code is still relatively wet behind
the ears, and memory leaks are the kind of bug I'd be expecting to
still be shaking out of it now. But you might not be using partitioned
tables at all. Anyway, anything you're doing with new-in-v10 features,
particularly schema-related features, would be a good bet for the
source of the problem.

regards, tom lane