Re: BUG #18767: Inconsistency in result of a Plpgsql GET DIAGNOSTICS PG_CONTEXT instruction. - Mailing list pgsql-bugs

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