Hi
> The problem is in plpgsql implementation of CALL statement
>>
>> In non atomic case - case of using procedures from DO block, the
>> expression plan is not cached, and plan is generating any time. This is
>> reason why it is slow.
>>
>> Unfortunately, generated plans are not released until SPI_finish.
>> Attached patch fixed this issue.
>>
>
> But now, recursive calling doesn't work :-(. So this patch is not enough
>
Attached patch is working - all tests passed
It doesn't solve performance, and doesn't solve all memory problems, but
significantly reduce memory requirements from 5007 bytes to 439 bytes per
one CALL
Regards
Pavel
>
>
>> Regards
>>
>> Pavel
>>
>>
>>> Regards
>>>
>>> Pavel
>>>
>>>
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index aeb6c8fefc..755387aab2 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -2143,6 +2143,7 @@ exec_stmt_call(PLpgSQL_execstate *estate, PLpgSQL_stmt_call *stmt)
LocalTransactionId after_lxid;
volatile bool pushed_active_snap = false;
volatile int rc;
+ bool plan_owner = false;
/* PG_TRY to ensure we clear the plan link, if needed, on failure */
PG_TRY();
@@ -2162,6 +2163,16 @@ exec_stmt_call(PLpgSQL_execstate *estate, PLpgSQL_stmt_call *stmt)
*/
exec_prepare_plan(estate, expr, 0, estate->atomic);
+ /*
+ * Not saved plan should be explicitly released. Without it, any
+ * not recursive usage of CALL statemenst leak plan in SPI memory.
+ * The created plan can be reused when procedure is called recursively,
+ * and releasing plan can be done only in recursion root call, when
+ * expression has not assigned plan. Where a plan was created, then
+ * there plan can be released.
+ */
+ plan_owner = true;
+
/*
* The procedure call could end transactions, which would upset
* the snapshot management in SPI_execute*, so don't let it do it.
@@ -2315,7 +2326,11 @@ exec_stmt_call(PLpgSQL_execstate *estate, PLpgSQL_stmt_call *stmt)
PG_END_TRY();
if (expr->plan && !expr->plan->saved)
+ {
+ if (plan_owner)
+ SPI_freeplan(expr->plan);
expr->plan = NULL;
+ }
if (rc < 0)
elog(ERROR, "SPI_execute_plan_with_paramlist failed executing query \"%s\": %s",
