TRUNCATE memory leak with temporary tables? - Mailing list pgsql-general

From Nick Muerdter
Subject TRUNCATE memory leak with temporary tables?
Date
Msg-id 88986113-6b01-452b-89d0-9492b6a79e33@www.fastmail.com
Whole thread Raw
Responses Re: TRUNCATE memory leak with temporary tables?
Re: TRUNCATE memory leak with temporary tables?
List pgsql-general
I've been seeing what looks like unbounded memory growth (until the OOM killer kicks in and kills the postgres process)
whenrunning a pl/pgsql function that performs TRUNCATE statements against various temporary tables in a loop. I think
I'vebeen able to come up with some fairly simple reproductions of the issue in isolation, but I'm trying to figure out
ifthis 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
Dockercontainers 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..200000000 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..200000000 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..200000000 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



pgsql-general by date:

Previous
From: Gmail
Date:
Subject: Re: How different is AWS-RDS postgres?
Next
From: "Dean Gibson (DB Administrator)"
Date:
Subject: AWS forcing PG upgrade from v9.6 a disaster