On Thu, 16 Jun 2022 at 12:04, Greg Stark <st...@mit.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
