Waiting for 8.5 – Add log_line_prefix placeholder %e to contain the current SQL state

Also yesterday, and also Peter Eisentraut, committed patch by Guillaume Smet, which:

Add log_line_prefix placeholder %e to contain the current SQL state
 
Author: Guillaume Smet

What exactly does it do, and how the state looks? Let's find out.

I defined log_line_prefix to be ‘%m %u@%d %p %r {%e} ‘.

log_min_duration_statement was set to 0.

Then I issued some queries. Logs looked like this:

2009-07-04 20:19:49.671 CEST [UNKNOWN]@[UNKNOWN] 7779  {00000} LOG:  connection received: host=[LOCAL]
2009-07-04 20:19:49.671 CEST depesz@depesz 7779 [LOCAL] {00000} LOG:  connection authorized: USER=depesz DATABASE=depesz
2009-07-04 20:19:53.462 CEST depesz@depesz 7779 [LOCAL] {00000} LOG:  duration: 0.834 ms  statement: SELECT 1;
2009-07-04 20:19:58.750 CEST depesz@depesz 7779 [LOCAL] {00000} LOG:  duration: 0.105 ms  statement: BEGIN;
2009-07-04 20:20:05.009 CEST depesz@depesz 7779 [LOCAL] {00000} LOG:  duration: 0.177 ms  statement: SELECT 2;
2009-07-04 20:20:11.284 CEST depesz@depesz 7779 [LOCAL] {42703} ERROR:  COLUMN "aaa" does NOT exist at CHARACTER 8
2009-07-04 20:20:11.284 CEST depesz@depesz 7779 [LOCAL] {42703} STATEMENT:  SELECT aaa;
2009-07-04 20:20:15.140 CEST depesz@depesz 7779 [LOCAL] {25P02} ERROR:  CURRENT TRANSACTION IS aborted, commands ignored until END OF TRANSACTION block
2009-07-04 20:20:15.140 CEST depesz@depesz 7779 [LOCAL] {25P02} STATEMENT:  SELECT 1;
2009-07-04 20:20:27.820 CEST depesz@depesz 7779 [LOCAL] {00000} LOG:  duration: 0.088 ms  statement: ROLLBACK;
2009-07-04 20:20:29.614 CEST depesz@depesz 7779 [LOCAL] {00000} LOG:  duration: 0.198 ms  statement: SELECT 2;

As you can see the %e is 00000, or 42703 or 25P02. Actually – it can be much more than this – full list of codes can be found in docs.

Generally it's not really fancy, but it will help in debugging and monitoring – after all, error messages are not constant (think about locales), but their codes are (or at least should be).

2 thoughts on “Waiting for 8.5 – Add log_line_prefix placeholder %e to contain the current SQL state”

  1. Thanks for your article.

    The main point is not especially to have a fixed reference compared to a localized message (they are in only one language in the server logs usually).

    It’s to be able to categorize errors based on the first 2 characters of the error code which define the class of the error. You can then separate end user errors (syntax errors and so on) from system ones: it’s very useful to filter the error the system administrator have to take seriously into account.

Comments are closed.