Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tRHPe-008Sox-CM for pgsql-general@arkaria.postgresql.org; Fri, 27 Dec 2024 20:58:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tRHPd-00D1g2-4I for pgsql-general@arkaria.postgresql.org; Fri, 27 Dec 2024 20:58:24 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tRHPc-00D1ft-HI for pgsql-general@lists.postgresql.org; Fri, 27 Dec 2024 20:58:24 +0000 Received: from mail-yb1-xb29.google.com ([2607:f8b0:4864:20::b29]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tRHPa-000qyo-1v for pgsql-general@lists.postgresql.org; Fri, 27 Dec 2024 20:58:23 +0000 Received: by mail-yb1-xb29.google.com with SMTP id 3f1490d57ef6-e537d9e3d75so8795834276.3 for ; Fri, 27 Dec 2024 12:58:22 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1735333102; x=1735937902; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=DPy5/rED4Az/LE9pm4SUlxGwsxvUHaZW61kK8x+REF0=; b=GNSIFCluLe0JepFmKQpo6Zlj17pgP53UwdQ7Wk7ydLyF0S8zsO+n0gvR0m7mRQ1nLQ E7eV79A1EZoyvQMiLxLZfoQA322/2EA7TqodXiRLMEzUuODxmd5jjb8OWQepDWfkoE/m z74tOZXEQtt2cj+hBgfTB7AuK/g9pd/O5a1rWVTGODRlyqkPg+a1Oqyqn17okKRU7CtD 2xGIJ7g7ds7HytO9lKTnI2jDhvcpl9U5IimrRbY7ZU4WCE6yqaEUAqp1i/Y+WVwIl7FF S4JgccrvaLhTuaAhQUMrCXEUmg0ySJYnVBXieAxQkF+VX27+jl4QealsTjIExvfwZPqB ez7g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1735333102; x=1735937902; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=DPy5/rED4Az/LE9pm4SUlxGwsxvUHaZW61kK8x+REF0=; b=PrNIWX843GGL9QytmAZRg1NFpUDgj1a3qKDe/NNZ1MQRX3bEEyb6bz/IZCaeXKYMsg Tpm2spOTa/30CgEey1wuLIy0FCy6B4m6Ma3XTPNG1lcNARy88D1+b+nStgbA6Y7pWh8O V6+m8EKTV5RdAy8Azm0NUgVXQsvMQ6sKDVqHpDjIdxp0pOd3Ugnz1Hf3oKwt7S7MzeID U4uOLmAwCpkYWsXMGIKBZl1ORkFTsuIw9CMUarV9eY3TudHFTkcByuBElRD12vHkBIAV rIs8WEpkc0a2akWHhjPa7XVT4udOGJ3us4EFcsKDhdh2ynbn/CG9eEs5o3BDLeT/NlLR Qgnw== X-Forwarded-Encrypted: i=1; AJvYcCXjwjywG/8CNowxvL/Wlla7dKxGguMOuaalYkvu1J99gFXqG1+MqEMxHXqyIlVN2Yv6l4/fmexEEPA0vuyV@lists.postgresql.org X-Gm-Message-State: AOJu0YwlTCF8Go64YsKCs6HbPBAcRkMO++vZdDWQ2fBPpvCaKEw1ndv2 LzsMC5o7aAuGk14oCTpo/ry9gO0DHAE0BMvA2MolpU+EXhx8WYwEUc2/SavxamVjfTDHZwZVhIM wrS0GN+OOuUNFe8mBeoODlDqQot8= X-Gm-Gg: ASbGncu9EXbwN2q4K8M5PJ3Jg6VsIMQLipFnwZ4MffX9sjlNJQKh8BfSGryoDXiAM+A PIMOY9xt+kg+fl9l+ri0TPzmFkbt4XIPUL1IiX1te8RSS5bK+TatsP4RrcgXak4XRGJIDGG0= X-Google-Smtp-Source: AGHT+IHEV/xQVqSEZYi6+n0g5Z8jr+c7+QjWA6yfLq+1wgNLTjXe20jK+59A/HsecJDd4LXxf2fQK9llrnQtrTAI9Dc= X-Received: by 2002:a05:690c:688d:b0:6ef:7ac0:1ab8 with SMTP id 00721157ae682-6f3f8240e8fmr228496757b3.40.1735333101845; Fri, 27 Dec 2024 12:58:21 -0800 (PST) MIME-Version: 1.0 References: <20241227205025.1d059f72c7c08d23c9648c26@magnetkern.de> In-Reply-To: From: Pavel Stehule Date: Fri, 27 Dec 2024 21:57:45 +0100 Message-ID: Subject: Re: search_path for PL/pgSQL functions partially cached? To: "David G. Johnston" Cc: Jan Behrens , "pgsql-general@lists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000001a447d062a46b9b5" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000001a447d062a46b9b5 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi p=C3=A1 27. 12. 2024 v 21:26 odes=C3=ADlatel David G. Johnston < david.g.johnston@gmail.com> napsal: > On Friday, December 27, 2024, Jan Behrens 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=E2=80=99ve never really understood why =E2=80=9C%TYPE=E2=80=99 exists= =E2=80=A6 > 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#PLPGS= QL-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 =E2=80=9Cset search_path=E2=80=9D clause to= =E2=80=9Ccreate > function=E2=80=9D. Code stored in the server should not rely on the sess= ion > 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 :=3D fx1(); raise notice '%', v; end; $function$ (2024-12-27 21:53:13) postgres=3D# set search_path to s1; SET (2024-12-27 21:53:34) postgres=3D# select public.foo(); NOTICE: 100 =E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=90 =E2=94=82 foo =E2=94=82 =E2=95=9E=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=A1 =E2=94=82 =E2=94=82 =E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=98 (1 row) (2024-12-27 21:53:44) postgres=3D# set search_path to s2; SET (2024-12-27 21:53:47) postgres=3D# select public.foo(); NOTICE: 200 =E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=90 =E2=94=82 foo =E2=94=82 =E2=95=9E=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=A1 =E2=94=82 =E2=94=82 =E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=98 (1 row) (2024-12-27 21:53:48) postgres=3D# set search_path to s1; SET (2024-12-27 21:53:51) postgres=3D# select public.foo(); NOTICE: 100 =E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=90 =E2=94=82 foo =E2=94=82 =E2=95=9E=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=A1 =E2=94=82 =E2=94=82 =E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=98 (1 row) so from my perspective is pg ok, tested on pg16 and pg18 > David J. > > --0000000000001a447d062a46b9b5 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi

p=C3=A1 27. 12. 2024 v=C2= =A021:26 odes=C3=ADlatel David G. Johnston <david.g.johnston@gmail.com> napsal:
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 *fir= st* 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 invo= lved, 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 sea= rch_path is different, but maybe there is some bug - there are some optimiz= ations related to faster execution of simple expressions.


I= =E2=80=99ve never really understood why =E2=80=9C%TYPE=E2=80=99 exists=E2= =80=A6

referenced types should increa= se 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.

<= /div>


Or is it documented somewhere?=C2=A0


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 qualify= ing 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 =E2=80=9Cset search= _path=E2=80=9D clause to =E2=80=9Ccreate function=E2=80=9D.=C2=A0 Code stor= ed in the server should not rely on the session search_path.

a lot of functionality in Postgres depends on the s= earch path - and then all should be consistent. Sure, writing procedures th= at depend on the current search path can be a short way to hell.
=
I cannot to reproduce it

CREATE OR REPLACE FUNCTION s1.fx1()
=C2=A0RE= TURNS integer
=C2=A0LANGUAGE plpgsql
AS $function$
begin
=C2=A0= return 100;
end
$function$

CREATE OR REPLACE FUNCTION s2.fx1()
=C2=A0RETURNS integer
=C2=A0LA= NGUAGE plpgsql
AS $function$
begin
=C2=A0 return 200;
end
$f= unction$
=C2=A0<= /span>
CREATE OR REPLACE FU= NCTION public.foo()
=C2=A0RETURNS void
=C2=A0LANGUAGE plpgsql
AS $= function$=C2=A0
decl= are v int;
begin v :=3D fx1();
=C2=A0 raise notice '%', v;end;
$function$
=
(2024-12-27 21:= 53:13) postgres=3D# set search_path to s1;
SET
(2024-12-27 21:53:34) = postgres=3D# select public.foo();
NOTICE: =C2=A0100
=E2=94=8C=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=90
=E2=94=82 foo =E2=94=82=
=E2=95=9E=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=A1
=E2= =94=82 =C2=A0 =C2=A0 =E2=94=82
=E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=98
(1 row)

(2024-12-27 21:53:44) postgres= =3D# set search_path to s2;
SET
(2024-12-27 21:53:47) postgres=3D# se= lect public.foo();
NOTICE: =C2=A0200
=E2=94=8C=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=90
=E2=94=82 foo =E2=94=82
=E2=95=9E= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=A1
=E2=94=82 =C2=A0 = =C2=A0 =E2=94=82
=E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=98
(1 row)

(2024-12-27 21:53:48) postgres=3D# set search_p= ath to s1;
SET
(2024-12-27 21:53:51) postgres=3D# select public.foo()= ;
NOTICE: =C2=A0100
=E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=90
=E2=94=82 foo =E2=94=82
=E2=95=9E=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=A1
=E2=94=82 =C2=A0 =C2=A0 =E2=94=82=E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=98
(1 row= )

so from my perspective is pg ok, tested o= n pg16 and pg18




=


David J.

--0000000000001a447d062a46b9b5--