When deleting the plpgsql function, release the CachedPlan of the function

zengman <zengman@halodbtech.com>
From: zengman <zengman@halodbtech.com>
To: pgsql-hackers <pgsql-hackers@lists.postgresql.org>
Date: 2025-08-18T06:50:35Z
Lists: pgsql-hackers
Attachments

00001_free_function_memory.patch (application/octet-stream)
Hi, hackers


I have observed an issue where the CachedPlan corresponding to a function/procedure is not released when we execute the "DROP FUNCTION\PROCEDURE" command. A patch to resolve this problem is attached.


A simple test case is as follows:


Step 1 ：
create or replace procedure test_pro() as $$declare
&nbsp; &nbsp; &nbsp; &nbsp; va int default 100;
begin
&nbsp; &nbsp; &nbsp; &nbsp; for i in 1 .. 10 loop
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; va := va + i;
&nbsp; &nbsp; &nbsp; &nbsp; end loop;


&nbsp; &nbsp; &nbsp; &nbsp; raise notice '%', va;
&nbsp; &nbsp; &nbsp; &nbsp; va := va;

end $$ LANGUAGE plpgsql;


Step 2：
call test_pro();


Step 3：
select * from pg_get_backend_memory_contexts() where parent = 'CacheMemoryContext' and name = 'CachedPlan';


Step 4：
drop procedure test_pro;


Step 5：
select * from pg_get_backend_memory_contexts() where parent = 'CacheMemoryContext' and name = 'CachedPlan';


Step 6：
create or replace procedure test_pro() as $$declare
&nbsp; &nbsp; &nbsp; &nbsp; va int default 100;
begin
&nbsp; &nbsp; &nbsp; &nbsp; for i in 1 .. 10 loop
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; va := va + i;
&nbsp; &nbsp; &nbsp; &nbsp; end loop;


&nbsp; &nbsp; &nbsp; &nbsp; raise notice '%', va;
&nbsp; &nbsp; &nbsp; &nbsp; va := va;

end $$ LANGUAGE plpgsql;


Step 7：
call test_pro();


Step 8：
select * from pg_get_backend_memory_contexts() where parent = 'CacheMemoryContext' and name = 'CachedPlan';




result：
postgres@zxm-VMware-Virtual-Platform:/data/16$ psqlpsql (16.10)
Type "help" for help.


postgres=# create or replace procedure test_pro() as $$
declare
&nbsp; &nbsp; &nbsp; &nbsp; va int default 100;
begin
&nbsp; &nbsp; &nbsp; &nbsp; for i in 1 .. 10 loop
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; va := va + i;
&nbsp; &nbsp; &nbsp; &nbsp; end loop;


&nbsp; &nbsp; &nbsp; &nbsp; raise notice '%', va;
&nbsp; &nbsp; &nbsp; &nbsp; va := va;
end $$ LANGUAGE plpgsql;
CREATE PROCEDURE
postgres=# call test_pro();
NOTICE: &nbsp;155
CALL
postgres=# select * from pg_get_backend_memory_contexts() where parent = 'CacheMemoryContext' and name = 'CachedPlan';
&nbsp; &nbsp; name &nbsp; &nbsp;| &nbsp; &nbsp;ident &nbsp; &nbsp; | &nbsp; &nbsp; &nbsp; parent &nbsp; &nbsp; &nbsp; | level | total_bytes | total_nblocks | free_bytes | free_chunks | used_bytes 
------------+--------------+--------------------+-------+-------------+---------------+------------+-------------+------------
&nbsp;CachedPlan | va := va &nbsp; &nbsp; | CacheMemoryContext | &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;2048 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;576 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; 1472
&nbsp;CachedPlan | va &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | CacheMemoryContext | &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;2048 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;584 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; 1464
&nbsp;CachedPlan | va := va + i | CacheMemoryContext | &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;2048 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;384 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; 1664
&nbsp;CachedPlan | 10 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | CacheMemoryContext | &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;2048 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;544 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; 1504
&nbsp;CachedPlan | 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| CacheMemoryContext | &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;2048 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;544 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; 1504
&nbsp;CachedPlan | 100 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| CacheMemoryContext | &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;2048 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;544 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; 1504
(6 rows)


