MIME-Version: 1.0
References: <20241227205025.1d059f72c7c08d23c9648c26@magnetkern.de>
 <CAKFQuwb4hgHH=Z6cx5Hh_qc10TCYMb1QVfP3099X1Psmyw0r3Q@mail.gmail.com> <2547821.1735333397@sss.pgh.pa.us>
In-Reply-To: <2547821.1735333397@sss.pgh.pa.us>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Fri, 27 Dec 2024 22:23:09 +0100
Message-ID: <CAFj8pRBvh_jhq_KkALZPt+N5Pim9FxC4uUEbEFhLB3f1u0gg_Q@mail.gmail.com>
Subject: Re: search_path for PL/pgSQL functions partially cached?
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>, Jan Behrens <jbe-mlist@magnetkern.de>, 
	"pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f08243062a4713d3"
Archived-At: <https://www.postgresql.org/message-id/CAFj8pRBvh_jhq_KkALZPt%2BN5Pim9FxC4uUEbEFhLB3f1u0gg_Q%40mail.gmail.com>
Precedence: bulk

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

p=C3=A1 27. 12. 2024 v 22:03 odes=C3=ADlatel Tom Lane <tgl@sss.pgh.pa.us> n=
apsal:

> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > 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.
>
> Yeah, I don't see this changing.  The actual answer is that we have
> search_path-aware caching of expressions and query plans within a
> plpgsql function, which is why the call to foo() reacts to the current
> search path.  But the types of plpgsql variables are only looked up
> on the first use (within a session).  Perhaps we ought to work harder
> on that, but it seems like a lot of overhead to add for something that
> will benefit next to nobody.
>
> > I=E2=80=99ve never really understood why =E2=80=9C%TYPE=E2=80=99 exists=
=E2=80=A6
>
> Compatibility with Oracle, I imagine.  I agree it's a bizarre feature.
> But you could get the same behavior without %TYPE, just by referencing
> some other type that has different declarations in different schemas.
>

This feature is not bizarre - just the implementation in Postgres is not
fully complete (and I am not sure if it is fixable). PLpgSQL uses plan
cache, but there is nothing similar for types.
It is designed for Oracle where search_path doesn't exist, and where change
of schema invalidates code, and requires recompilation. PL/pgSQL and
Postgres are much more dynamic systems than Oracle. Maybe PL/pgSQL
functions can holds dependency on types, and when any related custom type
is changed, then the cached function can be invalidated. Unfortunately, the
frequent change of search path can kill the performance.


> > 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 se=
ssion
> > search_path.
>
> Yeah, adding "set search_path" is recommendable if you don't want to
> think hard about this stuff.
>
>                         regards, tom lane
>
>
>

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote g=
mail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">p=C3=A1 27. 12.=
 2024 v=C2=A022:03 odes=C3=ADlatel Tom Lane &lt;<a href=3D"mailto:tgl@sss.p=
gh.pa.us">tgl@sss.pgh.pa.us</a>&gt; napsal:<br></div><blockquote class=3D"g=
mail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204=
,204,204);padding-left:1ex">&quot;David G. Johnston&quot; &lt;<a href=3D"ma=
ilto:david.g.johnston@gmail.com" target=3D"_blank">david.g.johnston@gmail.c=
om</a>&gt; writes:<br>
&gt; It is what it is - and if one is not careful one can end up writing<br=
>
&gt; hard-to-understand and possibly buggy code due to the various executio=
n<br>
&gt; environments and caches involved.<br>
<br>
Yeah, I don&#39;t see this changing.=C2=A0 The actual answer is that we hav=
e<br>
search_path-aware caching of expressions and query plans within a<br>
plpgsql function, which is why the call to foo() reacts to the current<br>
search path.=C2=A0 But the types of plpgsql variables are only looked up<br=
>
on the first use (within a session).=C2=A0 Perhaps we ought to work harder<=
br>
on that, but it seems like a lot of overhead to add for something that<br>
will benefit next to nobody.<br>
<br>
&gt; I=E2=80=99ve never really understood why =E2=80=9C%TYPE=E2=80=99 exist=
s=E2=80=A6<br>
<br>
Compatibility with Oracle, I imagine.=C2=A0 I agree it&#39;s a bizarre feat=
ure.<br>
But you could get the same behavior without %TYPE, just by referencing<br>
some other type that has different declarations in different schemas.<br></=
blockquote><div><br></div><div>This feature is not bizarre - just the imple=
mentation in Postgres is not fully complete (and I am not sure if it is fix=
able). PLpgSQL uses plan cache, but there is nothing similar for types.</di=
v><div>It is designed for Oracle where search_path doesn&#39;t exist, and w=
here change of schema invalidates code, and requires recompilation. PL/pgSQ=
L and</div><div>Postgres are much more dynamic systems than Oracle. Maybe P=
L/pgSQL functions can holds dependency on types, and when any related custo=
m type</div><div>is changed, then the cached function can be invalidated. U=
nfortunately, the frequent change of search path can kill the performance. =
<br></div><div><br></div><blockquote class=3D"gmail_quote" style=3D"margin:=
0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<br>
&gt; Add qualification or attach a =E2=80=9Cset search_path=E2=80=9D clause=
 to =E2=80=9Ccreate<br>
&gt; function=E2=80=9D.=C2=A0 Code stored in the server should not rely on =
the session<br>
&gt; search_path.<br>
<br>
Yeah, adding &quot;set search_path&quot; is recommendable if you don&#39;t =
want to<br>
think hard about this stuff.<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
<br>
<br>
</blockquote></div></div>

--000000000000f08243062a4713d3--