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

Truncate is not delete + vaccum.

It creates a new empty table , followed by rename of the existing table to the new empty table and finally dropping of the old table.

On May 28, 2021 at 7:05 AM, Vijaykumar Jain <vijaykumarjain.github@gmail.com> wrote:

Yes,

I too see growth when text type is used, but not when int or even fixed size char(10) is used.

I always thought truncate was similar to delete + vacuum full, 

but checking for your scenarios, I did not see an update on pg_stat_user_table on truncate for vacuums.

then i checked 

PostgreSQL Internals: TRUNCATE (pykello.github.io)

to help understand truncation better.

but then i still do not understand how a col type text which is dynamic results in mem growth (coz there are no rows inserted, i understand for long strings db does work to compress, move them to toast tables etc) but these are empty rows.

Maybe someone else will be able to explain what is going on.

On Fri, 28 May 2021 at 06:52, Nick Muerdter <stuff@nickm.org> wrote:

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..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

--

Thanks,

Vijay

Mumbai, India