Re: When deleting the plpgsql function, release the CachedPlan of the function - Mailing list pgsql-hackers

From Man Zeng
Subject Re: When deleting the plpgsql function, release the CachedPlan of the function
Date
Msg-id 175559184125.280981.15783482937984590429.pgcf@coridan.postgresql.org
Whole thread Raw
In response to Re: When deleting the plpgsql function, release the CachedPlan of the function  (Vladlen Popolitov <v.popolitov@postgrespro.ru>)
List pgsql-hackers
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';

pgsql-hackers by date:

Previous
From: Laurenz Albe
Date:
Subject: Re: Retail DDL
Next
From: Man Zeng
Date:
Subject: Re: When deleting the plpgsql function, release the CachedPlan of the function