Re: Possible memory leak with SQL function? - Mailing list pgsql-hackers

On 2013-09-13 18:32, Robert Haas wrote:

On Thu, Sep 12, 2013 at 5:29 AM, Yeb Havinga <yebhavinga@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