Re: search_path for PL/pgSQL functions partially cached?

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Pavel Stehule <[email protected]>
To: Jan Behrens <[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 13:48:29 +0100
Message-ID: <CAFj8pRBAt3HJf2zX3+BC7sAqnmDgxhfeKf-fqiJtO1GScU-HYQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
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]>

č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;




>
> >
> > Using qualified identifiers everywhere strongly reduces readability.
> There
> > are no aliases to the schema, so aliases cannot help.
>
> Yes, I agree on that. Using "SET search_path" in the function's
> definition fixes that problem, but it's easy to miss how important this
> is from reading the documentation:
>
> The manual regarding "CREATE FUNCTION" refers to "search_path" only
> within the "Writing SECURITY DEFINER Functions Safely" section. It's
> easy to skip that part unless you use that feature. Moreover, that
> section alone doesn't explain the weird behavior of four different
> outcomes of a function with only two schemas involved which I brought
> up in the beginning of this thread.
>
> The part on "SET configuration_parameter" part in the "CREATE FUNCTION"
> documentation doesn't mention the search_path or schemas. And I don't
> think you can expect every programmer will read the "Plan Caching"
> subsection in the "PL/pgSQL under the Hood" section. But even then, the
> information is just provided indirectly.
>
>
yes, probably nobody reads the plan caching doc. And if they read it, then
because they have performance problems.



> Searching for "schema" in "CREATE FUNCTION"'s documentation doesn't
> give any hint either.
>

This is a question - this is a generic feature in Postgres.  Every query
can be impacted by setting of search_path.

From my perspective, there can be a note in the documentation related to
copy types and row types.

https://www.postgresql.org/docs/current/plpgsql-declarations.html#PLPGSQL-DECLARATION-TYPE

The problem that you found is not just about the change of search_path.
Same problem can be found after altering the table.

Regards

Pavel


> I think (assuming that the behavior isn't fixed) that some slighly more
> prominent warning would be reasonable.
>
> >
> > you can identify the functions where search_path is not explicitly
> assigned
> >
> > select oid::regprocedure
> >   from pg_proc
> > where pronamespace::regnamespace not in ('pg_catalog',
> > 'information_schema')
> >    and not exists(select 1 from unnest(proconfig) g(v) where  v ~
> > '^search_path');
> >
> >
> > Regards
> >
> > Pavel
>
> Kind 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: <CAFj8pRBAt3HJf2zX3+BC7sAqnmDgxhfeKf-fqiJtO1GScU-HYQ@mail.gmail.com>

* 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