Re: search_path for PL/pgSQL functions partially cached?

On Sat, 28 Dec 2024 00:40:09 +0100
Jan Behrens <jbe-mlist(at)magnetkern(dot)de> wrote:

> > Add qualification or attach a “set search_path” clause to “create
> > function”. Code stored in the server should not rely on the session
> > search_path.
> >
> > David J.

I have been trying to adjust some of my code, and I still have cases
where I have to rely on the session's search_path. I'll provide an
example below.

>
> [...]
>
> My question is: Am I safe if I use fully-qualified types in the DECLARE
> section only? Or do I need to provide full qualification also in the
> code below (after SET search_path TO 'myschema')?
>
> And bonus question: Is it documented somewhere?
>
> [...]
>
> Kind Regards
> Jan Behrens

The following code is taken from a project I'm currently working on:

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

-- Let's assume we don't know the name of the schema in which the
-- "pgratio" extension with the RATIONAL data type is installed.
CREATE SCHEMA "qwertyuiop";
CREATE EXTENSION "pgratio" WITH SCHEMA "qwertyuiop";

-- This installs schema "myschema" with some dynamic function:
BEGIN;

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

-- Append schema of "pgratio" extension, which provides the RATIONAL
-- data type, to search_path:
SELECT set_config(
'search_path',
current_setting('search_path') || ', ' || quote_ident(nspname),
TRUE
) FROM pg_namespace, pg_extension
WHERE pg_namespace.oid = extnamespace AND extname = 'pgratio';

CREATE DOMAIN "rational_wrapper" AS RATIONAL;

CREATE FUNCTION "some_function" ("query_p" TEXT) RETURNS RATIONAL

--------------------------------------------------------------------
-- I cannot use SET search_path FROM CURRENT here, because "query_p"
-- shall refer to tables in the search_path of the caller.
--------------------------------------------------------------------

LANGUAGE plpgsql AS $$
DECLARE
"old_search_path" TEXT;

----------------------------------------------------------------
-- I have to fully qualify the following type.
-- Moreover, I can't use RATIONAL as I don't know its schema.
----------------------------------------------------------------

"result" "myschema"."rational_wrapper";
BEGIN
SELECT current_setting('search_path') INTO "old_search_path";
PERFORM set_config(
'search_path',
'myschema, ' || quote_ident(nspname) || ', pg_temp, ' ||
"old_search_path",
TRUE
) FROM pg_namespace, pg_extension
WHERE pg_namespace.oid = extnamespace AND extname = 'pgratio';

----------------------------------------------------------------
-- Is it safe to not fully qualify type RATIONAL below?
-- And, if yes, where in the documentation is this explained?
----------------------------------------------------------------

CREATE TEMP TABLE "mytemptable" ("val" RATIONAL);
EXECUTE 'INSERT INTO "mytemptable" '
'SELECT "query"."a" * "query"."b" '
'FROM (' || "query_p" || ') AS "query"';
-- Do some things here.
SELECT sum("val") INTO "result" FROM "mytemptable";
PERFORM set_config('search_path', "old_search_path", TRUE);
RETURN "result";
END;
$$;

COMMIT;

CREATE TABLE "tbl" ("foo" INT8, "bar" INT8);
INSERT INTO "tbl" VALUES (5, 7), (1, 10);

SELECT "myschema"."some_function"(
'SELECT "foo" AS "a", "bar" AS "b" FROM "tbl"'
);
\c
SELECT "myschema"."some_function"(
'SELECT "foo" AS "a", "bar" AS "b" FROM "tbl"'
);

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

The code for the pgratio extension that provides the RATIONAL data type
is found here: https://www.public-software-group.org/pgratio

Running that code on my machine correctly gives:

some_function
---------------
45
(1 row)

You are now connected to database "jbe" as user "jbe".
some_function
---------------
45
(1 row)

Because extensions can only be installed in one schema, it may be a bad
idea to have a component requiring an extension to be installed in a
particular schema (because if different components have different
expectations on the schema name, e.g. some might expect "pgratio" to be
installed in "public" and others might expect it in "pgratio" or some
other schema such as "qwertyuiop", this would lead to an unresolvable
conflict).

I would like to know if the above example is correct. It seems overall
bulky, but I haven't found a better way, assuming that it can be
unknown where a particular extension has been installed to. In
particular I feel a bit insecure about where I have to fully qualify,
and where not. See the comments in the code above.

Note that I want the function to accept a query that makes sense in the
caller's search_path. Thus using "SET search_path FROM CURRENT" is not
an option for me, I believe.

Regards,
Jan Behrens