Date: Sat, 28 Dec 2024 00:40:09 +0100
From: Jan Behrens <jbe-mlist@magnetkern.de>
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: "pgsql-general@lists.postgresql.org"
 <pgsql-general@lists.postgresql.org>
Subject: Re: search_path for PL/pgSQL functions partially cached?
Message-Id: <20241228004009.267f21b78394c934f27f9974@magnetkern.de>
In-Reply-To: <CAKFQuwb4hgHH=Z6cx5Hh_qc10TCYMb1QVfP3099X1Psmyw0r3Q@mail.gmail.com>
References: <20241227205025.1d059f72c7c08d23c9648c26@magnetkern.de>
	<CAKFQuwb4hgHH=Z6cx5Hh_qc10TCYMb1QVfP3099X1Psmyw0r3Q@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/20241228004009.267f21b78394c934f27f9974%40magnetkern.de>
Precedence: bulk

On Fri, 27 Dec 2024 13:26:28 -0700
"David G. Johnston" <david.g.johnston@gmail.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