Re: CREATE FUNCTION ... SEARCH { DEFAULT | SYSTEM | SESSION } - Mailing list pgsql-hackers

From Jeff Davis
Subject Re: CREATE FUNCTION ... SEARCH { DEFAULT | SYSTEM | SESSION }
Date
Msg-id 6b77a343f11b96c1d12226fe869cd71c7ff18b56.camel@j-davis.com
Whole thread Raw
In response to Re: CREATE FUNCTION ... SEARCH { DEFAULT | SYSTEM | SESSION }  (Peter Eisentraut <peter@eisentraut.org>)
Responses Re: CREATE FUNCTION ... SEARCH { DEFAULT | SYSTEM | SESSION }
Re: CREATE FUNCTION ... SEARCH { DEFAULT | SYSTEM | SESSION }
List pgsql-hackers
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




pgsql-hackers by date:

Previous
From: jian he
Date:
Subject: Re: Extract numeric filed in JSONB more effectively
Next
From: Nathan Bossart
Date:
Subject: Re: Replace known_assigned_xids_lck by memory barrier