MIME-Version: 1.0
In-Reply-To: <20241227205025.1d059f72c7c08d23c9648c26@magnetkern.de>
References: <20241227205025.1d059f72c7c08d23c9648c26@magnetkern.de>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Fri, 27 Dec 2024 13:26:28 -0700
Message-ID: <CAKFQuwb4hgHH=Z6cx5Hh_qc10TCYMb1QVfP3099X1Psmyw0r3Q@mail.gmail.com>
Subject: Re: search_path for PL/pgSQL functions partially cached?
To: Jan Behrens <jbe-mlist@magnetkern.de>
Cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000122550062a464778"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwb4hgHH%3DZ6cx5Hh_qc10TCYMb1QVfP3099X1Psmyw0r3Q%40mail.gmail.com>
Precedence: bulk

--000000000000122550062a464778
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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=E2=80=99ve never really understood why =E2=80=9C%TYPE=E2=80=99 exists=E2=
=80=A6


> 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 =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 sessio=
n
search_path.

David J.

--000000000000122550062a464778
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Friday, December 27, 2024, Jan Behrens &lt;<a href=3D"mailto:jbe-mlist@m=
agnetkern.de">jbe-mlist@magnetkern.de</a>&gt; wrote:<blockquote class=3D"gm=
ail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-le=
ft:1ex">
<br>
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 &quot;run&quot; function&#39;s =
result (&#39;2.4&#39;, &#39;2&#39;, &#39;5&#39;, and &#39;5.4&#39;). To me,=
 this behavior seems to be somewhat dangerous. Maybe it is even considered =
a bug?</blockquote><div><br></div><div>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.</div><div><b=
r></div><div>I=E2=80=99ve never really understood why =E2=80=9C%TYPE=E2=80=
=99 exists=E2=80=A6</div><div><br></div><blockquote class=3D"gmail_quote" s=
tyle=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><br>=
</blockquote><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;b=
order-left:1px #ccc solid;padding-left:1ex">Or is it documented somewhere?=
=C2=A0</blockquote><div><br></div><div>=C2=A0<a href=3D"https://www.postgre=
sql.org/docs/current/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING">http=
s://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-PLA=
N-CACHING</a></div><div><br></div><blockquote class=3D"gmail_quote" style=
=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Can some=
one explain to me what&#39;s going on, and what is the best practice to dea=
l with it? Is there a way to avoid fully qualifying every type and expressi=
on? Which parts do I have to qualify or is this something that could be fix=
ed in a future version of PostgreSQL?<br></blockquote><div><br></div><div>A=
dd qualification or attach a =E2=80=9Cset search_path=E2=80=9D clause to =
=E2=80=9Ccreate function=E2=80=9D.=C2=A0 Code stored in the server should n=
ot rely on the session search_path.</div><div><br></div><div>David J.</div>=
<div><br></div>

--000000000000122550062a464778--