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

On Mon, 2023-09-25 at 11:30 -0400, Robert Haas wrote:
> On Fri, Sep 22, 2023 at 4:05 PM Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> > You expect
> > Bob to do something like:
> >
> >   CREATE FUNCTION ... SET search_path = pg_catalog, pg_temp ...
> >
> > for all functions, not just SECURITY DEFINER functions, is that
> > right?
>
> Yes, I do.

Do users like Bob do that today? If not, what causes you to expect them
to do so in the future?

> I think it's self-evident that a SQL function's behavior is
> not guaranteed to be invariant under all possible values of
> search_path.

It's certainly not self-evident in a literal sense. I think you mean
that it's "obvious" or something, and perhaps that narrow question is,
but it's also not terribly helpful.

If the important behaviors here were so obvious, how did we end up in
this mess in the first place?

> > We've already established that even postgres hackers are having
> > difficulty keeping up with these nuances. Even though the SET
> > clause
> > has been there for a long time, our documentation on the subject is
> > insufficient and misleading. And on top of that, it's extra typing
> > and
> > noise for every schema file. Until we make some changes I don't
> > think
> > we can expect users to do as you suggest.
>
> Respectfully, I find this position unreasonable, to the point of
> finding it difficult to take seriously.

Which part exactly is unreasonable?

* Hackers are having trouble keeping up with the nuances.
* Our documentation on the subject *is* insufficient and misleading.
* "pg_temp" is noise.

It seems like you think that users are already doing "SET search_path =
pg_catalog, pg_temp" in all the necessary places, and therefore no
change is required?

> Most of the problems that we're dealing with here have analogues in
> the world of shell scripts.

I think analogies to unix are what caused a lot of the problems we have
today, because postgres is a very different world. In unix-like
environments, a file is just a file; in postgres, we have all kinds of
code attached in interesting ways.

Regards,
Jeff Davis