Hello Tom Lane, Tao Ma
I saw the post with title "question about the _SPI_save_plan() and
plan cache" discussing about memory free of dropped function.
I am using dynamically created function to imitate dynamic compound
statement in DB2.
I use function_factory(t1 text, t2 text, t3 text), pasted below, to
create dynamic function pg_temp.dyn_eval(), t1 is function name, t2 is
DECLARE part, t3 is BEGIN-END part. The t1 is fixed to
pg_temp.dyn_eval in client side.
Every time pg_temp.dyn_eval() is created, it is called only once.
On client side, a resident ODBC application connects to PostgreSQL,
working until system power off.
The application repeatedly "select function_factory(t1, t2, t3);" to
create pg_temp.dyn_eval(), "select pg_temp.dyn_eval();", and "drop
function pg_temp.dyn_eval();".
I observed the memory used by the server side PostgreSQL process
"postgres: postgres" increasing constantly, altough I confirmed that
pg_temp.dyn_eval() was dropped for sure. I think the increasing memory
is maybe caused by "SPI plan memory context", as you discussed.
Could you help to give some advice to free the memory as soon as
pg_temp.dyn_eval() is dropped.
I am using PostgreSQL 8.2.4.
Thank you.
ning
-----
CREATE FUNCTION function_factory(t1 TEXT, t2 TEXT, t3 TEXT)
RETURNS VOID AS $$
DECLARE func_body TEXT; func_cmd TEXT;
BEGIN func_body := 'DECLARE '; func_body := func_body || t2;
func_body := func_body || 'BEGIN '; func_body := func_body || t3; func_body := func_body || ' END;';
func_cmd := 'CREATE OR REPLACE FUNCTION ' || t1 || '() RETURNS VOID AS ' || quote_literal(func_body)
||' LANGUAGE plpgsql VOLATILE;' ;
EXECUTE func_cmd;
END;
$$ LANGUAGE plpgsql VOLATILE;
