Re: CREATE FUNCTION ... SEARCH { DEFAULT | SYSTEM | SESSION }

On 16.08.23 19:44, Jeff Davis wrote:
> On Wed, 2023-08-16 at 08:51 +0200, Peter Eisentraut wrote:
>> On 12.08.23 04:35, Jeff Davis wrote:
>>> The attached patch implements a new SEARCH clause for CREATE
>>> FUNCTION.
>>> The SEARCH clause controls the search_path used when executing
>>> functions that were created without a SET clause.
>>
>> I don't understand this.  This adds a new option for cases where the
>> existing option wasn't specified.  Why not specify the existing
>> option
>> then?  Is it not good enough?  Can we improve it?
>
> SET search_path = '...' not good enough in my opinion.
>
> 1. Not specifying a SET clause falls back to the session's search_path,
> which is a bad default because it leads to all kinds of inconsistent
> behavior and security concerns.

Not specifying SEARCH would have the same issue?

> 2. There's no way to explicitly request that you'd actually like to use
> the session's search_path, so it makes it very hard to ever change the
> default.

That sounds like something that should be fixed independently. I could
see this being useful for other GUC settings, like I want to run a
function explicitly with the session's work_mem.

> 3. It's user-unfriendly. A safe search_path that would be suitable for
> most functions is "SET search_path = pg_catalog, pg_temp", which is
> arcane, and requires some explanation.

True, but is that specific to functions? Maybe I want a safe
search_path just in general, for a session or something.

> 4. search_path for the session is conceptually different than for a
> function. A session should be context-sensitive and the same query
> should (quite reasonably) behave differently for different sessions and
> users to sort out things like object name conflicts, etc. A function
> should (ordinarily) be context-insensitive, especially when used in
> something like an index expression or constraint. Having different
> syntax helps separate those concepts.

I'm not sure I follow that. When you say a function should be
context-insensitive, you could also say, a function should be
context-sensitive, but have a separate context. Which is kind of how it
works now. Maybe not well enough.

> 5. There's no way to prevent pg_temp from being included in the
> search_path. This is separately fixable, but having the proposed SEARCH
> syntax is likely to make for a better user experience in the common
> cases.

seems related to #3

> I'm open to suggestion about other ways to improve it, but SEARCH is
> what I came up with.

Some extensions of the current mechanism, like search_path = safe,
search_path = session, search_path = inherit, etc. might work.