Re: pgsql: Fix search_path to a safe value during maintenance operations.

On Tue, Aug 1, 2023 at 10:42 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> Now, if we don't go in the direction of resolving everything at parse
> time, then I think capturing search_path is probably the next best
> thing, or at least the next best thing that I've thought up so far.

I'd much rather strongly encourage the user to write a safe and
self-sufficient function definition. Specifically, if there is no
search_path attached to the function then the search path that will be in
place will be temp + pg_catalog only. Though I wonder whether it would be
advantageous to allow a function to have a temporary schema separate from
the session-scoped one...

They can use ALTER FUNCTION and the existing "FROM CURRENT" specification
to get back to current behavior if desired.

Going further, I could envision an "explicit" mode that both performs a
parse-time check for object existence and optionally reports an error if
the lookup happened via an inexact match - forcing lots more type casts to
occur (we'd probably need to invent something to say "must match the
anyelement function signature") but ensuring at parse time you've correctly
identified everything you intend to be using. Sure, the meanings of those
things could change but the surface is much smaller than plugging a new
function that matches earlier in the lookup resolution process.

David J.