Stack traces for Postgres errors with backtrace_functions

If Postgres crashes you can get a stack trace with gdb. But how do you debug errors that don't crash Postgres?

Let's grab the Postgres 17 source and build it with debug symbols.

$ git clone https://github.com/postgres/postgres
$ cd postgres
$ git checkout REL_17_STABLE
$ ./configure --enable-debug --without-icu \
  --prefix=$(pwd)/build \
  --libdir=$(pwd)/build/lib
$ make -j16 && make install

Create and start a database.

$ ./build/bin/initdb testdb
$ ./build/bin/pg_ctl -D $(pwd)/testdb -l logfile start

And connect to it with psql.

$ ./build/bin/psql postgres
psql (17.5)
Type "help" for help.
postgres=#

Let's trigger an error by querying a table that doesn't exist.

postgres=# SELECT * FROM nothere;
ERROR:  relation "nothere" does not exist
LINE 1: SELECT * FROM nothere;
                     ^
postgres=#

This is not the type of ERROR we'd normally attempt to debug but it's simple and my goal is to demonstrate the process.

Before I knew better, if I was given some generic ERROR I'd grab the parts of the string that were not dynamic and git grep for them.

$ git grep '"relation' | grep 'does not exist"' | grep '.c:'
src/backend/catalog/aclchk.c:          errmsg("relation with OID %u does not exist",
src/backend/catalog/aclchk.c:          errmsg("relation with OID %u does not exist",
src/backend/catalog/aclchk.c:          errmsg("relation with OID %u does not exist",
src/backend/catalog/namespace.c:       errmsg("relation \"%s.%s\" does not exist",
src/backend/catalog/namespace.c:       errmsg("relation \"%s\" does not exist",
src/backend/parser/parse_relation.c:   errmsg("relation \"%s.%s\" does not exist",
src/backend/parser/parse_relation.c:   errmsg("relation \"%s\" does not exist",
src/backend/parser/parse_relation.c:   errmsg("relation \"%s\" does not exist",
src/backend/utils/adt/regproc.c:       errmsg("relation \"%s\" does not exist",
src/pl/plpgsql/src/pl_comp.c:          errmsg("relation \"%s\" does not exist", ident)));

And yeah after some string manipulation and grep foo we might be able to find it. Or we might not.

One direct path to knowing the function where the ERROR comes from is by setting VERBOSITY to verbose in psql. With this on, psql will tell us the function name and file where the error came from (which, hey, on its own solves a chunk of the original problem).

postgres=# \set VERBOSITY verbose
postgres=# SELECT * FROM nothere;
ERROR:  42P01: relation "nothere" does not exist
LINE 1: SELECT * FROM nothere;
                     ^
LOCATION:  parserOpenTable, parse_relation.c:1452

But I also just learned about about the backtrace_functions GUC while on call last week. You can use this GUC to have Postgres print a stack trace when an error happens. That is super useful!

The trick is that you must specify exactly which functions generating logs you care about getting stack traces for. I assume this is to avoid spamming logs with stack traces. That is, a stack trace is only printed out if a function (named in the comma-separated value of backtrace_functions) is the function where the log comes from.

But with \set VERBOSITY verbose we now know the exact function where the log came from: parserOpenTable. So we can set backtrace_functions to parserOpenTable and reload the Postgres config.

postgres=# ALTER SYSTEM SET backtrace_functions='parserOpenTable';
ALTER SYSTEM
postgres=# SELECT pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)

Run that failing query again:

postgres=# SELECT * FROM nothere;
ERROR:  42P01: relation "nothere" does not exist
LINE 1: SELECT * FROM nothere;
                     ^
LOCATION:  parserOpenTable, parse_relation.c:1452

And we'll see a stack trace for this error in logfile!

2025-07-31 13:46:42.657 EDT [84985] ERROR:  relation "nothere" does not exist at character 15
2025-07-31 13:46:42.657 EDT [84985] BACKTRACE:
       2   postgres                           0x000000010499a0d0 parserOpenTable.cold.1 + 156
       3   postgres                           0x00000001045d64cc parserOpenTable + 124
       4   postgres                           0x00000001045d666c addRangeTableEntry + 272
       5   postgres                           0x00000001045c2744 transformFromClauseItem + 256
       6   postgres                           0x00000001045c25a8 transformFromClause + 132
       7   postgres                           0x00000001045ad3fc transformSelectStmt + 128
       8   postgres                           0x00000001045ac858 transformStmt + 4316
       9   postgres                           0x00000001045ab344 parse_analyze_fixedparams + 192
       10  postgres                           0x00000001048003a8 exec_simple_query + 1024
       11  postgres                           0x00000001047fe2a8 PostgresMain + 3108
       12  postgres                           0x00000001047fa08c BackendInitialize + 0
       13  postgres                           0x000000010476f22c PgArchShmemSize + 0
       14  postgres                           0x0000000104773308 ServerLoop + 6772
       15  postgres                           0x0000000104770d38 PostmasterMain + 3436
       16  postgres                           0x00000001046a5844 main + 800
       17  dyld                               0x0000000196993154 start + 2476

There's also a potential patch to allow you to just automatically print stack traces for all errors. This would be especially useful for automated testing environments where errors can be infrequent and tedious to debug. I look forward to that patch eventually landing!