Re: DISCARD ALL ; stored procedures

On Thu, Jan 6, 2011 at 4:30 PM, Stephen Frost <sfrost@snowman.net> wrote:
> * Merlin Moncure (mmoncure@gmail.com) wrote:
>> this is a problem. under what circumstances would you want to discard
>> them and why?  the main problem I see with cached plpgsql plans is
>> interactions with search_path -- but DISCARD might not be the best way
>> to attack that problem.  There might be other reasons though.
>
> interaction w/ search_path (or, rather, lack of respect for it..) is
> exactly the issue here for me.

this has been discussed a couple of times -- a plausible alternative
might be to adjust the plan caching mechanism to organize the plan
cache around search_path.  that way you get a separate plan per
search_path instance.

discard has zero backwards compatibility issues but has one big
problem -- if you are using combination of connection pooling, lots of
plpgsql and search_path manipulation, you take a big performance hit.
in other words, even if you can discard everything., do you really
want to?

merlin