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).

  1. 2 comments

  2. # Guillaume
    Jul 7, 2009

    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.

  3. Jul 7, 2009

    @Guillaume:
    thanks for clarification. The fact that it will be possible clearly escaped me.

Leave a comment