Re: Memory exhaustion due to temporary tables?

On Monday, December 10, 2018, 5:50:22 PM EST, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Is the error message spelling really exactly "Cannot allocate memory"?
Yes - but I think the message is forwarded from Linux.  Here is an example:
2018-12-08 00:00:00.070 EST,,,32506,,5bc71a25.7efa,25643,,2018-10-17 07:16:53 EDT,,0,LOG,00000,"could not fork autovacuum worker process: Cannot allocate memory",,,,,,,,,""
Should have been more thoughtful in what I chose to mention.  Here is one that may be more familiar, and directly related to the issue I am encountering:

2018-12-08 00:00:03.642 EST,"schema_name","db_name",24949,"10.12.113.56:50138",5bfed8cd.6175,17637,"SELECT",2018-11-28 13:05:01 EST,24/223502,0,ERROR,53200,"out of memory","Failed on request of size 24.",,,,"SQL statement ""CREATE TEMPORARY TABLE table_name ON COMMIT DROP AS

> 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:
tc.log

|
|
|
| | |

|

|
|
| |
tc.log

|

|

|

A hopefully-helpful short excerpt from the top:
TopMemoryContext: 174216 total in 7 blocks; 12520 free (27 chunks); 161696 used
  pgstat TabStatusArray lookup hash table: 8192 total in 1 blocks; 632 free (0 chunks); 7560 used
  TopTransactionContext: 24576 total in 2 blocks; 7288 free (3 chunks); 17288 used
    Combo CIDs: 8192 total in 1 blocks; 1800 free (0 chunks); 6392 used
    ExecutorState: 8192 total in 1 blocks; 7400 free (0 chunks); 792 used
      ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
    SPI Exec: 2088960 total in 8 blocks; 865976 free (7 chunks); 1222984 used
      ExecutorState: 262216 total in 6 blocks; 94344 free (11 chunks); 167872 used
        HashTableContext: 8192 total in 1 blocks; 7584 free (0 chunks); 608 used
          HashBatchContext: 197104 total in 6 blocks; 0 free (0 chunks); 197104 used
        HashTableContext: 8192 total in 1 blocks; 7968 free (0 chunks); 224 used
          HashBatchContext: 49240 total in 2 blocks; 8136 free (0 chunks); 41104 used
        HashTableContext: 8192 total in 1 blocks; 7584 free (0 chunks); 608 used
          HashBatchContext: 197104 total in 6 blocks; 0 free (0 chunks); 197104 used

Anything I can do to zero in on helpful information, I'm all ears.

Thanks so much!Tom