PostgreSQL 如何打印函数调用栈信息-优快云博客

本文介绍如何在PostgreSQL中使用PL/pgSQL语言获取函数调用栈信息，并通过实例展示了如何将调用栈信息输出到日志或记录到表中，以便于调试和追踪函数调用情况。
摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >
增加了调用堆信息的输出. 可以用于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