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