Message-ID: <75510e9d2663573f1d32835eb6bcaf326e6a7997.camel@cybertec.at>
Subject: Re: search_path for PL/pgSQL functions partially cached?
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Jan Behrens <jbe-mlist@magnetkern.de>, Adrian Klaver
	 <adrian.klaver@aklaver.com>, Tom Lane <tgl@sss.pgh.pa.us>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>, 
 "pgsql-general@lists.postgresql.org"
	 <pgsql-general@lists.postgresql.org>
Date: Sun, 05 Jan 2025 07:48:56 +0100
In-Reply-To: <20250105001249.cdca9dd84a3061c06f936ca7@magnetkern.de>
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>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.2 (3.54.2-1.fc41) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/75510e9d2663573f1d32835eb6bcaf326e6a7997.camel%40cybertec.at>
Precedence: bulk

On Sun, 2025-01-05 at 00:12 +0100, Jan Behrens wrote:
> I constructed the following new example:
>=20
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>=20
> CREATE TABLE "tbl" ("col" NUMERIC(15, 0));
>=20
> CREATE FUNCTION "foo"() RETURNS TEXT LANGUAGE plpgsql AS $$
>   BEGIN
>     RETURN '2.4';
>   END;
> $$;
>=20
> BEGIN;
>=20
> CREATE SCHEMA "myschema";
> SET LOCAL search_path TO 'myschema';
>=20
> CREATE TABLE "tbl" ("col" NUMERIC);
>=20
> CREATE FUNCTION "foo"() RETURNS TEXT LANGUAGE plpgsql AS $$
>   BEGIN
>     RETURN '5.4';
>   END;
> $$;
>=20
> CREATE FUNCTION "run"() RETURNS TEXT LANGUAGE plpgsql AS $$
>   DECLARE
>     "old_search_path" TEXT;
>   BEGIN
>     "old_search_path" :=3D current_setting('search_path');
>     SET LOCAL search_path TO "myschema";
>     -- At this point, search_path is always set to 'myschema'!
>     DECLARE
>       "variable" "tbl"."col"%TYPE;
>     BEGIN
>       "variable" :=3D "foo"();
>       RETURN "variable";
>     END;
>     PERFORM set_config('search_path', "old_search_path", TRUE);
>   END;
> $$;
>=20
> COMMIT;
>=20
> Even if
>=20
> DECLARE "variable" "tbl"."col"%TYPE;
>=20
> follows *after* the schema is set to "myschema" in the example above, I
> still get differing results, depending on how the search_path was set
> when the function was first called.

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 =3D myschema
AS $$
  DECLARE
    "variable" "tbl"."col"%TYPE;
  BEGIN
    "variable" :=3D "foo"();
    RETURN "variable";
  END;
$$;

Yours,
Laurenz Albe