Date: Fri, 27 Dec 2024 20:50:25 +0100
From: Jan Behrens <jbe-mlist@magnetkern.de>
To: pgsql-general@lists.postgresql.org
Subject: search_path for PL/pgSQL functions partially cached?
Message-Id: <20241227205025.1d059f72c7c08d23c9648c26@magnetkern.de>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/20241227205025.1d059f72c7c08d23c9648c26%40magnetkern.de>
Precedence: bulk

Hello,

I'm experiencing some weird issues when running the following code in a psql session:

============

CREATE TABLE "tbl" ("col" NUMERIC(15, 0));

CREATE FUNCTION "foo"() RETURNS TEXT LANGUAGE plpgsql AS $$
  BEGIN
    RETURN '2.4';
  END;
$$;

BEGIN;

CREATE SCHEMA "myschema";
SET LOCAL search_path TO 'myschema';

CREATE TABLE "tbl" ("col" NUMERIC);

CREATE FUNCTION "foo"() RETURNS TEXT LANGUAGE plpgsql AS $$
  BEGIN
    RETURN '5.4';
  END;
$$;

CREATE FUNCTION "run"() RETURNS TEXT LANGUAGE plpgsql AS $$
  DECLARE
    "variable" "tbl"."col"%TYPE;
  BEGIN
    "variable" := "foo"();
    RETURN "variable";
  END;
$$;

COMMIT;

SELECT "myschema"."run"(); -- returns '2.4' (when run in the same session)

-- reconnect to database here:
\c

SELECT "myschema"."run"(); -- returns '2'
SET search_path TO 'myschema';
SELECT "myschema"."run"(); -- returns '5'

-- reconnect to database again:
\c

SET search_path TO 'myschema';
SELECT "myschema"."run"(); -- returns '5.4'
SET search_path TO 'public';
SELECT "myschema"."run"(); -- returns '2.4' again

============

I'm using PostgreSQL verison 16.4.

Is this the expected behavior? If yes, where is this documented? If no, what would be the expected behavior?

Of course, I could fix this by fully qualifying the table name "tbl" in the function. Nonetheless, I'm not really sure what's going on here.

It seems that it matters *both* how the search_path was set during the *first* invocation of the function within a session *and* how it is set during the actual call of the function. So even if there are just two schemas involved, there are 4 possible outcomes for the "run" function's result ('2.4', '2', '5', and '5.4'). To me, this behavior seems to be somewhat dangerous. Maybe it is even considered a bug? Or is it documented somewhere? I remember running into some problems like that in the past already, but unfortunately, I don't remember details.

I suppose this is because there is some caching mechanism in place. But apparently it only caches the "tbl"."col"%TYPE and not the "foo"() function call expression. 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?

Many thanks and kind regards,
Jan Behrens