Attached is a patch for tagging log lines produced by backends. It is not quite ready for application - this is for people to look over and play with.


For my testing I used the following settings:

log_connections = true
log_disconnections = true
log_line_info = '%T [%P] [EMAIL PROTECTED](%C:%S) %I line:%L '
log_statement = true
log_hostname = true
log_source_port = true


Comments welcome


(Rod Taylor has suggested adding in escapes for remote host and remote port. This can be done easily enough - I want to call a halt at some stage and get the basics of this in. Extending it will then be easy).

cheers

andrew
? GNUmakefile
? config.log
? config.status
? log_line_info.patch
? log_line_info.patch2
? logpatch.txt
? updatelog
? contrib/spi/.deps
? src/Makefile.global
? src/backend/postgres
? src/backend/access/common/.deps
? src/backend/access/gist/.deps
? src/backend/access/hash/.deps
? src/backend/access/heap/.deps
? src/backend/access/index/.deps
? src/backend/access/nbtree/.deps
? src/backend/access/rtree/.deps
? src/backend/access/transam/.deps
? src/backend/bootstrap/.deps
? src/backend/catalog/.deps
? src/backend/catalog/postgres.bki
? src/backend/catalog/postgres.description
? src/backend/commands/.deps
? src/backend/executor/.deps
? src/backend/lib/.deps
? src/backend/libpq/.deps
? src/backend/main/.deps
? src/backend/nodes/.deps
? src/backend/optimizer/geqo/.deps
? src/backend/optimizer/path/.deps
? src/backend/optimizer/plan/.deps
? src/backend/optimizer/prep/.deps
? src/backend/optimizer/util/.deps
? src/backend/parser/.deps
? src/backend/port/.deps
? src/backend/postmaster/.deps
? src/backend/regex/.deps
? src/backend/rewrite/.deps
? src/backend/storage/buffer/.deps
? src/backend/storage/file/.deps
? src/backend/storage/freespace/.deps
? src/backend/storage/ipc/.deps
? src/backend/storage/large_object/.deps
? src/backend/storage/lmgr/.deps
? src/backend/storage/page/.deps
? src/backend/storage/smgr/.deps
? src/backend/tcop/.deps
? src/backend/utils/.deps
? src/backend/utils/adt/.deps
? src/backend/utils/cache/.deps
? src/backend/utils/error/.deps
? src/backend/utils/fmgr/.deps
? src/backend/utils/hash/.deps
? src/backend/utils/init/.deps
? src/backend/utils/mb/.deps
? src/backend/utils/mb/conversion_procs/conversion_create.sql
? src/backend/utils/mb/conversion_procs/ascii_and_mic/.deps
? src/backend/utils/mb/conversion_procs/ascii_and_mic/libascii_and_mic.so.0.0
? src/backend/utils/mb/conversion_procs/cyrillic_and_mic/.deps
? src/backend/utils/mb/conversion_procs/cyrillic_and_mic/libcyrillic_and_mic.so.0.0
? src/backend/utils/mb/conversion_procs/euc_cn_and_mic/.deps
? src/backend/utils/mb/conversion_procs/euc_cn_and_mic/libeuc_cn_and_mic.so.0.0
? src/backend/utils/mb/conversion_procs/euc_jp_and_sjis/.deps
? src/backend/utils/mb/conversion_procs/euc_jp_and_sjis/libeuc_jp_and_sjis.so.0.0
? src/backend/utils/mb/conversion_procs/euc_kr_and_mic/.deps
? src/backend/utils/mb/conversion_procs/euc_kr_and_mic/libeuc_kr_and_mic.so.0.0
? src/backend/utils/mb/conversion_procs/euc_tw_and_big5/.deps
? src/backend/utils/mb/conversion_procs/euc_tw_and_big5/libeuc_tw_and_big5.so.0.0
? src/backend/utils/mb/conversion_procs/latin2_and_win1250/.deps
? src/backend/utils/mb/conversion_procs/latin2_and_win1250/liblatin2_and_win1250.so.0.0
? src/backend/utils/mb/conversion_procs/latin_and_mic/.deps
? src/backend/utils/mb/conversion_procs/latin_and_mic/liblatin_and_mic.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_ascii/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_ascii/libutf8_and_ascii.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_big5/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_big5/libutf8_and_big5.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_cyrillic/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_cyrillic/libutf8_and_cyrillic.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_euc_cn/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_euc_cn/libutf8_and_euc_cn.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_euc_jp/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_euc_jp/libutf8_and_euc_jp.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_euc_kr/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_euc_kr/libutf8_and_euc_kr.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_euc_tw/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_euc_tw/libutf8_and_euc_tw.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_gb18030/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_gb18030/libutf8_and_gb18030.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_gbk/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_gbk/libutf8_and_gbk.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_iso8859/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_iso8859/libutf8_and_iso8859.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_iso8859_1/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_iso8859_1/libutf8_and_iso8859_1.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_johab/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_johab/libutf8_and_johab.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_sjis/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_sjis/libutf8_and_sjis.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_tcvn/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_tcvn/libutf8_and_tcvn.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_uhc/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_uhc/libutf8_and_uhc.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_win1250/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_win1250/libutf8_and_win1250.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_win1256/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_win1256/libutf8_and_win1256.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_win874/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_win874/libutf8_and_win874.so.0.0
? src/backend/utils/misc/.deps
? src/backend/utils/mmgr/.deps
? src/backend/utils/sort/.deps
? src/backend/utils/time/.deps
? src/bin/initdb/.deps
? src/bin/initdb/initdb
? src/bin/initlocation/initlocation
? src/bin/ipcclean/ipcclean
? src/bin/pg_config/pg_config
? src/bin/pg_controldata/.deps
? src/bin/pg_controldata/pg_controldata
? src/bin/pg_ctl/pg_ctl
? src/bin/pg_dump/.deps
? src/bin/pg_dump/pg_dump
? src/bin/pg_dump/pg_dumpall
? src/bin/pg_dump/pg_restore
? src/bin/pg_encoding/.deps
? src/bin/pg_encoding/pg_encoding
? src/bin/pg_resetxlog/.deps
? src/bin/pg_resetxlog/pg_resetxlog
? src/bin/psql/.deps
? src/bin/psql/psql
? src/bin/scripts/.deps
? src/bin/scripts/clusterdb
? src/bin/scripts/createdb
? src/bin/scripts/createlang
? src/bin/scripts/createuser
? src/bin/scripts/dropdb
? src/bin/scripts/droplang
? src/bin/scripts/dropuser
? src/bin/scripts/vacuumdb
? src/include/pg_config.h
? src/include/stamp-h
? src/interfaces/ecpg/compatlib/.deps
? src/interfaces/ecpg/compatlib/libecpg_compat.so.1.1
? src/interfaces/ecpg/ecpglib/.deps
? src/interfaces/ecpg/ecpglib/libecpg.so.4.1
? src/interfaces/ecpg/pgtypeslib/.deps
? src/interfaces/ecpg/pgtypeslib/libpgtypes.so.1.1
? src/interfaces/ecpg/preproc/.deps
? src/interfaces/ecpg/preproc/ecpg
? src/interfaces/libpq/.deps
? src/interfaces/libpq/libpq.so.3.2
? src/pl/plpgsql/src/.deps
? src/pl/plpgsql/src/libplpgsql.so.1.0
? src/port/.deps
? src/test/regress/.deps
? src/test/regress/log
? src/test/regress/pg_regress
? src/test/regress/results
? src/test/regress/tmp_check
? src/test/regress/expected/constraints.out
? src/test/regress/expected/copy.out
? src/test/regress/expected/create_function_1.out
? src/test/regress/expected/create_function_2.out
? src/test/regress/expected/misc.out
? src/test/regress/sql/constraints.sql
? src/test/regress/sql/copy.sql
? src/test/regress/sql/create_function_1.sql
? src/test/regress/sql/create_function_2.sql
? src/test/regress/sql/misc.sql
Index: src/backend/postmaster/postmaster.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/postmaster/postmaster.c,v
retrieving revision 1.367
diff -c -r1.367 postmaster.c
*** src/backend/postmaster/postmaster.c 17 Feb 2004 03:54:56 -0000      1.367
--- src/backend/postmaster/postmaster.c 23 Feb 2004 15:04:47 -0000
***************
*** 2434,2439 ****
--- 2434,2440 ----
        /* set these to empty in case they are needed before we set them up */
        port->remote_host = "";
        port->remote_port = "";
