Re: search_path for PL/pgSQL functions partially cached?

On Thu, 2 Jan 2025 13:48:29 +0100
Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:

> čt 2. 1. 2025 v 13:15 odesílatel Jan Behrens <jbe-mlist(at)magnetkern(dot)de>
> napsal:
>
> > On Thu, 2 Jan 2025 12:40:59 +0100
> > Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> >
> > > How can you identify unwanted usage of non qualified identifiers from
> > > wanted usage of non qualified identifiers? It is a common pattern for
> > > sharding. Using not qualified identifiers of operators, functions is
> > common
> > > when you are using orafce extensions, etc.
> >
> > I don't fully understand the use-case. Could you elaborate?
> >
> > As I understand, even if identifiers are not fully-qualified, it is
> > forbidden to use the search_path to refer to different database
> > entities at run-time (as David pointed out).
> >
> > So I don't understand how a dynamic "search_path" could be used in any
> > scenario within functions except when EXECUTE is involved.
> >
>
> you don't need more databases
>
> schema one - customer x
> schema two - customer y
>
> create table one.t1(..); create table one.t2(..);
> create table two.t1(..); create table two.t2(..);
>
> set search_path to one;
> -- work with data set of customer x
>
> set search_path to two;
> -- work wit data set of customer y
>
> 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?

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.

Regards,
Jan