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

From Vijaykumar Jain
Subject Re: TRUNCATE memory leak with temporary tables?
Date
Msg-id CAM+6J95nk+MKRx=_bC4UwFR0D1VRwatDKZnxKkKFfz=P_sVZBA@mail.gmail.com
Whole thread Raw
In response to Re: TRUNCATE memory leak with temporary tables?  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
i tried to reproduce tracking mem allocation.

demo=# DO $$
        DECLARE    i bigint;
        BEGIN
                CREATE TEMPORARY TABLE pg_temp.foo (id int) with ( AUTOVACUUM_ENABLED = 0, TOAST.AUTOVACUUM_ENABLED = 0 );
                FOR i IN 1..200000000 LOOP
                        TRUNCATE pg_temp.foo;
                END LOOP;
        END
$$;

in a parallel tmux session.

strace -p 1620 --trace=memory

no movement/ no new output



****************************
when i replace the col with type text.

demo=# DO $$
        DECLARE    i bigint;
        BEGIN
                CREATE TEMPORARY TABLE pg_temp.foo (id text) with ( AUTOVACUUM_ENABLED = 0, TOAST.AUTOVACUUM_ENABLED = 0 );
                FOR i IN 1..200000000 LOOP
                        TRUNCATE pg_temp.foo;
                END LOOP;
        END
$$;

strace -p 1620 --trace=memory
strace: Process 1620 attached
--- SIGINT {si_signo=SIGINT, si_code=SI_USER, si_pid=1878, si_uid=1001} ---
brk(0x556c502ad000)                     = 0x556c502ad000
brk(0x556c502ed000)                     = 0x556c502ed000
brk(0x556c5036d000)                     = 0x556c5036d000
brk(0x556c5046d000)                     = 0x556c5046d000
brk(0x556c5066d000)                     = 0x556c5066d000
brk(0x556c50a6d000)                     = 0x556c50a6d000
brk(0x556c5126d000)                     = 0x556c5126d000

it seems it does try memory allocation repeatedly.
I am not a C developer :), please ignore if i am diverting.




On Fri, 28 May 2021 at 18:52, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Vijaykumar Jain <vijaykumarjain.github@gmail.com> writes:
> I too see growth when text type is used, but not when int or even fixed
> size char(10) is used.
> ...
> 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.

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


--
Thanks,
Vijay
Mumbai, India

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: TRUNCATE memory leak with temporary tables?
Next
From: "Nick Muerdter"
Date:
Subject: Re: TRUNCATE memory leak with temporary tables?