Thread

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

zengman <zengman@halodbtech.com> — 2025-08-19T08:24:01Z

When a function or stored procedure is created, called, and then dropped, 
the resulting CachedPlan is never released and can only be freed by exiting the session. 
Meanwhile, if you create another function or stored procedure with the same name and parameters, and then call it, 
you'll be able to see two separate CachedPlans via pg_get_backend_memory_contexts.

You may refer to the following test steps.

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

        raise notice '%', va;
        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
        va int default 100;
begin
        for i in 1 .. 10 loop
                va := va + i;
        end loop;

        raise notice '%', va;
        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';