public inbox for [email protected]  

help / color / mirror / Atom feed

From: Jan Behrens <[email protected]>
To: Pavel Stehule <[email protected]>
Cc: David G. Johnston <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: search_path for PL/pgSQL functions partially cached?
Date: Thu, 2 Jan 2025 16:34:40 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAFj8pRBAt3HJf2zX3+BC7sAqnmDgxhfeKf-fqiJtO1GScU-HYQ@mail.gmail.com>
References: <[email protected]>
	<CAKFQuwb4hgHH=Z6cx5Hh_qc10TCYMb1QVfP3099X1Psmyw0r3Q@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<CAKFQuwZ1G5p+nAqS4OCQ37duyqPkf8oNNEJ2p6HwDb0RzGzBTg@mail.gmail.com>
	<[email protected]>
	<CAFj8pRCb1aRzB4MmPX-X5BNF6-JgKMfDeirgnYNL-_gbCOqp8w@mail.gmail.com>
	<[email protected]>
	<CAFj8pRBAt3HJf2zX3+BC7sAqnmDgxhfeKf-fqiJtO1GScU-HYQ@mail.gmail.com>

On Thu, 2 Jan 2025 13:48:29 +0100
Pavel Stehule <[email protected]> wrote:

> čt 2. 1. 2025 v 13:15 odesílatel Jan Behrens <[email protected]>
> napsal:
> 
> > On Thu, 2 Jan 2025 12:40:59 +0100
> > Pavel Stehule <[email protected]> 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

view thread (33+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: search_path for PL/pgSQL functions partially cached?
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox