Re: Memory exhaustion due to temporary tables?

Thomas Carroll <tomfecarroll(at)yahoo(dot)com> writes:
> On Monday, December 10, 2018, 3:45:33 PM EST, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I imagine you checked this already, but ... what is temp_buffers set to?

> Thanks for your reply!  temp_buffers is the default 8MB, and I should have included that in my first email.

Hm. Well, the temporary-buffer arena definitely won't grow any larger
than that, so the problem is somewhere else.

> WRT temp tables and autovacuum: I realize I need to add an important detail here: The table is created by the function using:
> CREATE TEMPORARY TABLE table_name ON COMMIT DROP AS SELECT...
> So my perhaps-wrong expectation is that all remnants of the old temporary table are discarded from the previous invocation, so there is no need to do any vacuuming.

I see. The contents of the temp tables certainly go away at commit,
then, but the system catalog entries describing a temp table are just
as real as those for a regular table. So if you're creating and
dropping temp tables quickly, there's a potential for bloat in the
system catalogs (particularly pg_attribute), which autovacuum might
or might not keep up with at default settings. Still, I'd only expect
that to lead to disk space growth not memory consumption.

Is the error message spelling really exactly "Cannot allocate memory"?
Because that string appears nowhere in the Postgres backend sources,
and I don't think it's strerror's canonical phrasing for ENOMEM either.
So I'm wondering just where it's coming from.

Also, as mentioned upthread, it'd be interesting to see if there's
a memory context dump showing up in your server log. It'd look
something roughly like this:

TopMemoryContext: 67440 total in 5 blocks; 14016 free (27 chunks); 53424 used
TopTransactionContext: 32768 total in 3 blocks; 14992 free (21 chunks); 17776 used
Combo CIDs: 8192 total in 1 blocks; 1544 free (0 chunks); 6648 used
LocalBufferContext: 8397248 total in 8 blocks; 7936 free (0 chunks); 8389312 used
Local Buffer Lookup Table: 32768 total in 3 blocks; 6368 free (7 chunks); 26400 used
... lots more ...
Grand total: 9603680 bytes in 238 blocks; 283976 free (240 chunks); 9319704 used

regards, tom lane