+       port->commandTag = "";
  
        /* Save port etc. for ps status */
        MyProcPort = port;
Index: src/backend/utils/error/elog.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/error/elog.c,v
retrieving revision 1.126
diff -c -r1.126 elog.c
*** src/backend/utils/error/elog.c      29 Nov 2003 19:52:01 -0000      1.126
--- src/backend/utils/error/elog.c      23 Feb 2004 15:04:47 -0000
***************
*** 72,77 ****
--- 72,78 ----
  bool          Log_timestamp = false;          /* show timestamps in stderr
                                                                                 * 
output */
  bool          Log_pid = false;        /* show PIDs in stderr output */
+ char       *Log_line_info = ""; /* format for extra log line info */
  
  #ifdef HAVE_SYSLOG
  /*
***************
*** 146,152 ****
  static const char *print_timestamp(void);
  static const char *print_pid(void);
  static void append_with_tabs(StringInfo buf, const char *str);
! 
  
  /*
   * errstart --- begin an error-reporting cycle
--- 147,153 ----
  static const char *print_timestamp(void);
  static const char *print_pid(void);
  static void append_with_tabs(StringInfo buf, const char *str);
! static const char *log_line_info(void);
  
  /*
   * errstart --- begin an error-reporting cycle
***************
*** 1022,1027 ****
--- 1023,1132 ----
  }
  #endif   /* HAVE_SYSLOG */
  
