PostgreSQL 打印详细错误调用栈 - pg_backtrace-阿里云开发者社区

开发者社区> 阿里云数据库> 正文

PostgreSQL 打印详细错误调用栈 - pg_backtrace

简介: PostgreSQL 打印详细错误调用栈 - pg_backtrace

背景

PostgreSQL 使用backtrace,让PG的user process支持self-debugging。

NAME
       backtrace, backtrace_symbols, backtrace_symbols_fd - support for application self-debugging

SYNOPSIS
       #include <execinfo.h>

       int backtrace(void **buffer, int size);

       char **backtrace_symbols(void *const *buffer, int size);

       void backtrace_symbols_fd(void *const *buffer, int size, int fd);

支持:

1、打印错误SQL的调用栈内容。

2、了解LONG QUERY正在执行什么,慢在什么地方。 通过发送信号 (SIGINT)。

3、向日志中输出CORE的信息,打印调用栈信息,通过发送信号 (SIGSEGV or SIGBUS)。

This PostgreSQL extension can be used to get more information
about PostgreSQL backend execution when no debugger is available.
It allows to dump stack trace when error is reported or exception is caught.
So there three used cases of using this extension:

1. Find out the source of error. pg_backtrace extension provides
"pg_backtrace.level" GUC which selects error level for which backtrace information
is attached. Default value is ERROR. So once this extension is initialized,
all errors will include backtrace information which is dumped both in log
file and delivered to the client:

postgres=# select count(*)/0.0 from pg_class;  
ERROR:  division by zero  
CONTEXT:      postgres: knizhnik postgres [local] SELECT(numeric_div+0xbc) [0x7c5ebc]  
    postgres: knizhnik postgres [local] SELECT() [0x5fe4e2]  
    postgres: knizhnik postgres [local] SELECT() [0x610730]  
    postgres: knizhnik postgres [local] SELECT() [0x6115ca]  
    postgres: knizhnik postgres [local] SELECT(standard_ExecutorRun+0x15a) [0x60193a]  
    postgres: knizhnik postgres [local] SELECT() [0x74168c]  
    postgres: knizhnik postgres [local] SELECT(PortalRun+0x29e) [0x742a7e]  
    postgres: knizhnik postgres [local] SELECT() [0x73e922]  
    postgres: knizhnik postgres [local] SELECT(PostgresMain+0x1189) [0x73fde9]  
    postgres: knizhnik postgres [local] SELECT() [0x47d5e0]  
    postgres: knizhnik postgres [local] SELECT(PostmasterMain+0xd28) [0x6d0448]  
    postgres: knizhnik postgres [local] SELECT(main+0x421) [0x47e511]  
    /lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0xf0) [0x7f6361a13830]  
    postgres: knizhnik postgres [local] SELECT(_start+0x29) [0x47e589]  

2. Determine current state of backend (assume that there is some long running query
and you do not know where it spends most of time). It is possible to send SIGINT signal
to backend and it print current stack in logfile:

2018-11-12 18:24:12.222 MSK [24457] LOG:  Caught signal 2  
2018-11-12 18:24:12.222 MSK [24457] CONTEXT:      /lib/x86_64-linux-gnu/libpthread.so.0(+0x11390) [0x7f63624e3390]  
        /lib/x86_64-linux-gnu/libc.so.6(epoll_wait+0x13) [0x7f6361afa9f3]  
        postgres: knizhnik postgres [local] SELECT(WaitEventSetWait+0xbe) [0x71e4de]  
        postgres: knizhnik postgres [local] SELECT(WaitLatchOrSocket+0x8b) [0x71e93b]  
        postgres: knizhnik postgres [local] SELECT(pg_sleep+0x98) [0x7babd8]  
        postgres: knizhnik postgres [local] SELECT() [0x5fe4e2]  
        postgres: knizhnik postgres [local] SELECT() [0x6266a8]  
        postgres: knizhnik postgres [local] SELECT(standard_ExecutorRun+0x15a) [0x60193a]  
        postgres: knizhnik postgres [local] SELECT() [0x74168c]  
        postgres: knizhnik postgres [local] SELECT(PortalRun+0x29e) [0x742a7e]  
        postgres: knizhnik postgres [local] SELECT() [0x73e922]  
        postgres: knizhnik postgres [local] SELECT(PostgresMain+0x1189) [0x73fde9]  
        postgres: knizhnik postgres [local] SELECT() [0x47d5e0]  
        postgres: knizhnik postgres [local] SELECT(PostmasterMain+0xd28) [0x6d0448]  
        postgres: knizhnik postgres [local] SELECT(main+0x421) [0x47e511]  
        /lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0xf0) [0x7f6361a13830]  
        postgres: knizhnik postgres [local] SELECT(_start+0x29) [0x47e589]  

