PostgreSQL 9.4 patch, Obtaining the call stack context information in 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

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