Re: BUG #5516: Memory grows up problem

Greg Stark <gsstark(at)mit(dot)edu> writes:
> On Mon, Jun 21, 2010 at 12:41 PM, Yuhui <xingyuhui(at)gmail(dot)com> wrote:
>> CREATE TABLE mem_leak(A INT, B char(50) default 'abcdefg');
>> INSERT INTO mem_leak(a) VALUES(generate_series(1,200000000));
>>
>> The memory which the execution process occupation will grows larger and
>> larger until all the memory is gone or the process is over or the process is
>> killed by the os.

> I'm afraid that just how set-returning-functions like
> generate_series() worked prior to 8.4. They build a result set in
> memory for all their result records.

No, there hasn't been any change in the SRF protocols. The Result node
generating the VALUES() output would still leak memory, per this code:

/*
* Check to see if we're still projecting out tuples from a previous scan
* tuple (because there is a function-returning-set in the projection
* expressions). If so, try to project another one.
*/
if (node->ps.ps_TupFromTlist)
{
resultSlot = ExecProject(node->ps.ps_ProjInfo, &isDone);
if (isDone == ExprMultipleResult)
return resultSlot;
/* Done with that source tuple... */
node->ps.ps_TupFromTlist = false;
}

/*
* Reset per-tuple memory context to free any expression evaluation
* storage allocated in the previous tuple cycle. Note this can't happen
* until we're done projecting out tuples from a scan tuple.
*/
ResetExprContext(econtext);

It's actually more surprising that current releases don't leak memory on
this example than that older ones do. I traced through it and realized
that the reason for the change in behavior is that current versions
constant-fold the 'abcdefg'::char(50) expression, whereas before 8.4
it was left to be done at runtime. That's per this comment in
preprocess_expression():

/*
* Simplify constant expressions.
*
* Note: an essential effect of this is to convert named-argument function
* calls to positional notation and insert the current actual values of
* any default arguments for functions. To ensure that happens, we *must*
* process all expressions here. Previous PG versions sometimes skipped
* const-simplification if it didn't seem worth the trouble, but we can't
* do that anymore.
*/
expr = eval_const_expressions(root, expr);

We're not likely to consider back-patching that change, since it's only
accidental that it fixes this case, and there are lots of related cases
it wouldn't fix.

The best workaround is to not rely on an SRF in the values list.
Instead do something like

INSERT INTO mem_leak(a) SELECT a FROM generate_series(1,2000000) a;

As long as the SRF is in FROM, you shouldn't have a problem.

regards, tom lane