Thread: BUG #4677: memory growth
The following bug has been logged online: Bug reference: 4677 Logged by: aiwaniuk Email address: aiwaniuk@instytut.com.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@instytut.com.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@instytut.com.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.