Debug Postgresql

查询客户端连接上的服务进程id

postgres=# select pg_backend_pid();
 pg_backend_pid
----------------
          34255
(1 row)

postgres=# select 1;

开启debug日志

查找配置文件路径

# psql -d postgres -c "SHOW config_file;"
                   config_file
-------------------------------------------------
 /Users/user/local/postgres-data/postgresql.conf
(1 row)

postgresql.conf

# log_min_messages = warning
log_min_messages = debug5

#debug_print_parse = off
debug_print_parse = on

#debug_print_rewritten = off
debug_print_rewritten = on

#debug_print_plan = off
debug_print_plan = on

#debug_pretty_print = on
debug_pretty_print = on

pg_rewrite_query()
{

    // debug_print_parse = on
    if (Debug_print_parse)
        elog_node_display(LOG, "parse tree", query,
                          Debug_pretty_print);

    // debug_print_rewritten = on
    if (Debug_print_rewritten)
        elog_node_display(LOG, "rewritten parse tree", querytree_list,
                          Debug_pretty_print);

}

pg_plan_query(){
    // debug_print_plan = on
    if (Debug_print_plan)
        elog_node_display(LOG, "plan", plan, Debug_pretty_print);
}

GDB

(gdb) call elog_node_display(17, "what ever", Node * var, 0 or 1)

• 17代表INFO，详见elog.h，比如希望打印在日志里，将17替换为16，
• 0或1取决于debug_pretty_print为on还是off

pgNodeGraph

将打印信息图形化工具：

https://github.com/shenyuflying/pgNodeGraph

https://github.com/japinli/pg_node2graph

依赖 graphviz

brew install graphviz

例如：

select 1;

说明：

• 第一个图基于pgNodeGraph
• 第二个图基于pg_node2graph

parsetree_list

1、parse tree

2、rewritten

3、plan

ref

PostgreSQL数据库查询——exec_simple_query函数分析

https://www.cnblogs.com/feishujun/p/PostgreSQLSourceAnalysis_query001.html

调试代码

根据错误直接定位代码

postgres=# select * from tb1;
ERROR:  relation "tb1" does not exist
LINE 1: select * from tb1;
                      ^
postgres=# \errverbose
ERROR:  42P01: relation "tb1" does not exist
LINE 1: select * from tb1;
                      ^
LOCATION:  parserOpenTable, parse_relation.c:1452

查看日志

打印日志

ereport(LOG, (errmsg("PgStartTime: %ld", PgStartTime)));

通过进程号过滤日志

tail -f postgresql.log | grep '\[35004\]'

makefile

.PHONY: start
start:
    pg_ctl -D /Users/wang/local/postgres-data -l logfile start

.PHONY: stop
stop:
    pg_ctl -D /Users/wang/local/postgres-data -l logfile stop

.PHONY: restart
restart:
    make stop && make start

.PHONY: build
build:
    ./configure --prefix=/Users/wang/local/postgres --enable-debug --enable-cassert --enable-depend CFLAGS=-O0 && make && make install

.PHONY: rebuild
make:
    make && make install

.PHONY: reload
reload:
    make stop; make rebuild && make start

VSCode 配置

.vscode/launch.json

{
    // Use IntelliSense to learn about possible attributes.
    // Hover to view descriptions of existing attributes.
    // For more information, visit: https://go.microsoft.com/fwlink/?linkid=830387
    "version": "0.2.0",
    "configurations": [
        {
            "name": "(gdb) Launch",
            "type": "cppdbg",
            "request": "launch",
            "program": "/Users/wang/local/postgres/bin/postgres",
            "args": [
                "-D",
                "/Users/wang/local/postgres-data"
            ],
            "stopAtEntry": true,
            "cwd": "${fileDirname}",
            "environment": [],
            "externalConsole": false,
            "MIMode": "gdb",
            "setupCommands": [
                {
                    "description": "Enable pretty-printing for gdb",
                    "text": "-enable-pretty-printing",
                    "ignoreFailures": true
                },
                {
                    "description": "Set Disassembly Flavor to Intel",
                    "text": "-gdb-set disassembly-flavor intel",
                    "ignoreFailures": true
                },
                {
                    "description": "gdb setting", // 屏蔽信号输出
                    "text": "handle SIGUSR1 SIGUSR2 nostop noprint",
                    "ignoreFailures": true
                }
            ]
        },
        {
            "name": "(gdb) Attach",
            "type": "cppdbg",
            "request": "attach",
            "program": "/Users/wang/local/postgres/bin/postgres",
            "MIMode": "gdb",
            "setupCommands": [
                {
                    "description": "Enable pretty-printing for gdb",
                    "text": "-enable-pretty-printing",
                    "ignoreFailures": true
                },
                {
                    "description": "Set Disassembly Flavor to Intel",
                    "text": "-gdb-set disassembly-flavor intel",
                    "ignoreFailures": true
                },
                {
                    "description": "gdb setting", // 屏蔽信号输出
                    "text": "handle SIGUSR1 SIGUSR2 nostop noprint",
                    "ignoreFailures": true
                }
            ]
        },
        {
            "name": "(lldb) Launch",
            "type": "cppdbg",
            "request": "launch",
            "program": "/Users/wang/local/postgres/bin/postgres",
            "args": [
                "-D",
                "/Users/wang/local/postgres-data"
            ],
            "stopAtEntry": true,
            "cwd": "${fileDirname}",
            "environment": [],
            "externalConsole": false,
            "MIMode": "lldb"
        },
        {
            "name": "(lldb) Attach",
            "type": "cppdbg",
            "request": "attach",
            "program": "/Users/wang/local/postgres/bin/postgres",
            "MIMode": "lldb",
            "setupCommands": [
                {
                    "description": "Enable pretty-printing for gdb",
                    "text": "-enable-pretty-printing",
                    "ignoreFailures": true
                },
                {
                    "description": "Set Disassembly Flavor to Intel",
                    "text": "-gdb-set disassembly-flavor intel",
                    "ignoreFailures": true
                },
                {
                    "description": "gdb setting", // 屏蔽信号输出
                    "text": "handle SIGUSR1 SIGUSR2 nostop noprint",
                    "ignoreFailures": true
                }
            ]
        }
    ]
}