Re: Tightening behaviour for non-immutable behaviour in immutable functions

On Thu, 16 Jun 2022 at 12:04, Greg Stark <stark(at)mit(dot)edu> wrote:
>
> Providing a fixed up search_path for users would give them a smoother
> upgrade process where we can give a warning only if the search_path is
> changed substantively which is much less likely.
>
> I'm still quite concerned about the performance impact of having
> search_path on so many functions. It causes a cache flush which could
> be pretty painful on a frequently called function such as one in an
> index expression during a data load....

So it seems I missed a small change in Postgres SQL function world,
namely the SQL standard syntax and prosqlbody column from e717a9a18.

This feature is huge. It's awesome! It basically provides the lexical
scoping feature I was hoping to implement. Any sql immutable standard
syntax sql function can be safely used in indexes or elsewhere
regardless of your search_path as all the names are already resolved.

I'm now thinking we should just provide a LEXICAL option on Postgres
style functions to implement the same name path and store sqlbody for
them as well. They would have to be bound by the same restrictions
(notably no polymorphic parameters) but otherwise I think it should be
straightforward.

Functions defined this way would always be safe for pg_dump regardless
of the search_path used to define them and would also protect users
from accidentally corrupting indexes when users have different
search_paths.

This doesn't really address plpgsql functions of course, I doubt we
can do the same thing.

--
greg