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).
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.
@Guillaume:
thanks for clarification. The fact that it will be possible clearly escaped me.