On 2013-09-13 18:32, Robert Haas wrote:
On Thu, Sep 12, 2013 at 5:29 AM, Yeb Havinga <yebhavi...@gmail.com> wrote:
Is the following known behaviour, or should I put some time in writing a
self contained test case?
We have a function that takes a value and returns a ROW type. With the
function implemented in language SQL, when executing this function in a
large transaction, memory usage of the backend process increases.
MemoryContextStats showed a lot of SQL function data. Debugging
init_sql_fcache() showed that it was for the same function oid each time,
and the oid was the function from value to ROW type.
When the function is implemented in PL/pgSQL, the memory usage was much
less.
I'm sorry I cannot be more specific at the moment, such as what is 'much
less' memory with a PL/pgSQl function, and are there as many SQL function
data's as calls to the SQL function, because I would have to write a test
case for this. I was just wondering, if this is known behavior of SQL
functions vs PL/pgSQL functions, or could it be a bug?
It sounds like a bug to me, although I can't claim to know everything
there is to know about this topic.
I spent some time writing a test case, but failed to make a test case
that showed the memory difference I described upthread, in contrast, in
the test below, the SQL function actually shows a smaller memory
footprint than the plpgsql counterpart. This test case only demonstrates
that in a long running transaction, calling sql or plpgsql functions
causes increasing memory usage that is not released until after commit.
callit.sql:
----------
DO
$$
DECLARE b text;
i int;
BEGIN
-- SELECT 'a' into b; -- memory constant
i := fp('a'); -- memory increases
-- i := fs('a'); -- memory increases but slow
END;
$$ LANGUAGE plpgsql;
-------------
sqlvsplpgsql.sql:
-------------
CREATE OR REPLACE FUNCTION fp (a text)
RETURNS int
AS $$
DECLARE result int;
BEGIN
SELECT 10 INTO result;
RETURN result;
END;
$$
LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION fs (a text)
RETURNS int
AS $$
SELECT 10;
$$
LANGUAGE sql;
\i callit.sql
-------------
rm /tmp/ff /tmp/ff2 ; cp callit.sql /tmp/ff ; cat /tmp/ff /tmp/ff >>
/tmp/ff2; cat /tmp/ff2 /tmp/ff2 >> /tmp/ff; cat /tmp/ff /tmp/ff >>
/tmp/ff2; cat /tmp/ff2 /tmp/ff2 >> /tmp/ff;cat /tmp/ff /tmp/ff >>
/tmp/ff2; cat /tmp/ff2 /tmp/ff2 >> /tmp/ff;cat /tmp/ff /tmp/ff >>
/tmp/ff2; cat /tmp/ff2 /tmp/ff2 >> /tmp/ff;cat /tmp/ff /tmp/ff >>
/tmp/ff2; cat /tmp/ff2 /tmp/ff2 >> /tmp/ff;cat /tmp/ff /tmp/ff >>
/tmp/ff2; cat /tmp/ff2 /tmp/ff2 >> /tmp/ff;cat /tmp/ff /tmp/ff >>
/tmp/ff2; cat /tmp/ff2 /tmp/ff2 >> /tmp/ff
psql -1 postgres -f /tmp/ff
Then watch htop in another terminal.
--
Yeb Havinga
http://www.mgrid.net/
Mastering Medical Data
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
