log_line_info - Mailing list pgsql-patches

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] %U@%D(%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;