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

Lists: pgsql-hackers
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
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
From: Matthias van de Meent <boekewurm+postgres(at)gmail(dot)com>
To: zengman <zengman(at)halodbtech(dot)com>
Cc: pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: When deleting the plpgsql function, release the CachedPlan of the function
Date: 2025-08-18 16:13:44
Message-ID: CAEze2WgOLDRZr5Bu45iZCmdRPuC0TwFz5+yJRuD6zkz_ak8mRg@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Lists: pgsql-hackers

On Mon, 18 Aug 2025 at 08:51, zengman <zengman(at)halodbtech(dot)com> wrote:
>
> 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.

I'm trying to figure out how this patch is supposed to handle
concurrent sessions dropping a procedure that has cached plans. AFAIK,
we don't execute RemoveFunctionById in other sessions, so this would
still leave the plan caches active for other backends, right?

Kind regards,

Matthias van de Meent
Databricks

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Matthias van de Meent <boekewurm+postgres(at)gmail(dot)com>
Cc: zengman <zengman(at)halodbtech(dot)com>, pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: When deleting the plpgsql function, release the CachedPlan of the function
Date: 2025-08-18 16:38:35
Message-ID: 420591.1755535115@sss.pgh.pa.us
Views: Whole Thread | Raw Message | Download mbox | Resend email
Lists: pgsql-hackers

Matthias van de Meent <boekewurm+postgres(at)gmail(dot)com> writes:
> I'm trying to figure out how this patch is supposed to handle
> concurrent sessions dropping a procedure that has cached plans.

It doesn't, which is (one reason) why it's just a crude hack.

A more appropriate solution would be to make plpgsql install
a shared-cache-invalidation callback that would watch for
invalidations on pg_proc and mark relevant function trees as
deletable. It couldn't necessarily delete them right away,
since they might be in use at the moment the inval event
arrives. (That is, an inval might just indicate an update
not a delete. But flushing the function tree would be OK
in either case.)

I wonder if we could make src/backend/utils/cache/funccache.c
handle this, so that SQL functions could also benefit without
duplicated logic.

regards, tom lane

From: Man Zeng <zengman(at)halodbtech(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Cc: Man Zeng <zengman(at)halodbtech(dot)com>
Subject: Re: When deleting the plpgsql function, release the CachedPlan of the function
Date: 2025-08-19 01:44:03
Message-ID: 175556784361.994.11217855119678985664.pgcf@coridan.postgresql.org
Views: Whole Thread | Raw Message | Download mbox | Resend email
Lists: pgsql-hackers

That's correct—this is a simple and blunt patch, and it fails to account for many factors. Initially, I wasn't even sure if this qualified as a distinct issue. Your solution is far more reasonable, and I will rethink the new implementation thoroughly based on your approach.

Thanks,
Zeng Man

The new status of this patch is: Waiting on Author

From: Man Zeng <zengman(at)halodbtech(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Cc: Man Zeng <zengman(at)halodbtech(dot)com>
Subject: Re: When deleting the plpgsql function, release the CachedPlan of the function
Date: 2025-08-19 07:50:20
Message-ID: 175558982024.280981.17359643715378284723.pgcf@coridan.postgresql.org
Views: Whole Thread | Raw Message | Download mbox | Resend email
Lists: pgsql-hackers

https://www.postgresql.org/message-id/flat/tencent_0BA97862026BC74E75238899(at)qq(dot)com

The new status of this patch is: Needs review

From: Vladlen Popolitov <v(dot)popolitov(at)postgrespro(dot)ru>
To: Man Zeng <zengman(at)halodbtech(dot)com>
Cc: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: When deleting the plpgsql function, release the CachedPlan of the function
Date: 2025-08-19 08:01:58
Message-ID: e6686f7105847c538613f4bf81dc61c3@postgrespro.ru
Views: Whole Thread | Raw Message | Download mbox | Resend email
Lists: pgsql-hackers

Man Zeng писал(а) 2025-08-19 10:50:
> https://www.postgresql.org/message-id/flat/tencent_0BA97862026BC74E75238899(at)qq(dot)com
>
> The new status of this patch is: Needs review

Hi!

I read from the beginning till this email and did not
find the description what exactly you are going to fix.
Could you provide more details?

A procedure creates plans and put them to cache for every
SQL query, that it executes (including limits in FOR operator
in your example, that considered as SQL queries). These plans remains
in the cache , when a procedure exits.

These cached plans can be used by this procedure again or can be used by
other procedures or by direct SQL query. It is not clear, why we should
delete them? It is the goal of the cache to use plans in other queries.

--
Best regards,

Vladlen Popolitov.

From: Man Zeng <zengman(at)halodbtech(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Cc: Man Zeng <zengman(at)halodbtech(dot)com>
Subject: Re: When deleting the plpgsql function, release the CachedPlan of the function
Date: 2025-08-19 08:24:01
Message-ID: 175559184125.280981.15783482937984590429.pgcf@coridan.postgresql.org
Views: Whole Thread | Raw Message | Download mbox | Resend email
Lists: 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';

From: Man Zeng <zengman(at)halodbtech(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Cc: Man Zeng <zengman(at)halodbtech(dot)com>
Subject: Re: When deleting the plpgsql function, release the CachedPlan of the function
Date: 2025-08-19 08:28:37
Message-ID: 175559211717.280981.9958752159928616181.pgcf@coridan.postgresql.org
Views: Whole Thread | Raw Message | Download mbox | Resend email
Lists: pgsql-hackers

So in my opinion, the cached plan will not be reused but will constantly occupy resources.

regards,
Zeng Man

From: Vladlen Popolitov <v(dot)popolitov(at)postgrespro(dot)ru>
To: Man Zeng <zengman(at)halodbtech(dot)com>
Cc: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: When deleting the plpgsql function, release the CachedPlan of the function
Date: 2025-08-19 08:39:50
Message-ID: 018782afd6c520264080381734403c5e@postgrespro.ru
Views: Whole Thread | Raw Message | Download mbox | Resend email
Lists: pgsql-hackers

Man Zeng писал(а) 2025-08-19 11:28:
> So in my opinion, the cached plan will not be reused but will
> constantly occupy resources.
>
> regards,
> Zeng Man
Hi!

In your example function will be compiled (the tree is created in the
memory)
and executed.
During execution this function creates a plan for very simple query 1
and stores it in the cache, than it creates a plan for query 10 and
store
in in the cache. There is no other queries, no more plans in the cache.
After execution function releases the tree (own code) from memory,
but cached queries are remains in the memory, it is why this cache is
created -
for usage by other queries.

I do not know, what other cache of the stored procedure do you mean. I
suppose,
a stored procedure creates cached plans only for queries inside of the
procedure,
not for itself.

--
Best regards,

Vladlen Popolitov.