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

On Mon, 2023-08-21 at 15:14 -0400, Robert Haas wrote:
> Another, related thing that I recently discovered would
> be useful is a way to say "I'd like to switch the search_path to X,
> but I'd also like to discover what the prevailing search_path was
> just
> before entering this function."

Interesting, that could probably be accommodated one way or another.

> However, my overall concern here is that this feels like it's
> reinventing the wheel. We already have a way of setting search_path;
> this gives us a second one.

In one sense, you are obviously right. We have a way to set search_path
for a function already, just like any other GUC.

But I don't look at the search_path as "just another GUC" when it comes
to executing a function. The source of the initial value of search_path
is more like the IMMUTABLE marker.

We can also do something with the knowledge the SEARCH marker gives us.
For instance, issue WARNINGs or ERRORs when someone uses a SEARCH
SESSION function in an index expression or constraint, or perhaps when
they try to declare a function IMMUTABLE in the first place.

In other words, the SEARCH clause tells us where search_path comes
from, not so much what it is specifically. I believe that tells us
something fundamental about the kind of function it is. If I tell you
nothing about a function except whether the search path comes from the
system or the session, you can imagine how it should be used (or not
used, as the case may be).

> I'm inclined to think that if there are semantics that we currently
> lack, we should think of extending the current syntax to support
> them.
> Right now you can SET search_path = 'specific value' or SET
> search_path FROM CURRENT or leave it out. We could introduce a new
> way
> of spelling "leave it out," like RESET search_path or whatever.

The thought occurred to me but any way I looked at it was messier and
less user-friendly. It feels like generalizing from search_path to all
GUCs, and then needing to specialize for search_path anyway.

For instance, if we want the default search_path to be the safe value
'pg_catalog, pg_temp', where would that default value come from? Or
instead, we could say that the default would be FROM CURRENT, which
would seem to generalize; but then we immediately run into the problem
that we don't want most GUCs to default to FROM CURRENT (because that
would capture the entire GUC state, which seems bad for several
reasons), and again we'd need to specialize for search_path.

In other words, search_path really *is* special. I don't think it's
great to generalize from it as though it were just like every other
GUC.

I do recognize that "SEARCH SYSTEM ... SET search_path = '...'" is
redundant, and that's not great. I just see the other options as worse,
but if I've misunderstood your approach then please clarify.

Regards,
Jeff Davis