Re: plpgsql memory leaks

Hi

pá 12. 1. 2024 v 22:25 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:

> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> > default master branch - res 190MB ram
> > jit_inline_above_cost = -1 doesn't helps
> > disabling JIT doesn't helps too,
>
> > so it looks like the wrong hypothesis , and the problem is maybe
> somewhere
> > else :-/
>
> I see no leak with these examples on HEAD, either with or without
> --enable-llvm --- the process size stays quite stable according
> to "top". I wonder if you are using some extension that's
> contributing to the problem.
>

memory info after DO $$ BEGIN END $$;

(2024-01-13 05:36:46) postgres=# do $$ begin end $$;
DO
(2024-01-13 05:37:16) postgres=# select meminfo();
NOTICE: Total non-mmapped bytes (arena): 1114112
NOTICE: # of free chunks (ordblks): 11
NOTICE: # of free fastbin blocks (smblks): 0
NOTICE: # of mapped regions (hblks): 2
NOTICE: Bytes in mapped regions (hblkhd): 401408
NOTICE: Max. total allocated space (usmblks): 0
NOTICE: Free bytes held in fastbins (fsmblks): 0
NOTICE: Total allocated space (uordblks): 1039216
NOTICE: Total free space (fordblks): 74896
NOTICE: Topmost releasable block (keepcost): 67360

after script execution

NOTICE: ("1165 kB","1603 kB","438 kB")
NOTICE: Total non-mmapped bytes (arena): 22548480
NOTICE: # of free chunks (ordblks): 25
NOTICE: # of free fastbin blocks (smblks): 0
NOTICE: # of mapped regions (hblks): 2
NOTICE: Bytes in mapped regions (hblkhd): 401408
NOTICE: Max. total allocated space (usmblks): 0
NOTICE: Free bytes held in fastbins (fsmblks): 0
NOTICE: Total allocated space (uordblks): 1400224
NOTICE: Total free space (fordblks): 21148256
NOTICE: Topmost releasable block (keepcost): 20908384

so attached memory is 20MB - but is almost free. The sum of memory context
is very stable without leaks (used 1165kB).

but when I modify the script to

CREATE OR REPLACE FUNCTION public.fx(iter integer)
RETURNS void
LANGUAGE plpgsql
AS $function$
declare
c cursor(m bigint) for select distinct i from generate_series(1, m) g(i);
t bigint;
s bigint;
begin
for i in 1..iter
loop
open c(m := i * 10000);
s := 0;
loop
fetch c into t;
exit when not found;
s := s + t;
end loop;
raise notice '===========before close';
raise notice '%', (select (pg_size_pretty(sum(used_bytes)),
pg_size_pretty(sum(total_bytes)), pg_size_pretty(sum(free_bytes))) from
pg_get_backend_memory_contexts());
--perform meminfo();
raise notice '-----------after close';
close c;
raise notice '%=%', i, s;
raise notice '%', (select (pg_size_pretty(sum(used_bytes)),
pg_size_pretty(sum(total_bytes)), pg_size_pretty(sum(free_bytes))) from
pg_get_backend_memory_contexts());
--perform meminfo();
end loop;
end;
$function$

meminfo is simple extension - see the attachment, I got interesting things

NOTICE: ===========before close
NOTICE: ("149 MB","154 MB","5586 kB")
NOTICE: Total non-mmapped bytes (arena): 132960256
NOTICE: # of free chunks (ordblks): 49
NOTICE: # of free fastbin blocks (smblks): 0
NOTICE: # of mapped regions (hblks): 4
NOTICE: Bytes in mapped regions (hblkhd): 51265536
NOTICE: Max. total allocated space (usmblks): 0
NOTICE: Free bytes held in fastbins (fsmblks): 0
NOTICE: Total allocated space (uordblks): 110730576
NOTICE: Total free space (fordblks): 22229680
NOTICE: Topmost releasable block (keepcost): 133008

so this script really used mbytes memory, but it is related to query
`select distinct i from generate_series(1, m) g(i);`

This maybe is in correlation to my default work mem 64MB - when I set work
mem to 10MB, then it consumes only 15MB

So I was confused because it uses only about 3x work_mem, which is not too
bad.

Regards

Pavel

>
> regards, tom lane
>