Re: [HACKERS] Possible memory leak with SQL function?
Yeb Havinga yebhavi...@gmail.com writes:
On 2013-09-13 18:32, Robert Haas wrote:
On Thu, Sep 12, 2013 at 5:29 AM, Yeb Havinga yebhavi...@gmail.com wrote:
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.
When the function is implemented in PL/pgSQL, the memory usage was much
less.
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.
I looked into this, and found that what the test is showing is that use
of a simple expression in a plpgsql DO block leaks some memory that's
not reclaimed till end of transaction; see analysis at
http://www.postgresql.org/message-id/7438.1384273...@sss.pgh.pa.us
You had
-- SELECT 'a' into b; -- memory constant
i := fp('a'); -- memory increases
-- i := fs('a'); -- memory increases but slow
The SELECT doesn't leak because it's not a simple expression. The
other two cases exhibit what's basically the same leak, though the
SQL-function case leaks less memory per iteration and probably takes
more cycles to do it, as a consequence of inlining the function's
constant result into the calling expression.
I'm not sure whether we're going to put much effort into fixing this
leak; this usage pattern seems outside what DO blocks are intended for.
(If you're going to execute the same code over and over again, it makes
a whole lot more sense to define it as a real function, to avoid parsing
overhead. Or just put it inside a loop in the DO text.)
But anyway, the bottom line is that this test case doesn't seem to
have much to do with your original problem with SQL functions.
Can you have another go at recreating that leak?
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Possible memory leak with SQL function?
Noah, this is the kind of memory leak I was referring to which would be
nice if valgrind could help with. I'm not sure exactly what that would look
like though, I've never tried writing support code for valgrind to deal
with custom allocators.
--
greg
On 16 Sep 2013 15:38, Yeb Havinga yebhavi...@gmail.com wrote:
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-hackershttp://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 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 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
Re: [HACKERS] Possible memory leak with SQL function?
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. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Possible memory leak with SQL function?
Hello list, 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? -- 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
