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

Поиск

Список

Период

Сортировка

От	zengman
Тема	When deleting the plpgsql function, release the CachedPlan of the function
Дата	18 августа 09:50:35
Msg-id	tencent_14CE941B7CD8B7172CF049A6@qq.com обсуждение исходный текст
Ответы	Re: When deleting the plpgsql function, release the CachedPlan of the function
Список	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

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