Re: Memory exhaustion due to temporary tables?

On Monday, December 10, 2018, 3:45:33 PM EST, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

Thomas Carroll <tomfecarroll(at)yahoo(dot)com> writes:
> Postgres version: 10.5.  work_mem setting: 4MB, shared_buffers setting: 800 MB, connections typically around 30-40.

I imagine you checked this already, but ... what is temp_buffers set to?
That constrains the maximum memory used for temporary-table buffers in
each process, and an unreasonable setting for it could lead to the
described behavior.

Another thing to keep in mind with long-lived "temporary" tables is
that autovacuum can't do anything with them; so it's incumbent on your
application to periodically VACUUM and/or ANALYZE them as needed.
Otherwise such tables will bloat, which could contribute to excessive
use of temporary-table buffers.

            regards, tom lane
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.

> Another thing to keep in mind with long-lived "temporary" tables...

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.
Thanks again,Tom