PostgreSQL Tutorial: Logging backtrace for errors in functions

Summary: in this tutorial, you will learn how to log backtrace for errors in specified functions.

Table of Contents

Introduction to PostgreSQL backtrace_functions

The backtrace_functions option is an option intended for developer use, which was introduced in PostgreSQL 13. backtrace_functions is a configuration parameter for specifying a list of internal C functions for which to generate backtraces when the function calls ereport() or elog().

You can specify a list of internal C function names separated by comma, if an error is raised and matches any C function in the given list, then the backtrace will be logged into the server logs. This is very useful for debugging some specific areas of the source code, especially when the error happens randomly. As the document also mentioned, this option is not available on all platforms, and quality of the backtraces depends on the compilation options.

PostgreSQL backtrace_functions example

Let’s see how a PostgreSQL stack trace log looks like. We will try to use a non-existent type to create a table. Supposing we know that the “type does not exist” error comes from typenameType() in the source code. So we do this:

SET backtrace_functions = 'typenameType';

CREATE TABLE tab (id invalidtype);
ERROR:  type "invalidtype" does not exist
LINE 1: CREATE TABLE tab (id invalidtype);

The above statement raised an error, and here’s a snippet from the server log:

2023-07-28 20:17:01.482 UTC [25220] ERROR:  type "invalidtype" does not exist at character 22
2023-07-28 20:17:01.482 UTC [25220] BACKTRACE:
    postgres: redrock postgres [local] CREATE TABLE(typenameType+0xa4) [0xaaaaafcd2ac4]
    postgres: redrock postgres [local] CREATE TABLE(+0x20f550) [0xaaaaafcd4550]
    postgres: redrock postgres [local] CREATE TABLE(transformCreateStmt+0x53c) [0xaaaaafcd7a10]
    postgres: redrock postgres [local] CREATE TABLE(+0x44df20) [0xaaaaaff12f20]
    postgres: redrock postgres [local] CREATE TABLE(standard_ProcessUtility+0x16c) [0xaaaaaff1225c]
    postgres: redrock postgres [local] CREATE TABLE(+0x44a4e4) [0xaaaaaff0f4e4]
    postgres: redrock postgres [local] CREATE TABLE(+0x44af88) [0xaaaaaff0ff88]
    postgres: redrock postgres [local] CREATE TABLE(PortalRun+0x198) [0xaaaaaff10ed8]
    postgres: redrock postgres [local] CREATE TABLE(+0x44764c) [0xaaaaaff0c64c]
    postgres: redrock postgres [local] CREATE TABLE(PostgresMain+0x970) [0xaaaaaff0d3d4]
    postgres: redrock postgres [local] CREATE TABLE(+0x3b3be4) [0xaaaaafe78be4]
    postgres: redrock postgres [local] CREATE TABLE(PostmasterMain+0xdc0) [0xaaaaafe79b70]
    postgres: redrock postgres [local] CREATE TABLE(main+0x480) [0xaaaaafb82510]
    /lib/aarch64-linux-gnu/libc.so.6(__libc_start_main+0xe0) [0xffffaac956e0]
    postgres: redrock postgres [local] CREATE TABLE(+0xbd5d8) [0xaaaaafb825d8]
2023-07-28 20:17:01.482 UTC [25220] STATEMENT:  CREATE TABLE tab (id invalidtype);

Each line of the backtrace has the function name, an offset into that function, and the return address of that frame.

For some stack frames, the function name is not present; instead, the function address is present. These are static functions. For such functions, the function names are not exposed. But we may be able to get their names from their addresses, with the help of addr2line command-line tool:

$ addr2line 0x20f550 -f -e `which postgres`
transformColumnDefinition
parse_utilcmd.c:?

Here, the option -f displays the function names as well as source file and line number, -e used to specify the name of the executable for which addresses should be translated. In the output, the line number doesn’t show up. To get the line number and file name, you can add the option -ggdb to the compilation parameter CFLAGS when building PostgreSQL.

Summary

This tutorial discussed one very useful feature for developers, introduced in PostgreSQL 13. It is really handy to help us quickly locate the cause of errors, especially for some random issues.