MIME-Version: 1.0
References: <20241227205025.1d059f72c7c08d23c9648c26@magnetkern.de>
 <CAKFQuwb4hgHH=Z6cx5Hh_qc10TCYMb1QVfP3099X1Psmyw0r3Q@mail.gmail.com>
 <20241228004009.267f21b78394c934f27f9974@magnetkern.de> <20250101185504.3d50c571c3448512e94288e8@magnetkern.de>
 <CAKFQuwZ1G5p+nAqS4OCQ37duyqPkf8oNNEJ2p6HwDb0RzGzBTg@mail.gmail.com>
 <20250102113727.1574b14fd677d164c32160bc@magnetkern.de> <CAFj8pRCb1aRzB4MmPX-X5BNF6-JgKMfDeirgnYNL-_gbCOqp8w@mail.gmail.com>
 <20250102131547.f47d7eeb27c7a8256a8cd189@magnetkern.de> <CAFj8pRBAt3HJf2zX3+BC7sAqnmDgxhfeKf-fqiJtO1GScU-HYQ@mail.gmail.com>
 <20250102163440.17acbac07e2e32016cb5433c@magnetkern.de>
In-Reply-To: <20250102163440.17acbac07e2e32016cb5433c@magnetkern.de>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Thu, 2 Jan 2025 17:20:05 +0100
Message-ID: <CAFj8pRDo8uOBQqqu=qKJVr9XDyx7L0_GNmu9TEEe3DWU=9tU5w@mail.gmail.com>
Subject: Re: search_path for PL/pgSQL functions partially cached?
To: Jan Behrens <jbe-mlist@magnetkern.de>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>, 
	"pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000200a1e062abb8b11"
Archived-At: <https://www.postgresql.org/message-id/CAFj8pRDo8uOBQqqu%3DqKJVr9XDyx7L0_GNmu9TEEe3DWU%3D9tU5w%40mail.gmail.com>
Precedence: bulk

--000000000000200a1e062abb8b11
Content-Type: text/plain; charset="UTF-8"

Hi


> >
> > some times can be pretty ineffective to have database per customer - more
> > connect, disconnect in postgres is much more expensive than SET
> search_path
> > TO .. and maybe RESET plans;
>
> I guess that means there is a practical application where search_path
> MAY change at runtime IF done in different sessions or if the cache is
> reset using the DISCARD command:
>
> https://www.postgresql.org/docs/17/sql-discard.html
>
> I assume DISCARD PLANS would be the right command?
>

that depends. plan inside plan cache is invalidated when search_path is
different. You use RESET plans because you want to release all plans
quickly.

Unfortunately, the types assigned to plpgsql variables are not invalidated.
This is the source of problems. It is a classical problem - it is hard to
say when you should invalidate cache.
Current design is not ideal - but it is almost a good enough compromise
between correctness and performance. It is true, so nobody did some work to
fix it. So maybe the impact to performance should not be too bad, but it is
not an easy issue. plans are isolated - and the impact of one plan to the
second plan is zero. For variables it is exactly opposite.


>
> This seems to be a very special case though. I think there should be a
> warning in the documentation of CREATE FUNCTION with regard to schemas
> anyway, though.
>

I am not sure. If you want to use this warning, then it should be
everywhere where any non-qualified identifier can be used. Maybe in plpgsql
can be more accented so almost everything in plpgsql depends on the current
setting of search_path. Lot of people don't understand, so every expression
in plpgsql is SQL and every expression is executed like part of a query.
And unfortunately there are some different caches - plpgsql cache and plan
cache and both caches are invalidated at different times (I think so
plpgsql cache is not resetted by RESET PLANS). Maybe it is better to
explain how plpgsql works. It is a little bit different from well known
interpreted languages.


> Regards,
> Jan
>

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

<div dir=3D"ltr"><div class=3D"gmail_quote gmail_quote_container"><div>Hi<b=
r></div><div>=C2=A0</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">
&gt; <br>
&gt; some times can be pretty ineffective to have database per customer - m=
ore<br>
&gt; connect, disconnect in postgres is much more expensive than SET search=
_path<br>
&gt; TO .. and maybe RESET plans;<br>
<br>
I guess that means there is a practical application where search_path<br>
MAY change at runtime IF done in different sessions or if the cache is<br>
reset using the DISCARD command:<br>
<br>
<a href=3D"https://www.postgresql.org/docs/17/sql-discard.html" rel=3D"nore=
ferrer" target=3D"_blank">https://www.postgresql.org/docs/17/sql-discard.ht=
ml</a><br>
<br>
I assume DISCARD PLANS would be the right command?<br></blockquote><div><br=
></div><div>that depends. plan inside plan cache is invalidated when search=
_path is different. You use RESET plans because you want to release all pla=
ns quickly.</div><div><br></div><div>Unfortunately, the types assigned to p=
lpgsql variables are not invalidated. This is the source of problems. It is=
 a classical problem - it is hard to say when you should invalidate cache. =
<br></div><div>Current design is not ideal - but it is almost a good enough=
 compromise between correctness and performance. It is true, so nobody did =
some work to fix it. So maybe the impact to performance should not be too b=
ad, but it is not an easy issue. plans are isolated - and the impact of one=
 plan to the second plan is zero. For variables it is exactly opposite.<br>=
</div><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0p=
x 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<br>
This seems to be a very special case though. I think there should be a<br>
warning in the documentation of CREATE FUNCTION with regard to schemas<br>
anyway, though.<br></blockquote><div><br></div><div>I am not sure. If you w=
ant to use this warning, then it should be everywhere where any non-qualifi=
ed identifier can be used. Maybe in plpgsql can be more accented so almost =
everything in plpgsql depends on the current setting of search_path. Lot of=
 people don&#39;t understand, so every expression in plpgsql is SQL and eve=
ry expression is executed like part of a query. And unfortunately there are=
 some different caches - plpgsql cache and plan cache and both caches are i=
nvalidated at different times (I think so plpgsql cache is not resetted by =
RESET PLANS). Maybe it is better to explain how plpgsql works. It is a litt=
le bit different from well known interpreted languages.<br></div><div><br><=
/div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bo=
rder-left:1px solid rgb(204,204,204);padding-left:1ex">
<br>
Regards,<br>
Jan<br>
</blockquote></div></div>

--000000000000200a1e062abb8b11--