On Friday, December 27, 2024, Jan Behrens <jbe-mlist@magnetkern.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?
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.
