public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: David G. Johnston <[email protected]>
To: Jan Behrens <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: search_path for PL/pgSQL functions partially cached?
Date: Fri, 27 Dec 2024 12:59:15 -0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAKFQuwb4hgHH=Z6cx5Hh_qc10TCYMb1QVfP3099X1Psmyw0r3Q@mail.gmail.com>
References: <[email protected]>
	<CAKFQuwb4hgHH=Z6cx5Hh_qc10TCYMb1QVfP3099X1Psmyw0r3Q@mail.gmail.com>

On 12/27/24 12:26, David G. Johnston wrote:
> On Friday, December 27, 2024, Jan Behrens <[email protected] 
> <mailto:[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’ve never really understood why “%TYPE’ exists…

Per:

https://www.postgresql.org/docs/current/plpgsql-declarations.html#PLPGSQL-DECLARATION-TYPE

"By using %TYPE you don't need to know the data type of the structure 
you are referencing, and most importantly, if the data type of the 
referenced item changes in the future (for instance: you change the type 
of user_id from integer to real), you might not need to change your 
function definition.

%TYPE is particularly valuable in polymorphic functions, since the data 
types needed for internal variables can change from one call to the 
next. Appropriate variables can be created by applying %TYPE to the 
function's arguments or result placeholders."

The second case I can buy, the first I am not so sure of. It seems to me 
the first case it can be 'solved' by the second case.


> 
> 
>     Or is it documented somewhere? 
> 
> 
> https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING <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.
> 
> David J.
> 

-- 
Adrian Klaver
[email protected]

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: <[email protected]>

* 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