Re: DISCARD ALL ; stored procedures

* Merlin Moncure (mmoncure(at)gmail(dot)com) wrote:
> 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.

That would certainly be fine for me. To be honest, I feel like I've
even suggested that in the past, somewhere.

> 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?

I don't see how this can be an unnecessary performance hit. You might
argue that I should redesign things to not work this way, but that's a
whole different discussion. At the moment the options are:

- switch to using execute
- force a full database reconnect

To get the 'correct' behavior.

If it's "performance" vs. "correctness", you can guess what I'm going to
vote for, however, in this case, I can't see how either of the other
options would perform better than a discard-like approach. If people
are already using 'discard all;' then they're already throwing away
their plans for prepared queries, it strikes me as unlikely that they'd
have an issue with also getting rid of stored procedure plans. If they
do, they could certainly use the individual 'discard' statements
instead (presuming we implement this with a new discard argument).

Thanks

Stephen