case.txt

application/octet-stream

Download

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';
    name    |    ident     |       parent       | level | total_bytes | total_nblocks | free_bytes | free_chunks | used_bytes 
------------+--------------+--------------------+-------+-------------+---------------+------------+-------------+------------
 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
postgres=# select * from pg_get_backend_memory_contexts() where parent = 'CacheMemoryContext' and name = 'CachedPlan';
    name    |    ident     |       parent       | level | total_bytes | total_nblocks | free_bytes | free_chunks | used_bytes 
------------+--------------+--------------------+-------+-------------+---------------+------------+-------------+------------
 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=# 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';
    name    |    ident     |       parent       | level | total_bytes | total_nblocks | free_bytes | free_chunks | used_bytes 
------------+--------------+--------------------+-------+-------------+---------------+------------+-------------+------------
 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
 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
(12 rows)

postgres=#