Re: Possible memory leak with SQL function?

<p dir="ltr">Noah, this is the kind of memory leak I was referring to which would be nice if valgrind could help with.
I'mnot sure exactly what that would look like though, I've never tried writing support code for valgrind to deal with
customallocators.<p dir="ltr">-- <br /> greg<div class="gmail_quote">On 16 Sep 2013 15:38, "Yeb Havinga" <<a
href="mailto:yebhavinga@gmail.com">yebhavinga@gmail.com</a>>wrote:<br type="attribution" /><blockquote
class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"> On 2013-09-13 18:32, Robert
Haaswrote:<br /><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
OnThu, Sep 12, 2013 at 5:29 AM, Yeb Havinga <<a href="mailto:yebhavinga@gmail.com"
target="_blank">yebhavinga@gmail.com</a>>wrote:<br /><blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px#ccc solid;padding-left:1ex"> Is the following known behaviour, or should I put some time in
writinga<br /> self contained test case?<br /><br /> We have a function that takes a value and returns a ROW type. With
the<br/> function implemented in language SQL, when executing this function in a<br /> large transaction, memory usage
ofthe backend process increases.<br /> MemoryContextStats showed a lot of SQL function data. Debugging<br />
init_sql_fcache()showed that it was for the same function oid each time,<br /> and the oid was the function from value
toROW type.<br /><br /> When the function is implemented in PL/pgSQL, the memory usage was much<br /> less.<br /><br />
I'msorry I cannot be more specific at the moment, such as what is 'much<br /> less' memory with a PL/pgSQl function,
andare there as many SQL function<br /> data's as calls to the SQL function, because I would have to write a test<br />
casefor this. I was just wondering, if this is known behavior of SQL<br /> functions vs PL/pgSQL functions, or could it
bea bug?<br /></blockquote> It sounds like a bug to me, although I can't claim to know everything<br /> there is to
knowabout this topic.<br /><br /></blockquote> I spent some time writing a test case, but failed to make a test case
thatshowed the memory difference I described upthread, in contrast, in the test below, the SQL function actually shows
asmaller 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.<br
/><br/> callit.sql:<br /> ----------<br /> DO<br /> $$<br /> DECLARE  b text;<br />          i int;<br /> BEGIN<br />
--  SELECT 'a' into b; -- memory constant<br />    i := fp('a'); -- memory increases<br /> --   i := fs('a'); -- memory
increasesbut slow<br /> END;<br /> $$ LANGUAGE plpgsql;<br /> -------------<br /><br /> sqlvsplpgsql.sql:<br />
-------------<br/> CREATE OR REPLACE FUNCTION fp (a text)<br />  RETURNS int<br />  AS $$<br /> DECLARE result int;<br
/>BEGIN<br />         SELECT 10 INTO result;<br />         RETURN result;<br /> END;<br /> $$<br /> LANGUAGE
plpgsql;<br/> CREATE OR REPLACE FUNCTION fs (a text)<br />  RETURNS int<br />  AS $$<br />  SELECT 10;<br /> $$<br />
LANGUAGEsql;<br /> \i callit.sql<br /> -------------<br /><br /><br /> 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<br /><br /> psql -1 postgres -f /tmp/ff<br
/><br/> Then watch htop in another terminal.<br /><br /><br /> -- <br /> Yeb Havinga<br /><a
href="http://www.mgrid.net/"target="_blank">http://www.mgrid.net/</a><br /> Mastering Medical Data<br /><br /><br /><br
/>-- <br /> Sent via pgsql-hackers mailing list (<a href="mailto:pgsql-hackers@postgresql.org"
target="_blank">pgsql-hackers@postgresql.org</a>)<br/> To make changes to your subscription:<br /><a
href="http://www.postgresql.org/mailpref/pgsql-hackers"
target="_blank">http://www.postgresql.org/<u></u>mailpref/pgsql-hackers</a><br/></blockquote></div>