Re: wrong search_path being used

On 2013-01-12 14:29:38 -0500, Tom Lane wrote:
> "Kevin Grittner" <kgrittn(at)mail(dot)com> writes:
> > To try to get your function code to work as you expect, the
> > language would essentially need to identify which statements could
> > be pre-planned, and which would needed to be treated as raw source
> > on each execution. That would be tricky to implement, and would
> > itself have some run-time cost. At this point we've put the burden
> > on the programmer to identify this at the time the code is written,
> > rather than adding run-time expense.
>
> I think that the alternative most likely to succeed is to consider any
> change in the active value of search_path as forcing replanning of
> cached plans. This wouldn't be that hard to implement but there's
> a definite risk of loss of performance due to unnecessary replanning
> (since the path change might or might not affect the particular query).
> It's also not unlikely that it could break applications that work today,
> because they depend -- perhaps without being aware of it -- on the
> existing first-path-wins behavior.
>
> Having said that, it seems likely that more people would prefer that
> behavior than the existing one. But it hasn't been clear enough to
> justify making such a subtly incompatible change.

Its a somewhat common practise to use SET in functions or as a
configuration parameter to functions. I think at least the latter should
still work without forcing to replan any query. Given that we advice
setting the search path for SECURITY DEFINER...

I guess it wouldn't really be feasible to keep the search path used to
plan a query in its cached form and check that it fits the one currently
used on every use of the cached plan?

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services