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

From: zengman <zengman(at)halodbtech(dot)com>
To: pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: When deleting the plpgsql function, release the CachedPlan of the function
Date: 2025-08-18 06:50:35
Message-ID: tencent_14CE941B7CD8B7172CF049A6@qq.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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(at)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)

Attachment Content-Type Size
00001_free_function_memory.patch application/octet-stream 6.7 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2025-08-18 06:52:43 Re: Compilation issues for HASH_STATISTICS and HASH_DEBUG options
Previous Message Michael Paquier 2025-08-18 06:49:41 Re: [Proposal] Expose internal MultiXact member count function for efficient monitoring