3. Get stack trace for SIGSEGV or SIGBUS signals
(if dumping cores is disabled for some reasons):

2018-11-12 18:25:52.636 MSK [24518] LOG:  Caught signal 11  
2018-11-12 18:25:52.636 MSK [24518] CONTEXT:      /home/knizhnik/postgresql/dist/lib/pg_backtrace.so(+0xe37) [0x7f6358838e37]  
        /lib/x86_64-linux-gnu/libpthread.so.0(+0x11390) [0x7f63624e3390]  
        /home/knizhnik/postgresql/dist/lib/pg_backtrace.so(pg_backtrace_sigsegv+0) [0x7f6358838fb0]  
        postgres: knizhnik postgres [local] SELECT() [0x5fe474]  
        postgres: knizhnik postgres [local] SELECT() [0x6266a8]  
        postgres: knizhnik postgres [local] SELECT(standard_ExecutorRun+0x15a) [0x60193a]  
        postgres: knizhnik postgres [local] SELECT() [0x74168c]  
        postgres: knizhnik postgres [local] SELECT(PortalRun+0x29e) [0x742a7e]  
        postgres: knizhnik postgres [local] SELECT() [0x73e922]  
        postgres: knizhnik postgres [local] SELECT(PostgresMain+0x1189) [0x73fde9]  
        postgres: knizhnik postgres [local] SELECT() [0x47d5e0]  
        postgres: knizhnik postgres [local] SELECT(PostmasterMain+0xd28) [0x6d0448]  
        postgres: knizhnik postgres [local] SELECT(main+0x421) [0x47e511]  
        /lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0xf0) [0x7f6361a13830]  
        postgres: knizhnik postgres [local] SELECT(_start+0x29) [0x47e589]  

As far as Postgres extension is loaded and initialized on first access to
its functions, it is necessary to call pg_backtrace_init() function to
be able to use this extension. This function function actually does nothing
and _PG_init() registers signal handlers for SIGSEGV, SIGBUS and SIGINT and
executor run hook which setups exception context.

This extension is using backtrace function which is available at most Unixes.
As it was mentioned in backtrace documentation:

The symbol names may be unavailable without the use of special linker options.  
For systems using the GNU linker, it is necessary to use the -rdynamic  
linker option.  Note that names of "static" functions are not exposed,  
and won't be available in the backtrace.  

Postgres is built without -rdynamic option. This is why not all function addresses
in the stack trace above are resolved. It is possible to use GDB (at development
host with correspondent postgres binaries) or Linux addr2line utility to
get resolve function addresses:

    $ addr2line -e ~/postgresql/dist/bin/postgres -a 0x5fe4e2  
    0x00000000005fe4e2  
    execExprInterp.c:?  

用法

Usage:

create extension pg_backtrace;  
select pg_backtrace_init();  

参考

pg_backtrace

https://github.com/postgrespro/pg_backtrace

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议,奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等,奖品丰富,快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
阿里云数据库
使用钉钉扫一扫加入圈子
+ 订阅

帮用户承担一切数据库风险,给您何止是安心!

官方博客
链接