Re: pl/pgsql Plan Invalidation and search_path

On Jan 27, 2008 10:45 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > In doing some test on 8.3RC2, I was dismayed to discover that the
> > pl/pgsql plan invalidation logic added doesn't consider changing the
> > search_path to invalidate a plan.
>
> We never considered it so before, either. The plancache code goes out
> of its way to maintain the same path that was used initially, and
> I think that's what it should do: a cache module should avoid letting
> the semantics of what it's cached change without the caller's knowledge.
> If we were to change this, we'd probably have to think in terms of
> making the active search_path be part of the lookup key for cached plans.
>
> > Would it be possible to have this case handled?
>
> It's far too late to reconsider this point for 8.3. If you want to
> bring it up for 8.4, we could think about what the behavioral and
> performance implications would really be. In the meantime, the answer
> is the same as it's always been: if that's what you want, use EXECUTE.

For the record, IMO it would on balance be better to have the plan
invalidate when setting the search path. This is a special case but a
reasonable one, and the surprising behavior should be weighed against
a bigger, more unpleasant surprise when the plans don't change. Also,
I'd argue that with with this change hides the inner workings of the
plan caching, eliminating one of the last corner cases where you have
to deal with it outside of performance considerations.

merlin