Feedback-ID: i76984098:Fastmail
Message-ID: <ebe401db-e5a6-49f8-808b-3f007601b8ed@aklaver.com>
Date: Tue, 18 Jun 2024 09:19:30 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: set search_path "$owner". And name versus literal for schemas.
To: Dominique Devienne <ddevienne@gmail.com>,
 pgsql-general@lists.postgresql.org
References: <CAFCRh-8Bnh7PMFKjx=wsueTw6-CEbocOOz1SUpFKbvOjPD+shg@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAFCRh-8Bnh7PMFKjx=wsueTw6-CEbocOOz1SUpFKbvOjPD+shg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/ebe401db-e5a6-49f8-808b-3f007601b8ed%40aklaver.com>
Precedence: bulk

On 6/18/24 01:35, Dominique Devienne wrote:
> Hi. Two things related to the search_path.
> 
> First, [the doc][1] mentions one can use a variable like "$user" for
> the search_path. But setting the search_path is also for FUNCTIONs and
> PROCEDUREs, and there what I really REALLY would like, is the ability
> to use "$owner", to limit the search_path to the OWNER schema of that
> func/proc, instead of having to explicitly spell it out. When I want
> to *clone* a schema, having to "patch" the search_path of all those
> funcs/procs (to replace the old schema with the new one), is a real
> PITA.
> 
> Has this ever been considered? And if so, why was it refused?
> It would simplify my life so much, I wonder why this doesn't already exist.

I could see this. I would choose something other then $owner as you are 
not really concerned with the func/proc owner but it's location. 
Something like $home would seem more on point.

> 
> Second, and related to the first point, when I introspect a schema,
> the search_path
> of functions/procedures seems to be rewritten with literals, instead of names.
> Even the doc uses names, so why is it rewritten as literals? Or
> accepts both in fact.
> 
> To actually simplify schema cloning, the introspected proc/func
> search_path should remain "$owner" (or '$owner' I guess...) and not be
> expanded. Otherwise we'd back to "manual" patching of the search_path,
> which again is a PITA.
> 
> I'm curious to hear/read what PostgreSQL experts have to say on this subject.
> 
> Thanks, --DD
> 
> [1]: https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com