Re: BUG #5902: pl/pgsql plans are not invalidated on discard all

On Tue, Mar 1, 2011 at 5:11 AM, Ingmar Brouns <swingi@gmail.com> wrote:
> Hi Tom,
>
> thanks for your reply.
>
>>
>> > I was looking for a workaround to this problem, and figured that calli=
ng
>> > 'discard all', or 'discard plans' should do the trick.
>>
>> That's not a solution because the plancache module intentionally
>> preserves the original search_path value while replanning. =A0This
>> may not be what you wished for, but it's operating as intended,
>> and changing it would break other use-cases that work today.
>>
>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0regards, tom lane
>
> We are weighing our options to deal with this problem.
> I'm very interested in the use cases that=A0 will break when using the
> current search_path instead of the original search_path when replanning.
> Can you tell me what will break?

This has been much discussed in the archives.  What you probably want
is a proposed (but not vetted) enhancement to pl/pgsql so that the
plan cache is organized around search path, so that search_path 'a'
has a set of plans, search_path 'b' has another set of plans, etc.
That way, you aren't constantly recompiling your functions in a pooled
environment (performance would suck).

One workaround is to have the function be in the schemas you are
floating with search_path. I would consider wrapping your 'create
function' in a script or a plpgsql function so you can automate it's
creation across schemas.

Another workaround is to keep all function code that touches schema
private data in regular sql functions.  You can also use EXECUTE
inside pl/pgsql safely.

merlin