Re: Possible memory leak with SQL function?

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