+ /*
+  * Formatted tag info for log if option is set and we have data,
+  * or empty string otherwise.
+  */
+ static const char * 
+ log_line_info(void)
+ {
+ 
+       /* static accumulator for line numbers */
+       static int log_line_number = 0;
+  
+       /* space for option string + one of each option, plus some room to spare */
+       /* Note: if more identifiers are built in this will have to increase */
+       static char *result = NULL;
+       int format_len = strlen(Log_line_info);
+       int result_len = 2*NAMEDATALEN + format_len +120 ;
+ 
+       if (result == NULL)
+               result = malloc(result_len);
+       result[0] = '\0';
+ 
+       if (format_len > 0 && MyProcPort)
+       {
+               int i,j;
+               char * dbname = MyProcPort->database_name;
+               char * username = MyProcPort->user_name;
+               time_t stamp_time;
+               log_line_number++;
+               if (dbname == NULL)
+                       dbname="";
+               if (username == NULL)
+                       username = "";
+ 
+               /*
+                * invariant through each iteration of this loop:
+                *  . j is the index of the trailing null on result
+                *  . result_len - j is the number of chars we have room for
+                *    including the trailing null
+                *  . there is room to write at least one more non-null char plus the
+                *    trailing null
+                */
+               for (i = 0, j=0; i < format_len && j < result_len-1; i++)
+               {
+                       if(Log_line_info[i] != '%')
+                       {
+                               /* literal char, just copy */
+                               result[j]=Log_line_info[i];
+                               j++;
+                               result[j] = '\0';
+                               continue;
+                       }
+                       else if (i == format_len - 1)
+                       {
+                               /* format error - skip it */
+                               continue;
+                       }
+                       /* go to char after '%', take action accordingly */
+                       i++;
+                       switch (Log_line_info[i])
+                       {
+                               case 'U': 
+                                       j += 
snprintf(result+j,result_len-j,"%s",username);
+                                       break;
+                               case 'D': 
+                                       j += 
snprintf(result+j,result_len-j,"%s",dbname);
+                                       break;
+                               case 'C':
+                                       j += snprintf(result+j,result_len-j,"%lx.%lx",
+                                                                 
(long)(MyProcPort->session_start.tv_sec),
+                                                                 (long)MyProcPid);
+                                       break;
+                               case 'P':
+                                       j += 
snprintf(result+j,result_len-j,"%ld",(long)MyProcPid);
+                                       break;
+                               case 'L':
+                                       j += 
snprintf(result+j,result_len-j,"%d",log_line_number);
+                                       break;
+                               case 'T':
+                                       stamp_time = time(NULL);
+                                       j += strftime(result+j, result_len-j, 
"%Y-%m-%d %H:%M:%S",
+                                                        localtime(&stamp_time));
+                                       break;
+                               case 'S':
+                                       j += strftime(result+j, result_len-j, 
"%Y-%m-%d %H:%M:%S",
+                                                        
localtime(&(MyProcPort->session_start.tv_sec)));
+                                       break;
+                               case 'I':
+                                       j += snprintf(result+j,result_len-j,"%s",
+                                                                 
MyProcPort->commandTag);
+                                       break;
+                               case '%': 
+                                       result[j] = '%';
+                                       j++;
+                                       result[j] = '\0';
+                                       break;
+                               default:
+                                       /* format error - skip it */
+                                       break;
+                       }
+               }
+       }
+       return result;
+ }
+ 
  
  /*
   * Write error report to server's log
***************
*** 1033,1039 ****
  
        initStringInfo(&buf);
  
!       appendStringInfo(&buf, "%s:  ", error_severity(edata->elevel));
  
        if (Log_error_verbosity >= PGERROR_VERBOSE)
        {
--- 1138,1145 ----
  
        initStringInfo(&buf);
  
!       appendStringInfo(&buf, "%s%s:  ", 
!                                        log_line_info(), 
error_severity(edata->elevel));
  
        if (Log_error_verbosity >= PGERROR_VERBOSE)
        {
***************
*** 1066,1083 ****
--- 1172,1192 ----
        {
                if (edata->detail)
                {
+                       appendStringInfoString(&buf, log_line_info() );
                        appendStringInfoString(&buf, gettext("DETAIL:  "));
                        append_with_tabs(&buf, edata->detail);
                        appendStringInfoChar(&buf, '\n');
                }
                if (edata->hint)
                {
+                       appendStringInfoString(&buf, log_line_info() );
                        appendStringInfoString(&buf, gettext("HINT:  "));
                        append_with_tabs(&buf, edata->hint);
                        appendStringInfoChar(&buf, '\n');
                }
                if (edata->context)
                {
+                       appendStringInfoString(&buf, log_line_info() );
                        appendStringInfoString(&buf, gettext("CONTEXT:  "));
                        append_with_tabs(&buf, edata->context);
                        appendStringInfoChar(&buf, '\n');
***************
*** 1086,1096 ****
                {
                        /* assume no newlines in funcname or filename... */
                        if (edata->funcname && edata->filename)