postgres=# drop procedure test_pro;
DROP PROCEDURE
postgres=# select * from pg_get_backend_memory_contexts() where parent = 'CacheMemoryContext' and name = 'CachedPlan';
&nbsp; &nbsp; name &nbsp; &nbsp;| &nbsp; &nbsp;ident &nbsp; &nbsp; | &nbsp; &nbsp; &nbsp; parent &nbsp; &nbsp; &nbsp; | level | total_bytes | total_nblocks | free_bytes | free_chunks | used_bytes 
------------+--------------+--------------------+-------+-------------+---------------+------------+-------------+------------
&nbsp;CachedPlan | va := va &nbsp; &nbsp; | CacheMemoryContext | &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;2048 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;576 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; 1472
&nbsp;CachedPlan | va &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | CacheMemoryContext | &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;2048 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;584 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; 1464
&nbsp;CachedPlan | va := va + i | CacheMemoryContext | &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;2048 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;384 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; 1664
&nbsp;CachedPlan | 10 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | CacheMemoryContext | &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;2048 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;544 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; 1504
&nbsp;CachedPlan | 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| CacheMemoryContext | &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;2048 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;544 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; 1504
&nbsp;CachedPlan | 100 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| CacheMemoryContext | &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;2048 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;544 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; 1504
(6 rows)


postgres=# create or replace procedure test_pro() as $$
declare
&nbsp; &nbsp; &nbsp; &nbsp; va int default 100;
begin
&nbsp; &nbsp; &nbsp; &nbsp; for i in 1 .. 10 loop
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; va := va + i;
&nbsp; &nbsp; &nbsp; &nbsp; end loop;


&nbsp; &nbsp; &nbsp; &nbsp; raise notice '%', va;
&nbsp; &nbsp; &nbsp; &nbsp; va := va;
end $$ LANGUAGE plpgsql;
CREATE PROCEDURE
postgres=# call test_pro();
NOTICE: &nbsp;155
CALL
postgres=# select * from pg_get_backend_memory_contexts() where parent = 'CacheMemoryContext' and name = 'CachedPlan';
&nbsp; &nbsp; name &nbsp; &nbsp;| &nbsp; &nbsp;ident &nbsp; &nbsp; | &nbsp; &nbsp; &nbsp; parent &nbsp; &nbsp; &nbsp; | level | total_bytes | total_nblocks | free_bytes | free_chunks | used_bytes 
------------+--------------+--------------------+-------+-------------+---------------+------------+-------------+------------
&nbsp;CachedPlan | va := va &nbsp; &nbsp; | CacheMemoryContext | &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;2048 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;576 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; 1472
&nbsp;CachedPlan | va &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | CacheMemoryContext | &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;2048 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;584 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; 1464
&nbsp;CachedPlan | va := va + i | CacheMemoryContext | &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;2048 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;384 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; 1664
&nbsp;CachedPlan | 10 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | CacheMemoryContext | &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;2048 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;544 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; 1504
&nbsp;CachedPlan | 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| CacheMemoryContext | &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;2048 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;544 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; 1504
&nbsp;CachedPlan | 100 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| CacheMemoryContext | &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;2048 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;544 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; 1504
&nbsp;CachedPlan | va := va &nbsp; &nbsp; | CacheMemoryContext | &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;2048 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;576 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; 1472
&nbsp;CachedPlan | va &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | CacheMemoryContext | &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;2048 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;584 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; 1464
&nbsp;CachedPlan | va := va + i | CacheMemoryContext | &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;2048 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;384 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; 1664
&nbsp;CachedPlan | 10 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | CacheMemoryContext | &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;2048 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;544 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; 1504
&nbsp;CachedPlan | 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| CacheMemoryContext | &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;2048 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;544 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; 1504
&nbsp;CachedPlan | 100 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| CacheMemoryContext | &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;2048 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 | &nbsp; &nbsp; &nbsp; &nbsp;544 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; 1504
(12 rows)