PostgreSQL 9.4 新增plpgsql补丁, 该补丁增加了调用堆信息的输出. 可以用于plpgsql debug等.
测试 :
以下SQL :
将call stack的信息打印出来如下 :
对于一些敏感函数, 如果要跟踪被调用或者间接调用的情况, 可以把stack的信息输出到表中 例如.
[参考]
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)[参考]
