search_path for PL/pgSQL functions partially cached? - Mailing list pgsql-general

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'sgoing 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,
thereare 4 possible outcomes for the "run" function's result ('2.4', '2', '5', and '5.4'). To me, this behavior seems
tobe somewhat dangerous. Maybe it is even considered a bug? Or is it documented somewhere? I remember running into some
problemslike 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
andnot the "foo"() function call expression. Can someone explain to me what's going on, and what is the best practice
todeal with it? Is there a way to avoid fully qualifying every type and expression? Which parts do I have to qualify or
isthis something that could be fixed in a future version of PostgreSQL?
 

Many thanks and kind regards,
Jan Behrens