I spent a bit of time looking into the memory leak reported here:
http://www.postgresql.org/message-id/52376C35.5040107@gmail.com
I think this test case doesn't have much to do with the complainant's
original problem, but anyway it is exposing a genuine leakage issue.
The difficulty is that when plpgsql wants to execute a "simple
expression", it does an ExecInitExpr to build the execution state
tree for the expression, and then it keeps that around for the
duration of the transaction. This was an intentional memory for
speed tradeoff that we made years ago (replacing logic that had
to do the ExecInitExpr over again during each function call).
I think it works well enough for plain old functions, although
somebody whose transactions executed thousands of distinct plpgsql
functions might beg to differ. However, in the example shown in
the above message, we're executing tens of thousands of plpgsql
DO blocks in a single transaction, and there's no possibility of
sharing execution state trees across executions for those.
We recover most of the memory associated with each DO block ...
but not the ExecInitExpr trees.
I believe this could be fixed in a reasonably localized fashion
by making each DO block create its very own simple_eval_estate
instead of sharing one with the rest of the transaction, and
then making sure to throw that away on exit from the DO block.
This would add some overhead per DO block, but not a huge amount
(probably negligible in comparison to the block's parsing costs,
anyway). A bigger objection is that the behavior around
simple_eval_estate and simple_econtext_stack would become a lot
more complicated and hard to understand. Possibly it would help
to use PLpgSQL_execstate fields for them.
Or we could say "what the heck are you doing executing tens of
thousands of DO blocks? Make it into a real live function;
you'll save a lot of cycles on parsing costs." I'm not sure that
this is a usage pattern we ought to be optimizing for.
Thoughts?
regards, tom lane
