Subject: Re: plpgsql memory leaks

From: Michael Banck <[email-masked]>
Date: Fri, 12 Jan 2024 14:53:45 +0100

Hi,

On Fri, Jan 12, 2024 at 01:35:24PM +0100, Pavel Stehule wrote:
> pá 12. 1. 2024 v 11:54 odesílatel Michael Banck <[email-masked]> napsal:
> > Which version of Postgres is this and on which platform/distribution?
> 
> It was tested on master branch (pg 17) on Fedora 39
> 
> > Did you try keep jit on but set jit_inline_above_cost to 0?
> >
> > The back-branches have a fix for the above case, i.e. llvmjit memleaks
> > that can be worked-around by setting jit_inline_above_cost=0.

I got that wrong, it needs to be -1 to disable it.

But if you are already running the master branch, it is probably a
separate issue.

Michael

---

From: Pavel Stehule <[email-masked]>
Date: Fri, 12 Jan 2024 20:09:14 +0100

> But if you are already running the master branch, it is probably a

I tested code

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
    s := 0;
    for r in c(i*10000)
    loop
      s := s + r.i;
    end loop;
    raise notice '%=%', i, s;
  end loop;
end;
$function$

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 :-/

Regards

Pavel

---

From: Tom Lane <[email-masked]>
Date: Fri, 12 Jan 2024 16:25:53 -0500

> so it looks like the wrong hypothesis , and the problem is maybe somewhere

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.

			regards, tom lane

---

From: Pavel Stehule <[email-masked]>
Date: Sat, 13 Jan 2024 06:25:04 +0100

Hi

> 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