search_path for PL/pgSQL functions partially cached?

[Jan Behrens]

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