Re: Possible memory leak with SQL function?

Yeb Havinga <yebhavinga(at)gmail(dot)com> writes:
> 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:
>>> 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.1384273112@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