How to free memory in plpgsql?

Question

I write a UDF in PostgreSQL to test my index efficiency, like this

CREATE OR REPLACE PROCEDURE query()
LANGUAGE plpgsql
AS
$$
DECLARE
i int;
exp text;
temprow RECORD;
t timestamp;
cnt int;
sum int;
BEGIN
set max_parallel_workers_per_gather = 0;
set enable_seqscan = false;

drop table if exists query_ranges;
create table query_ranges(box boxndf, ts timestamp, te timestamp);
FOR i IN 1 .. 5000 LOOP
    INSERT INTO query_ranges SELECT something;
END LOOP;

sum = 0;
t = clock_timestamp();
FOR temprow IN
    SELECT * FROM query_ranges
LOOP
    select count(*) from data_table where something into cnt;
    sum = sum + cnt;
    COMMIT;
END LOOP;
RAISE NOTICE '%', clock_timestamp() - t;

END;
$$;

However I find the memory usage keep growing and then I get a OOM. I guess the query select count(*) from data_table where something into cnt; may alloc some memory. If I simply run one query, the memory will soon be freed. But if I use this UDF, the memory is not freed between loops of queries.

Are there any way to free these allocated memory or avoid OOM?

user_406691 · Answer

you cannot control memory in PL/pgSQL. Postgres has hierarchical memory control - some parts of memory are released when a) the row is processed, the query is processed, the transactions is processed. The procedures in Postgres are little bit strange creatures because they are half inside transaction and half outside transaction, so there can be some problem in Postgres procedures.

There was lot of fixed errors related to procedures, so check if your Postgres is fresh and updated. If it doesn't help, then report this as Postgres bug to Postgres community.

I tested following example on Postgres 13.4 and it is working without any problems:

postgres=# create or replace procedure foo()
as $$
declare r record; cnt int; s int;
begin
  s := 0;
  for r in select * from pg_proc union all select * from pg_proc loop
    select count(*) from pg_proc into cnt;
    s := s + cnt; commit;
  end loop;
end;
$$ language plpgsql;

Maybe you use some extension. I don't know the datat type boxndf. There can be some issue in this extension too.

How to free memory in plpgsql?

1 Answers