Re: search_path for PL/pgSQL functions partially cached?

Hi

pá 27. 12. 2024 v 21:26 odesílatel David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> napsal:

> On Friday, December 27, 2024, Jan Behrens <jbe-mlist(at)magnetkern(dot)de> wrote:
>>
>>
>> It seems that it matters *both* how the search_path was set during the
>> *first* invocation of the function within a session *and* how it is set
>> during the actual call of the function. So even if there are just two
>> schemas involved, there are 4 possible outcomes for the "run" function's
>> result ('2.4', '2', '5', and '5.4'). To me, this behavior seems to be
>> somewhat dangerous. Maybe it is even considered a bug?
>
>
> It is what it is - and if one is not careful one can end up writing
> hard-to-understand and possibly buggy code due to the various execution
> environments and caches involved.
>

I think plan cache should be invalidated when search_path is different, but
maybe there is some bug - there are some optimizations related to faster
execution of simple expressions.

> I’ve never really understood why “%TYPE’ exists…
>

referenced types should increase readability - it ensures type
compatibility - minimally on oracle, where the change of schema requires
recompilation. In Postgres it is working on 99% - plpgsql functions don't
hold dependency on types.

>
>> Or is it documented somewhere?
>
>
>
> https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING
>
> Can someone explain to me what's going on, and what is the best practice
>> to deal with it? Is there a way to avoid fully qualifying every type and
>> expression? Which parts do I have to qualify or is this something that
>> could be fixed in a future version of PostgreSQL?
>>
>
> Add qualification or attach a “set search_path” clause to “create
> function”. Code stored in the server should not rely on the session
> search_path.
>

a lot of functionality in Postgres depends on the search path - and then
all should be consistent. Sure, writing procedures that depend on the
current search path can be a short way to hell.

I cannot to reproduce it

CREATE OR REPLACE FUNCTION s1.fx1()
RETURNS integer
LANGUAGE plpgsql
AS $function$
begin
return 100;
end
$function$

CREATE OR REPLACE FUNCTION s2.fx1()
RETURNS integer
LANGUAGE plpgsql
AS $function$
begin
return 200;
end
$function$

CREATE OR REPLACE FUNCTION public.foo()
RETURNS void
LANGUAGE plpgsql
AS $function$
declare v int;
begin v := fx1();
raise notice '%', v;
end;
$function$

(2024-12-27 21:53:13) postgres=# set search_path to s1;
SET
(2024-12-27 21:53:34) postgres=# select public.foo();
NOTICE: 100
┌─────┐
│ foo │
╞═════╡
│ │
└─────┘
(1 row)

(2024-12-27 21:53:44) postgres=# set search_path to s2;
SET
(2024-12-27 21:53:47) postgres=# select public.foo();
NOTICE: 200
┌─────┐
│ foo │
╞═════╡
│ │
└─────┘
(1 row)

(2024-12-27 21:53:48) postgres=# set search_path to s1;
SET
(2024-12-27 21:53:51) postgres=# select public.foo();
NOTICE: 100
┌─────┐
│ foo │
╞═════╡
│ │
└─────┘
(1 row)

so from my perspective is pg ok, tested on pg16 and pg18

> David J.
>
>