Date: Sun, 5 Jan 2025 11:43:21 +0100
From: Jan Behrens <jbe-mlist@magnetkern.de>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>, Tom Lane <tgl@sss.pgh.pa.us>,
 "David G. Johnston" <david.g.johnston@gmail.com>,
 "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: Re: search_path for PL/pgSQL functions partially cached?
Message-Id: <20250105114321.bbe1fdad3f888db9383c0e28@magnetkern.de>
In-Reply-To: <75510e9d2663573f1d32835eb6bcaf326e6a7997.camel@cybertec.at>
References: <20241227205025.1d059f72c7c08d23c9648c26@magnetkern.de>
	<CAKFQuwb4hgHH=Z6cx5Hh_qc10TCYMb1QVfP3099X1Psmyw0r3Q@mail.gmail.com>
	<20241228004009.267f21b78394c934f27f9974@magnetkern.de>
	<20250103135332.7910a008714abaa04d32e5e2@magnetkern.de>
	<CAKFQuwaU19_6HaB+9-L-fQhjUr8_5ACvxLAPRBhEdfLv9JVZBg@mail.gmail.com>
	<20250103174849.f071d9c6942f76a9b812157c@magnetkern.de>
	<CAKFQuwZdt+YLi=9_WraRLajuOkmw4esFzbHTXmv5MwHJemdDhQ@mail.gmail.com>
	<20250103223312.dde69ae482776e4f1b0c2258@magnetkern.de>
	<14e035fb-ab35-4a73-a637-ad0180067b07@aklaver.com>
	<20250104002203.2aed83d48d795ce6fbbd3b61@magnetkern.de>
	<e9d0d4d0-9d92-4b58-aa19-f8140cd4b10f@aklaver.com>
	<20250105001249.cdca9dd84a3061c06f936ca7@magnetkern.de>
	<75510e9d2663573f1d32835eb6bcaf326e6a7997.camel@cybertec.at>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/20250105114321.bbe1fdad3f888db9383c0e28%40magnetkern.de>
Precedence: bulk

On Sun, 05 Jan 2025 07:48:56 +0100
Laurenz Albe <laurenz.albe@cybertec.at> wrote:

> So what you should do is set the "search_path" *on* the function, not *in*
> the function:
> 
> CREATE FUNCTION "run"() RETURNS TEXT LANGUAGE plpgsql
> SET search_path = myschema
> AS $$
>   DECLARE
>     "variable" "tbl"."col"%TYPE;
>   BEGIN
>     "variable" := "foo"();
>     RETURN "variable";
>   END;
> $$;
> 
> Yours,
> Laurenz Albe

Yes, that's what works and what I would also do whenever possible
(probably in the form "SET search_path FROM CURRENT").

Summarizing the remaining thread, some issues are:

* The documentation isn't providing a prominent warning that behavior
  can be surprising if "SET search_path" is not used in the function's
  or procedure's defintion. (E.g. searching for "schema" in the
  documentation page for "CREATE FUNCTION" doesn't give any helpful
  hints or warning.)

* Things get more complicated when it's impossible to use
  "SET search_path" in the function's/procedure's definition, for which
  there are two scenarios:

  Scenario 1: The function or procedure needs or wants to access or use
              the search_path of the caller.

  Scenario 2: A procedure wants to execute transactional statements
              such as COMMIT or ROLLBACK within its body.

  In scenario 1, using "SET search_path" will overwrite the caller's
  search_path at runtime, so I cannot access it. (In my post from Sat,
  4 Jan 2025 14:23:10 +0100, I have proposed a wrapper function to work
  around that.)

  In scenario 2, using "SET search_path" is simply not possible and
  will be rejected by PostgreSQL.

* It is a bit unclear how the exact behavior is when I set a
  search_path from within the functions body (e.g. due to one of the two
  scenarios above). There are some examples that show some quite
  surprising behavior, at least if you don't fully understand the
  plan caching mechanism that is used.

Kind regards,
Jan Behrens