public inbox for [email protected]  

help / color / mirror / Atom feed

From: Jan Behrens <[email protected]>
To: [email protected]
Subject: search_path for PL/pgSQL functions partially cached?
Date: Fri, 27 Dec 2024 20:50:25 +0100
Message-ID: <[email protected]> (raw)

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: search_path for PL/pgSQL functions partially cached?
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox