From: | ning <mailxiening(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Tao Ma <feng_eden(at)163(dot)com> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | memory free of constantly changed function |
Date: | 2009-08-17 09:26:09 |
Message-ID: | 27f31620908170226m38fa1524w546fc9ecbd4be932@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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;
From | Date | Subject | |
---|---|---|---|
Next Message | Boszormenyi Zoltan | 2009-08-17 09:31:00 | Re: Split-up ECPG patches |
Previous Message | Pavel Stehule | 2009-08-17 09:00:01 | Re: Buffer usage in EXPLAIN and pg_stat_statements |