Re: search_path for PL/pgSQL functions partially cached?

On Fri, 27 Dec 2024 13:26:28 -0700
"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> > Or is it documented somewhere?
>
> https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING

I can't find any notes regarding functions and schemas in that section.

> Can someone explain to me what's going on, and what is the best practice to
> > deal with it? Is there a way to avoid fully qualifying every type and
> > expression? Which parts do I have to qualify or is this something that
> > could be fixed in a future version of PostgreSQL?
> >
>
> Add qualification or attach a “set search_path” clause to “create
> function”. Code stored in the server should not rely on the session
> search_path.
>
> David J.

In my (real world) case, I was unable to use "SET search_path FROM
CURRENT" because it isn't possible to use "SET" in procedures that use
transactions, due to this documented limitation:

"If a SET clause is attached to a procedure, then that procedure cannot
execute transaction control statements (for example, COMMIT and
ROLLBACK, depending on the language)."

https://www.postgresql.org/docs/17/sql-createprocedure.html

My procedure looks more or less like this:

CREATE PROCEDURE "myfunc"()
LANGUAGE plpgsql AS
$$
DECLARE
"old_search_path" TEXT;
-- some more variables
BEGIN
SELECT current_setting('search_path') INTO "old_search_path";
SET search_path TO 'myschema';
-- some code that uses COMMIT and SET TRANSACTION ISOLATION LEVEL
PERFORM set_config('search_path', "old_search_path", FALSE);
END;
$$;

My question is: Am I safe if I use fully-qualified types in the DECLARE
section only? Or do I need to provide full qualification also in the
code below (after SET search_path TO 'myschema')?

And bonus question: Is it documented somewhere?

Maybe not many people run into these issues because schemas and
functions aren't used as often in combination?

Kind Regards
Jan Behrens