!                               appendStringInfo(&buf, gettext("LOCATION:  %s, 
%s:%d\n"),
                                                                 edata->funcname, 
edata->filename,
                                                                 edata->lineno);
                        else if (edata->filename)
!                               appendStringInfo(&buf, gettext("LOCATION:  %s:%d\n"),
                                                                 edata->filename, 
edata->lineno);
                }
        }
--- 1195,1207 ----
                {
                        /* assume no newlines in funcname or filename... */
                        if (edata->funcname && edata->filename)
!                               appendStringInfo(&buf, gettext("%sLOCATION:  %s, 
%s:%d\n"),
!                                                                log_line_info(),
                                                                 edata->funcname, 
edata->filename,
                                                                 edata->lineno);
                        else if (edata->filename)
!                               appendStringInfo(&buf, gettext("%sLOCATION:  %s:%d\n"),
!                                                                log_line_info(),
                                                                 edata->filename, 
edata->lineno);
                }
        }
***************
*** 1100,1105 ****
--- 1211,1217 ----
         */
        if (edata->elevel >= log_min_error_statement && debug_query_string != NULL)
        {
+               appendStringInfoString(&buf, log_line_info() );
                appendStringInfoString(&buf, gettext("STATEMENT:  "));
                append_with_tabs(&buf, debug_query_string);
                appendStringInfoChar(&buf, '\n');
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/misc/guc.c,v
retrieving revision 1.187
diff -c -r1.187 guc.c
*** src/backend/utils/misc/guc.c        17 Feb 2004 03:54:57 -0000      1.187
--- src/backend/utils/misc/guc.c        23 Feb 2004 15:04:47 -0000
***************
*** 1496,1501 ****
--- 1496,1511 ----
        },
  
        {
+               {"log_line_info", PGC_SIGHUP, LOGGING_WHAT,
+                gettext_noop("Controls extra information on each log line"),
+                gettext_noop("if blank no extra info is logged")
+               },
+               &Log_line_info,
+               "", NULL, NULL
+       },
+ 
+ 
+       {
                {"DateStyle", PGC_USERSET, CLIENT_CONN_LOCALE,
                        gettext_noop("Sets the display format for date and time 
values."),
                        gettext_noop("Also controls interpretation of ambiguous "
Index: src/backend/utils/misc/postgresql.conf.sample
===================================================================
RCS file: 
/projects/cvsroot/pgsql-server/src/backend/utils/misc/postgresql.conf.sample,v
retrieving revision 1.106
diff -c -r1.106 postgresql.conf.sample
*** src/backend/utils/misc/postgresql.conf.sample       17 Feb 2004 03:54:57 -0000     
 1.106
--- src/backend/utils/misc/postgresql.conf.sample       23 Feb 2004 15:04:48 -0000
***************
*** 185,190 ****
--- 185,195 ----
  #log_disconnections = false
  #log_duration = false
  #log_pid = false
+ #log_line_info = ''         # e.g. '<%U%%%D> ' 
+                             #  %U=username %D=databasename %C=sessionid
+                           #  %P=PID %T=timestamp %L=session line number
+                             #  %S=session start timestamp %I=command tag
+                             #  %%=%
  #log_statement = false
  #log_timestamp = false
  #log_hostname = false
Index: src/backend/utils/misc/ps_status.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/misc/ps_status.c,v
retrieving revision 1.17
diff -c -r1.17 ps_status.c
*** src/backend/utils/misc/ps_status.c  22 Feb 2004 21:26:55 -0000      1.17
--- src/backend/utils/misc/ps_status.c  23 Feb 2004 15:04:48 -0000
***************
*** 276,285 ****
  void
  set_ps_display(const char *activity)
  {
! #ifndef PS_USE_NONE
        /* no ps display for stand-alone backend */
        if (!IsUnderPostmaster)
                return;
  
  #ifdef PS_USE_CLOBBER_ARGV
        /* If ps_buffer is a pointer, it might still be null */
--- 276,291 ----
  void
  set_ps_display(const char *activity)
  {
!   
        /* no ps display for stand-alone backend */
        if (!IsUnderPostmaster)
                return;
+ 
+       /* save it for logging context */
+       if (MyProcPort)
+         MyProcPort->commandTag = activity;
+ 
+ #ifndef PS_USE_NONE
  
  #ifdef PS_USE_CLOBBER_ARGV
        /* If ps_buffer is a pointer, it might still be null */
Index: src/include/miscadmin.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/include/miscadmin.h,v
retrieving revision 1.153
diff -c -r1.153 miscadmin.h
*** src/include/miscadmin.h     10 Feb 2004 03:42:45 -0000      1.153
--- src/include/miscadmin.h     23 Feb 2004 15:04:48 -0000
***************
*** 24,29 ****
--- 24,31 ----
  #define MISCADMIN_H
  
  
+ #include "libpq/libpq-be.h"
+ 
  /*****************************************************************************
   *      System interrupt and critical section handling
   *
Index: src/include/libpq/libpq-be.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/include/libpq/libpq-be.h,v
retrieving revision 1.41
diff -c -r1.41 libpq-be.h
*** src/include/libpq/libpq-be.h        21 Feb 2004 06:29:58 -0000      1.41
--- src/include/libpq/libpq-be.h        23 Feb 2004 15:04:48 -0000
***************
*** 50,55 ****
--- 50,56 ----
        SockAddr        raddr;                  /* remote addr (client) */
        char        *remote_host;   /* name (or ip addr) of remote host */
        char        *remote_port;   /* text rep of remote port */
+       char        *commandTag;    /* command tag for display in log lines */
        struct timeval  session_start;  /* for session duration logging */
        CAC_state       canAcceptConnections;   /* postmaster connection status */
  
Index: src/include/utils/elog.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/include/utils/elog.h,v
retrieving revision 1.64
diff -c -r1.64 elog.h
*** src/include/utils/elog.h    29 Nov 2003 22:41:15 -0000      1.64
--- src/include/utils/elog.h    23 Feb 2004 15:04:48 -0000
***************
*** 171,176 ****
--- 171,177 ----
  extern PGErrorVerbosity Log_error_verbosity;
  extern bool Log_timestamp;
  extern bool Log_pid;
+ extern char *Log_line_info;
  
  #ifdef HAVE_SYSLOG
  extern int    Use_syslog;
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html

Reply via email to