TRUNCATE memory leak with temporary tables?
I've been seeing what looks like unbounded memory growth (until the OOM killer kicks in and kills the postgres process) when running a pl/pgsql function that performs TRUNCATE statements against various temporary tables in a loop. I think I've been able to come up with some fairly simple reproductions of the issue in isolation, but I'm trying to figure out if this is a memory leak or of I'm perhaps doing something wrong with tuning or other settings. What I've observed: - The memory growth occurs if the temp table has indexes or a primary key set on it. - Alternatively, the memory growth also occurs if the temp table has certain column types on it (eg, "text" types). - If the table doesn't have indexes and only has integer columns present, then the memory growth does *not* occur. - I originally saw this against a PostgreSQL 12 server, but I've tested this against PostgreSQL 9.6.22, 12.7, and 13.3 Docker containers and reproduced it against all versions in the containers. Here are 2 separate examples that seem to show the memory growth on the server (the first being a table with a "text" column, the second example having no text column but a primary key index): DO $$ DECLARE i bigint; BEGIN CREATE TEMPORARY TABLE pg_temp.foo (id integer, bar text); FOR i IN 1..2 LOOP TRUNCATE pg_temp.foo; END LOOP; END $$ DO $$ DECLARE i bigint; BEGIN CREATE TEMPORARY TABLE pg_temp.foo (id integer); ALTER TABLE pg_temp.foo ADD PRIMARY KEY (id); FOR i IN 1..2 LOOP TRUNCATE pg_temp.foo; END LOOP; END $$ Compare that to this example (which doesn't have an index or any other column types that trigger this), which does *not* show any memory growth: DO $$ DECLARE i bigint; BEGIN CREATE TEMPORARY TABLE pg_temp.foo (id integer); FOR i IN 1..2 LOOP TRUNCATE pg_temp.foo; END LOOP; END $$ Any help in determining what's going on here (or if there are other ways to go about this) would be greatly appreciated! Thank you! Nick
Re: TRUNCATE memory leak with temporary tables?
On Fri, May 28, 2021, at 7:22 AM, Tom Lane wrote: > The text column would cause the table to have an associated toast table [1], > which in turn would have an index. Both of those would be reallocated as > new files on-disk during TRUNCATE, just like the table proper. > > A plausible theory here is that TRUNCATE leaks some storage associated > with an index's relcache entry, but not any for a plain table. > > regards, tom lane > > [1] https://www.postgresql.org/docs/current/storage-toast.html Yeah, I forgot to mention this originally, but I see memory growth against a "varchar(501)" field, but *not* against a "varchar(500)" field, so I was wondering if there was some length threshold that triggered something with toast table behavior somehow involved. But if the toast table involves an index, then maybe all of this gets back to just the indexes like you say. And I originally thought this issue was limited to temp tables, but now I'm not so sure. I seem to be able to reproduce the memory growth against regular tables (both normal and UNLOGGED) too: DO $$ DECLARE i bigint; BEGIN CREATE TABLE public.foo (id integer, bar text); FOR i IN 1..2 LOOP TRUNCATE public.foo; END LOOP; END $$ The memory growth seems to be slower in this case, so maybe that's why I didn't catch it earlier, but I think it's maybe growing at the same rate, it's just that this loop goes slower against real tables than temp tables. For example, I see similar total memory growth by the time this reaches 100,000 loops for either temp or non-temp tables, the temp version just reaches that point a lot more quickly (which makes sense). Thanks! Nick
