Обсуждение: When deleting the plpgsql function, release the CachedPlan of the function

On Mon, 18 Aug 2025 at 08:51, zengman <zengman@halodbtech.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

Matthias van de Meent <boekewurm+postgres@gmail.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

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

Thanks,
Zeng Man

The new status of this patch is: Waiting on Author

https://www.postgresql.org/message-id/flat/tencent_0BA97862026BC74E75238899@qq.com

The new status of this patch is: Needs review

Man Zeng писал(а) 2025-08-19 10:50:
> https://www.postgresql.org/message-id/flat/tencent_0BA97862026BC74E75238899@qq.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.

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';

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

regards, 
Zeng Man

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.

"=?utf-8?B?56ug5pmo5pum?=" <zhangchenxi@halodbtech.com> writes:
> I think you misunderstand Man's meaning. In Man's example, the func cache
> neither dropped (will cause memory leak) nor reused. So the question here:
> 1. Drop the cache when func dropped
> 2. Keep the cache and reused when func recreate
> I prefer the 2ed solution.

There is no provision for re-using a plancache entry by discovering
that the query you want to cache matches some existing entry.
So your option 2 is not going to happen, at least not without a
large amount of new code.  Furthermore, even if we wrote that code,
would it help much?  There's not a lot of reason to think that
a recreated version of the plpgsql function would contain exactly
the same queries as before.  There might be more reason to hope
that small plans (like for individual expressions) could be shared
among multiple plpgsql functions, but I'm still skeptical that
it'd be worth doing.

I think that Man's proposal to drop the plpgsql function-cache entry
and the plancache entries that it links to is a reasonable idea,
although of course it will only help in scenarios that may not be
common.  (I doubt that typical applications have a lot of run-time
churn in pg_proc.)

BTW, could people try harder to maintain the thread links when
replying?  This conversation has already degenerated into several
not-cross-linked threads in the archives.  It looks like the
problem is that some of you are using "X-mailer: QQMail 2.x",
which apparently doesn't feel a need to generate References:
or In-reply-to: headers.  That's super unfriendly behavior for
mailing lists.

            regards, tom lane

章晨曦 писал(а) 2025-08-19 12:31:

> 
> I think you misunderstand Man's meaning. In Man's example, the func
> cache
> neither dropped (will cause memory leak) nor reused. So the question
> here:
> 
> 1. Drop the cache when func dropped
> 2. Keep the cache and reused when func recreate
> I prefer the 2ed solution.
> 
> Regards,
> 
> Jet C.X. ZHANG
Hi

  It is why I am asking - the patch does not have a detail
description in text, it is not clear what and why is changed.

  What do you mean, when speak about function cache?
What object or function allocate it? I wrote
in my previous email, that function does not have any
cache, it deallocates after the execution everything allocated
and does not have cache. Cached plans are not function cache,
it is the cache of plans for future use by any other functions
or simple query with the same generic or custom plan. They all
are deallocated at least when user decides to close the connection.

Could you clarify, what function cache do you mean?

-- 
Best regards,

Vladlen Popolitov.

Vladlen Popolitov <v.popolitov@postgrespro.ru> writes:
>   What do you mean, when speak about function cache?

What I'm thinking about is the function parse-tree, which not
incidentally contains a bunch of links to cached plans (via SPI).
Looking at plpgsql_free_function_memory might clarify things
for you.

            regards, tom lane

It seems not an easy task. And here is my new work of such task.
The main idea is register a sys cache callback in cached_function_compile
when not registered. So it will effect for all SPL.
And also introduce a new hash table to track the function for cache inval
callback. The procedure in callback to lookup for a function will be:
hashvalue
     |
     v
[lookup func_key_hashtable]
     |
     v
  func_key
     |
    v
[lookup func_hashtable]
    |
    v
 function

But still remain lots of work to consider. As we don't know what's the real
operation of this invalidation, that's may cause unnecessary deletion. e.g.

postgres=# create or replace function strtest() returns text as $$
postgres$# begin
postgres$#   raise notice 'foo\\bar\041baz';
postgres$#   return 'foo\\bar\041baz';
postgres$# end
postgres$# $$ language plpgsql;
WARNING:  nonstandard use of \\ in a string literal
LINE 3:   raise notice 'foo\\bar\041baz';
                       ^
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING:  nonstandard use of \\ in a string literal
LINE 4:   return 'foo\\bar\041baz';
                 ^
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING:  nonstandard use of \\ in a string literal
LINE 4:   return 'foo\\bar\041baz';
                 ^
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
CREATE FUNCTION
postgres=# select strtest();
WARNING:  nonstandard use of \\ in a string literal           <--- redundant warning
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING:  nonstandard use of \\ in a string literal
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
NOTICE:  foo\bar!baz
WARNING:  nonstandard use of \\ in a string literal
LINE 1: 'foo\\bar\041baz'
        ^
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
QUERY:  'foo\\bar\041baz'
   strtest   
-------------
 foo\bar!baz
(1 row)

the function works no error, but cause deplicate warning message. i'm still working on this...

Attempt to clear the compilation warning, overwrite the plpgsql.out file, and update the patch.