Home > mailing lists

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

From	zengman
Subject	When deleting the plpgsql function, release the CachedPlan of the function
Date	August 18 09:50:35
Msg-id	tencent_14CE941B7CD8B7172CF049A6@qq.com Whole thread Raw
List	pgsql-hackers

Tree view

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

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

result：

postgres@zxm-VMware-Virtual-Platform:/data/16$ psql

psql (16.10)

Type "help" for help.

postgres=# 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;

CREATE PROCEDURE

postgres=# call test_pro();

NOTICE: 155

CALL

postgres=# select * from pg_get_backend_memory_contexts() where parent = 'CacheMemoryContext' and name = 'CachedPlan';

------------+--------------+--------------------+-------+-------------+---------------+------------+-------------+------------

CachedPlan | va := va | CacheMemoryContext | 2 | 2048 | 2 | 576 | 0 | 1472

CachedPlan | va | CacheMemoryContext | 2 | 2048 | 2 | 584 | 0 | 1464

CachedPlan | va := va + i | CacheMemoryContext | 2 | 2048 | 2 | 384 | 0 | 1664

CachedPlan | 10 | CacheMemoryContext | 2 | 2048 | 2 | 544 | 0 | 1504

CachedPlan | 1 | CacheMemoryContext | 2 | 2048 | 2 | 544 | 0 | 1504

CachedPlan | 100 | CacheMemoryContext | 2 | 2048 | 2 | 544 | 0 | 1504

(6 rows)

postgres=# drop procedure test_pro;

DROP PROCEDURE