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; -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers