Re: search_path for PL/pgSQL functions partially cached? - Mailing list pgsql-general
| From | Pavel Stehule |
|---|---|
| Subject | Re: search_path for PL/pgSQL functions partially cached? |
| Date | |
| Msg-id | CAFj8pRBAt3HJf2zX3+BC7sAqnmDgxhfeKf-fqiJtO1GScU-HYQ@mail.gmail.com Whole thread Raw |
| In response to | Re: search_path for PL/pgSQL functions partially cached? (Jan Behrens <jbe-mlist@magnetkern.de>) |
| Responses |
Re: search_path for PL/pgSQL functions partially cached?
|
| List | pgsql-general |
č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;
>
> 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.
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
pgsql-general by date: