Re: question about the _SPI_save_plan() and plan cache

Hi,

It seems postgres cache the plan under CacheMemoryContext during the
plpgsql executing.
If there is a function with lots of variables and every one of them got a
default value,
postgres will allocate lots of memory for caching the default value plan(we
have to run
the function at least once). Once we DROP the function, the memory consumed
by the plan
will be leak. The same thing for the compiled function
structure(PLpgSQL_function).

Is this an oldest known question?

Thanks,
Tao Ma

"Tao Ma" <feng_eden(at)163(dot)com> writes:
> Once we DROP the function, the memory consumed
> by the plan will be leak.

I'm pretty unconcerned about DROP FUNCTION. The case that seems worth
worrying about is CREATE OR REPLACE FUNCTION, and in that case we'll
reclaim the storage on the next call of the function.

regards, tom lane

I knew that the delete_function() will reclaim the memory context
allocated for the function. But I did not find any code for removing
the plan(SPI plan memory context), saved by calling _SPI_save_plan.

Is the plan memory context freed when someone issued CREATE OR
REPLACE FUNCTION?

Thanks.

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrotes:25310(dot)1248791701(at)sss(dot)pgh(dot)pa(dot)us(dot)(dot)(dot)
> "Tao Ma" <feng_eden(at)163(dot)com> writes:
>> Once we DROP the function, the memory consumed
>> by the plan will be leak.
>
> I'm pretty unconcerned about DROP FUNCTION. The case that seems worth
> worrying about is CREATE OR REPLACE FUNCTION, and in that case we'll
> reclaim the storage on the next call of the function.
>
> regards, tom lane
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

"Tao Ma" <feng_eden(at)163(dot)com> writes:
> I knew that the delete_function() will reclaim the memory context
> allocated for the function. But I did not find any code for removing
> the plan(SPI plan memory context), saved by calling _SPI_save_plan.

Hmmm ... good point, those probably won't get cleaned up. In an
environment where functions are getting changed constantly, that
might be worth doing.

regards, tom lane

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> "Tao Ma" <feng_eden(at)163(dot)com> writes:
>> I knew that the delete_function() will reclaim the memory context
>> allocated for the function. But I did not find any code for removing
>> the plan(SPI plan memory context), saved by calling _SPI_save_plan.
>
> Hmmm ... good point, those probably won't get cleaned up. In an
> environment where functions are getting changed constantly, that
> might be worth doing.
>
> regards, tom lane
>

Hi, I just paste a re-produce sql script. Is it possible to cache the
SPI plan under the function cache context?

Thanks.

begin 666 spi_plan_leak_eg.sql
M0U)%051%($Q!3D=504=%('!L<&=S<6P[#0H-"BTM(&=E;F5R871E(&$@:'5G
M92!F=6YC=&EO;@T*0U)%051%($]2(%)%4$Q!0T4(at)1E5(dot)0U1)3TX(at)9G5N8U]G
M96YE<F%T;W(H*2!215154DY3(%1%6%0(at)05,@)"0-"D1%0TQ!4D4-"B @<W1M
M="!415A4.PT*("!I($E.5#L-"D)%1TE.#0H@('-T;70(at)(dot)CT@)T-214%412!/
M4B!215!,04-%($953D-424].(&8H*2!215154DY3(%1%6%0(at)05,@)$$D1$5#
M3$%212 G.PT*("!&3U(@:2!)3B Q+BXQ,# P($Q/3U -"B @("!S=&UT(#H]
M('-T;70(at)?'P@)R!V87)?=&5X="<@?'P@:2!\?" G(%1%6%0(at)1$5&055,5"!#
M55)214Y47U1)344[)SL-"B @("!S=&UT(#H]('-T;70(at)?'P@)R!V87)?:6YT
M)R!\?"!I('Q\("<@24Y4($1%1D%53%0@,3 P.R<[#0H@(" @<W1M=" Z/2!S
M=&UT('Q\("<@8W5R7R<@?'P@:2!\?" G($-54E-/4B H<#$(at)24Y4+"!P,B!4
M15A4*2!)4R!314Q%0U0@<#(-"B @(" @(" @(" @(" @(" @(" @(" @(" @
M(" @(" @(" @(" @1E)/32!D=6%L(%=(15)%(%)/5TY532 \(' Q.R<[#0H@
M($5.1"!,3T]0.PT*("!S=&UT(#H]('-T;70(at)?'P@)T)%1TE.(%)%5%523B!V
M87)?=&5X=#$[($5.1#L@)$$D($Q!3D=504=%('!L<&=S<6PG.PT*("!%6$5#
M551%('-T;70[#0H@(%)%5%523B G1E5.0U1)3TX(at)1T5(dot)15)!5$]2)SL-"D5.
M1#L-"B0D($Q!3D=504=%('!L<W%L(dot)PT*#0I314Q%0U0(at)9G5N8U]G96YE<F%T
M;W(H*3L-"E-%3$5#5"!F*"D[("TM(&-O;G-U;65S(&%B;W5T(#(P34(@;65M
M;W)Y+(at)T*#0I$4D]0($953D-424].(&8H*3L(at)+2T@4U!)('!L86X@;&5A:PT*
M1%)/4"!&54Y#5$E/3B!F=6YC7V=E;F5R871O<B(at)I(dot)PT*#0H-"E-%3$5#5"!F
M=6YC7V=E;F5R871O<B(at)I(dot)PT*4T5,14-4(&8H*3L(at)+2T@8V]N<W5M97,@86YO
1=&AE<B R,$U"(&UE;6]R>2X`
`
end