Re: wrong search_path being used

Andres Freund wrote:
> 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?

The particular complaint here is about:

 perform pg_catalog.set_config('search_path', text_parameter, true);
 select ... from unqualified_tablename ...;

The table used is based on the parameter passed on the first execution;
the local search_path set on subsequent executions is ignored.  Perhaps
we should just not save a plan for any function which sets a new
search_path while it is executing?

-Kevin