PostgreSQL Oracle 兼容性之 - pl/sql dbms_utility.format_error_backtrace - 如何打印plpgsql函数调用栈信息

背景

PostgreSQL 9.4 新增plpgsql补丁, 该补丁增加了调用堆信息的输出. 可以用于plpgsql debug等.

测试 :

pg94@db-192-168-100-216-> psql  
psql (9.4devel)  
Type "help" for help.  
  
digoal=# -- access to call stack  
digoal=# create or replace function inner_func(int)  
digoal-# returns int as $$  
digoal$# declare _context text;  
digoal$# begin  
digoal$#   get diagnostics _context = pg_context;  
digoal$#   raise notice '***%***', _context;  
digoal$#   return 2 * $1;  
digoal$# end;  
digoal$# $$ language plpgsql;  
CREATE FUNCTION  
digoal=#   
digoal=# create or replace function outer_func(int)  
digoal-# returns int as $$  
digoal$# begin  
digoal$#   return inner_func($1);  
digoal$# end;  
digoal$# $$ language plpgsql;  
CREATE FUNCTION  
digoal=#   
digoal=# create or replace function outer_outer_func(int)  
digoal-# returns int as $$  
digoal$# begin  
digoal$#   return outer_func($1);  
digoal$# end;  
digoal$# $$ language plpgsql;  
CREATE FUNCTION  
digoal=#   
digoal=# select outer_outer_func(10);  
NOTICE:  ***PL/pgSQL function inner_func(integer) line 4 at GET DIAGNOSTICS  
PL/pgSQL function outer_func(integer) line 3 at RETURN  
PL/pgSQL function outer_outer_func(integer) line 3 at RETURN***  
CONTEXT:  PL/pgSQL function outer_func(integer) line 3 at RETURN  
PL/pgSQL function outer_outer_func(integer) line 3 at RETURN  
 outer_outer_func   
------------------  
               20  
(1 row)  

以下SQL :

digoal$#   get diagnostics _context = pg_context;  
digoal$#   raise notice '***%***', _context;  

将call stack的信息打印出来如下 :

NOTICE:  ***PL/pgSQL function inner_func(integer) line 4 at GET DIAGNOSTICS  
PL/pgSQL function outer_func(integer) line 3 at RETURN  
PL/pgSQL function outer_outer_func(integer) line 3 at RETURN***  
CONTEXT:  PL/pgSQL function outer_func(integer) line 3 at RETURN  
PL/pgSQL function outer_outer_func(integer) line 3 at RETURN  

对于一些敏感函数, 如果要跟踪被调用或者间接调用的情况, 可以把stack的信息输出到表中 例如.

digoal=#  create table if not exists rec_inner_func_called (id serial8 primary key, info text, crt_time timestamp default clock_timestamp());    
digoal=#   
create or replace function inner_func(int)  
returns int as $$  
declare _context text;  
begin  
  get diagnostics _context = pg_context;  
  insert into rec_inner_func_called(info) values (_context);                
  return 2 * $1;  
end;  
$$ language plpgsql;  
CREATE FUNCTION  
  
digoal=# select outer_outer_func(10);  
 outer_outer_func   
------------------  
               20  
(1 row)  
  
digoal=# select * from rec_inner_func_called;  
 id |                              info                               |          crt_time            
----+-----------------------------------------------------------------+----------------------------  
  1 | PL/pgSQL function inner_func(integer) line 4 at GET DIAGNOSTICS+| 2013-07-26 09:19:32.588016  
    | PL/pgSQL function outer_func(integer) line 3 at RETURN         +|   
    | PL/pgSQL function outer_outer_func(integer) line 3 at RETURN    |   
(1 row)  
digoal=# select outer_outer_func(100);  
 outer_outer_func   
------------------  
              200  
(1 row)  
  
digoal=# select outer_outer_func(100);  
 outer_outer_func   
------------------  
              200  
(1 row)  
  
digoal=#   
digoal=#   
digoal=# select * from rec_inner_func_called;  
 id |                              info                               |          crt_time            
----+-----------------------------------------------------------------+----------------------------  
  1 | PL/pgSQL function inner_func(integer) line 4 at GET DIAGNOSTICS+| 2013-07-26 09:19:32.588016  
    | PL/pgSQL function outer_func(integer) line 3 at RETURN         +|   
    | PL/pgSQL function outer_outer_func(integer) line 3 at RETURN    |   
  2 | PL/pgSQL function inner_func(integer) line 4 at GET DIAGNOSTICS+| 2013-07-26 09:19:46.031669  
    | PL/pgSQL function outer_func(integer) line 3 at RETURN         +|   
    | PL/pgSQL function outer_outer_func(integer) line 3 at RETURN    |   
  3 | PL/pgSQL function inner_func(integer) line 4 at GET DIAGNOSTICS+| 2013-07-26 09:20:30.25935  
    | PL/pgSQL function outer_func(integer) line 3 at RETURN         +|   
    | PL/pgSQL function outer_outer_func(integer) line 3 at RETURN    |   
  4 | PL/pgSQL function inner_func(integer) line 4 at GET DIAGNOSTICS+| 2013-07-26 09:20:32.665713  
    | PL/pgSQL function outer_func(integer) line 3 at RETURN         +|   
    | PL/pgSQL function outer_outer_func(integer) line 3 at RETURN    |   
(4 rows)  

参考

1. http://www.postgresql.org/docs/devel/static/plpgsql-control-structures.html#PLPGSQL-DIAGNOSTICS

https://www.postgresql.org/docs/11/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

2. http://www.postgresql.org/message-id/flat/CAFj8pRChsM1zshEFi0Sy6_VHQpWQ6gr0o6d3qvt9tGG9ovnNNA@mail.gmail.com#CAFj8pRChsM1zshEFi0Sy6_VHQpWQ6gr0o6d3qvt9tGG9ovnNNA@mail.gmail.com

3. http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=831283256796d1c20858862b568d73e505eb4a84

4. https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_util.htm#i1003874

digoal’s 大量PostgreSQL文章入口