Re: wrong search_path being used

Em 12-01-2013 18:13, Tom Lane escreveu:
> Andres Freund<andres(at)2ndquadrant(dot)com> writes:
>> On 2013-01-12 14:29:38 -0500, Tom Lane wrote:
>>> 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.
>> 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?
> Actually that's exactly what I meant: every time we arrive at a query
> with a cached plan, check to see if the active search_path value is the
> same as what it was when we made the cached plan, and replan if not.
>
> There's already infrastructure to save the search_path value for a plan,
> but what it's being used for right now is to restore the first-plan-time
> value of the path when a replan is forced for some other reason. It
> wouldn't be that hard to change it around to use it this way instead.
>
> regards, tom lane

Something that would be really handy for applications using schemas for
implementing multi-tenant support would be allowing usage of a function
param in the SET section of the function. Something like this:

CREATE FUNCTION some_function(p_schema, ...)
RETURNS VOID AS $$
BEGIN
...
END;
$$ LANGUAGE plpgsql
SET search_path = p_schema, public;

Not sure what syntax to use since p_schema could be the name of some
existent schema but you got the idea.

This would avoid all the wrapper lines to save and restore the old
search_path (specially when there are earlier returns in the function body).