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@xxxxxxxxxxxxx> wrote:
Vijaykumar Jain <vijaykumarjain.github@xxxxxxxxx> 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
