public inbox for [email protected]  

help / color / mirror / Atom feed

From: Pavel Stehule <[email protected]>
To: David G. Johnston <[email protected]>
Cc: Jan Behrens <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: search_path for PL/pgSQL functions partially cached?
Date: Fri, 27 Dec 2024 21:57:45 +0100
Message-ID: <CAFj8pRD5gOGjpOph=Hc72nfWSjy5W6OyG9p20VRng-aSYXxNsA@mail.gmail.com> (raw)
In-Reply-To: <CAKFQuwb4hgHH=Z6cx5Hh_qc10TCYMb1QVfP3099X1Psmyw0r3Q@mail.gmail.com>
References: <[email protected]>
	<CAKFQuwb4hgHH=Z6cx5Hh_qc10TCYMb1QVfP3099X1Psmyw0r3Q@mail.gmail.com>

Hi

pá 27. 12. 2024 v 21:26 odesílatel David G. Johnston <
[email protected]> napsal:

> On Friday, December 27, 2024, Jan Behrens <[email protected]> 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.
>
>

view thread (33+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: search_path for PL/pgSQL functions partially cached?
  In-Reply-To: <CAFj8pRD5gOGjpOph=Hc72nfWSjy5W6OyG9p20VRng-aSYXxNsA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox