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

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.

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.

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.

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.

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.

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

Regards,
Jeff Davis