Date: Thu, 2 Jan 2025 16:34:40 +0100
From: Jan Behrens <jbe-mlist@magnetkern.de>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>,
 "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: Re: search_path for PL/pgSQL functions partially cached?
Message-Id: <20250102163440.17acbac07e2e32016cb5433c@magnetkern.de>
In-Reply-To: <CAFj8pRBAt3HJf2zX3+BC7sAqnmDgxhfeKf-fqiJtO1GScU-HYQ@mail.gmail.com>
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>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/20250102163440.17acbac07e2e32016cb5433c%40magnetkern.de>
Precedence: bulk

On Thu, 2 Jan 2025 13:48:29 +0100
Pavel Stehule <pavel.stehule@gmail.com> wrote:

> čt 2. 1. 2025 v 13:15 odesílatel Jan Behrens <jbe-mlist@magnetkern.de>
> napsal:
> 
> > On Thu, 2 Jan 2025 12:40:59 +0100
> > Pavel Stehule <pavel.stehule@gmail.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