Feedback-ID: i76984098:Fastmail
Message-ID: <58f46d3c-7c62-4518-90d7-03d4e3dfe3cf@aklaver.com>
Date: Fri, 27 Dec 2024 12:59:15 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: search_path for PL/pgSQL functions partially cached?
To: "David G. Johnston" <david.g.johnston@gmail.com>,
 Jan Behrens <jbe-mlist@magnetkern.de>
Cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
References: <20241227205025.1d059f72c7c08d23c9648c26@magnetkern.de>
 <CAKFQuwb4hgHH=Z6cx5Hh_qc10TCYMb1QVfP3099X1Psmyw0r3Q@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAKFQuwb4hgHH=Z6cx5Hh_qc10TCYMb1QVfP3099X1Psmyw0r3Q@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/58f46d3c-7c62-4518-90d7-03d4e3dfe3cf%40aklaver.com>
Precedence: bulk

On 12/27/24 12:26, David G. Johnston wrote:
> On Friday, December 27, 2024, Jan Behrens <jbe-mlist@magnetkern.de 
> <mailto: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’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
adrian.klaver@aklaver.com