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(at)gmail(dot)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