BUG #4677: memory growth

The following bug has been logged online:

Bug reference: 4677
Logged by: aiwaniuk
Email address: aiwaniuk(at)instytut(dot)com(dot)pl
PostgreSQL version: >8.0
Operating system: linux
Description: memory growth
Details:

i postgres version 8.2, 8.3 and probobly 8.1 there is problem with running
VOLATILE plpgsql function with begin - exception checking that performs
other VOLATILE plpgsql function. if either, first or second performing
removed, problem doesn't shows. here's an example

CREATE FUNCTION info.f()
RETURNS void AS
$BODY$
DECLARE
tmp text;
BEGIN
-- do anything
tmp = md5(random()::text) || md5(random()::text);
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

CREATE or replace FUNCTION info.memory_growth(i_max integer)
RETURNS integer AS
$BODY$
DECLARE
i integer;
BEGIN
i = 0;
WHILE i < i_max LOOP

BEGIN
PERFORM info.f();
EXCEPTION
WHEN OTHERS THEN
--
END;

PERFORM info.f();

i = i + 1;
END LOOP;

RETURN i;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;

after running
select info.memory_growth(100000);

please take a look how's memory of client process grows. is there some
logical problem how functions are create ?

"aiwaniuk" <aiwaniuk(at)instytut(dot)com(dot)pl> writes:
> i postgres version 8.2, 8.3 and probobly 8.1 there is problem with running
> VOLATILE plpgsql function with begin - exception checking that performs
> other VOLATILE plpgsql function. if either, first or second performing
> removed, problem doesn't shows. here's an example

The reason there's an issue here is that you're invoking the same
function f() both inside and outside the subtransactions caused by
the begin/exception block. The expressions inside f() get re-prepared
each time the subtransaction ID changes. The memory this eats is
reclaimed at subtransaction end. So the memory used by the call inside
the begin/exception block is cleaned up immediately, but the memory
used by the other call accumulates in the outer subtransaction's
workspace.

We'll think about how to fix this, but you shouldn't expect that a
fix will appear quickly. A possible workaround is to put another
begin/exception block around the other call of the function.

regards, tom lane

On Thu, Feb 26, 2009 at 03:50:16PM -0500, Tom Lane wrote:
> "aiwaniuk" <aiwaniuk(at)instytut(dot)com(dot)pl> writes:
> > i postgres version 8.2, 8.3 and probobly 8.1 there is problem with running
> The reason there's an issue here is that you're invoking the same
> function f() both inside and outside the subtransactions caused by
> the begin/exception block. The expressions inside f() get re-prepared
> each time the subtransaction ID changes. The memory this eats is
> reclaimed at subtransaction end. So the memory used by the call inside
> the begin/exception block is cleaned up immediately, but the memory
> used by the other call accumulates in the outer subtransaction's
> workspace.

thanks for replay.

i have to say something more. i see that if there is one perform
of f() (no matter if it is begin - exception block, or not) postgres
process consumes 450MB of memory. but if there are two executions of
f(), and i make
select info.memory_growth(1000000);
postgres process consumes 5.2GB of memory. this is way too much...
or not ? isn't it some bug in memory reciment ?

executing f() only once, consumes as i mention, 450MB of
memory. changing number of loops in execution do not lead to memory
growth. if we performing two executions of f() (one in begin -
exception block) used memory is growing, on and on. i have 13GB of
memory, and multiplying number loops by 10 (from 1000000 to 10000000)
causes server crash - not enough memory.

there is one interesting thing, if i create a copy of f() function (lets say
f1), and function f() is performing in begin-exception block, and f1()
is performing in main block, mamory do not grows!! postgres constantly
uses 450MB interesting, isn't it ?

looking forward for comment

On Thu, Feb 26, 2009 at 03:50:16PM -0500, Tom Lane wrote:
> The reason there's an issue here is that you're invoking the same

i'v forgotten to mention that this behavior we saw when we
had migrated one database from version 8.0 to 8.3. further test showed, that
in v8.2 this problem is also seen. versions 8.0 and 8.1 (at least 8.1.3) are
insensitive to this issue.