Re: BUG #18767: Inconsistency in result of a Plpgsql GET DIAGNOSTICS PG_CONTEXT instruction.

Thanks a lot Pavel and Tom for your reply.

Le 06/01/2025 à 17:24, Pavel Stehule a écrit :
> Hi
>
> It is not a bug. GET DIAGNOSTICS PG_CONTEXT returns error context
>
> Error context is generated by the function
> plpgsql_exec_error_callback. This function uses
> estate->func->fn_signature string
> and this string is generated by function format_procedure. This
> function hides schema when function is visible from current search_path
>
> (2025-01-06 17:01:05) postgres=# create schema test;
> CREATE SCHEMA
> (2025-01-06 17:01:26) postgres=# create function public.fx(a int)
> returns void as $$ begin end $$ language plpgsql;
> CREATE FUNCTION
> (2025-01-06 17:01:47) postgres=# create function test.fx(a int)
> returns void as $$ begin end $$ language plpgsql;
> CREATE FUNCTION
>
> (2025-01-06 17:02:16) postgres=# select 'test.fx'::regproc;
> ┌─────────┐
> │ regproc │
> ╞═════════╡
> │ test.fx │
> └─────────┘
> (1 row)
>
> (2025-01-06 17:02:22) postgres=# select 'test.fx'::regproc::regprocedure;
> ┌──────────────────┐
> │   regprocedure   │
> ╞══════════════════╡
> │ test.fx(integer) │
> └──────────────────┘
> (1 row)
>
> (2025-01-06 17:02:27) postgres=# select
> 'public.fx'::regproc::regprocedure;
> ┌──────────────┐
> │ regprocedure │
> ╞══════════════╡
> │ fx(integer)  │
> └──────────────┘
> (1 row)
>
> This is mostly used for displaying functions in error messages.
>
> Unfortunately it is not possible to change it without a compatibility
> break. This was designed more than 20 years ago.

I aggree to not break the compatibility. All the more that there are
probably good security arguments for the current behaviour.

But there is something not clear to me.

All sub-function calls use the schema-qualified function names. When the
functions have been created with a search_path set to tst, I get :

select 'tst.caller1'::regproc::regprocedure;
 regprocedure
--------------
 caller1()
(1 row)

select 'tst.called'::regproc::regprocedure;
 regprocedure
--------------
 called()
(1 row)

select 'caller1'::regproc::regprocedure;
 regprocedure
--------------
 caller1()
(1 row)

select 'called'::regproc::regprocedure;
 regprocedure
--------------
 called()
(1 row)

This totaly corresponds to the behaviour your explained : the tst schema
is into the path, so it is not returned.

Then when the search_path is reset, I get :

show search_path;
   search_path
-----------------
 "$user", public
(1 row)

select 'tst.caller1'::regproc::regprocedure;

 regprocedure
---------------
 tst.caller1()
(1 row)

select 'tst.called'::regproc::regprocedure;
 regprocedure
--------------
 tst.called()
(1 row)

... which is also expected. But then (and keeping the same search_path),
the stack in the called() function looks like exactly as when the
search_path is set to tst:

select tst.caller1() as stack_in_called;
psql:test_stack.sql:39: WARNING:  Beware: the calling function is not
the expected one!
                     stack_in_called
----------------------------------------------------------
 PL/pgSQL function tst.called() line 5 at GET DIAGNOSTICS+
 PL/pgSQL function caller2() line 1 at RETURN            +
 PL/pgSQL function caller1() line 1 at RETURN
(1 row)

To get all schema qualified function calls in the returned stack, the
functions must have been *created* without the schema in the path. So it
looks to me that what matter is the search_path sometimes at the
functions call times and sometimes at the functions creation time. That
is the unconsistency I wrote about. But I probably miss something.

>
> If you want to track function calls exactly you need own extension
> based on pl debug api like https://github.com/EnterpriseDB/pldebugger

OK. Thanks for the pointer.

I will probably change the functions architecture, by moving back the
sensitive pieces of code into calling functions and set them SECURITY
DEFINER. I am not very fan of having more SECURITY DEFINER functions,
but I don't see other not too complex solution.

Regards. Philippe.

>
>
> Regards
>
> Pavel
>
>
>
>