Re: [HACKERS] Overhead for stats_command_string et al, take - Mailing list pgsql-patches

Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > If you put a contition test in set_ps_display(), the only clean way to
> > do this is for init_ps_display() to force update_process_title to true
> > before we call set_ps_display(), then reset it to its original value,
> > but that sounds pretty ugly.
>
> No, refactor the code.  I was envisioning something called maybe
> transmit_ps_display that would contain the part of set_ps_display
> beginning at "Transmit new setting to kernel".  Then both set_ps_display
> and init_ps_display would call that.

I went with a 'force' boolean for set_ps_display().

--
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/config.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/config.sgml,v
retrieving revision 1.66
diff -c -c -r1.66 config.sgml
*** doc/src/sgml/config.sgml    19 Jun 2006 01:51:21 -0000    1.66
--- doc/src/sgml/config.sgml    26 Jun 2006 23:20:43 -0000
***************
*** 2888,2893 ****
--- 2888,2908 ----
        </listitem>
       </varlistentry>

+      <varlistentry id="guc-update-process-title" xreflabel="update_process_title">
+       <term><varname>update_process_title</varname> (<type>boolean</type>)</term>
+       <indexterm>
+        <primary><varname>update_process_title</> configuration parameter</primary>
+       </indexterm>
+       <listitem>
+        <para>
+         Enables updating of the process title every time a new SQL command
+         is received by the server.  The process title is typically viewed
+         by the <command>ps</> command or in Windows using the <application>Process
+         Explorer</>.   Only superusers can change this setting.
+        </para>
+       </listitem>
+      </varlistentry>
+
       <varlistentry id="guc-stats-start-collector" xreflabel="stats_start_collector">
        <term><varname>stats_start_collector</varname> (<type>boolean</type>)</term>
        <indexterm>
Index: src/backend/bootstrap/bootstrap.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/bootstrap/bootstrap.c,v
retrieving revision 1.217
diff -c -c -r1.217 bootstrap.c
*** src/backend/bootstrap/bootstrap.c    18 Jun 2006 15:38:36 -0000    1.217
--- src/backend/bootstrap/bootstrap.c    26 Jun 2006 23:20:43 -0000
***************
*** 353,360 ****
                  statmsg = "??? process";
                  break;
          }
!         init_ps_display(statmsg, "", "");
!         set_ps_display("");
      }

      /* Acquire configuration parameters, unless inherited from postmaster */
--- 353,359 ----
                  statmsg = "??? process";
                  break;
          }
!         init_ps_display(statmsg, "", "", "");
      }

      /* Acquire configuration parameters, unless inherited from postmaster */
Index: src/backend/commands/async.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/async.c,v
retrieving revision 1.131
diff -c -c -r1.131 async.c
*** src/backend/commands/async.c    25 Apr 2006 14:11:54 -0000    1.131
--- src/backend/commands/async.c    26 Jun 2006 23:20:44 -0000
***************
*** 908,914 ****
      if (Trace_notify)
          elog(DEBUG1, "ProcessIncomingNotify");

!     set_ps_display("notify interrupt");

      notifyInterruptOccurred = 0;

--- 908,914 ----
      if (Trace_notify)
          elog(DEBUG1, "ProcessIncomingNotify");

!     set_ps_display("notify interrupt", false);

      notifyInterruptOccurred = 0;

***************
*** 979,985 ****
       */
      pq_flush();

!     set_ps_display("idle");

      if (Trace_notify)
          elog(DEBUG1, "ProcessIncomingNotify: done");
--- 979,985 ----
       */
      pq_flush();

!     set_ps_display("idle", false);

      if (Trace_notify)
          elog(DEBUG1, "ProcessIncomingNotify: done");
Index: src/backend/postmaster/autovacuum.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/postmaster/autovacuum.c,v
retrieving revision 1.20
diff -c -c -r1.20 autovacuum.c
*** src/backend/postmaster/autovacuum.c    18 Jun 2006 15:38:37 -0000    1.20
--- src/backend/postmaster/autovacuum.c    26 Jun 2006 23:20:44 -0000
***************
*** 239,246 ****
      MyProcPid = getpid();

      /* Identify myself via ps */
!     init_ps_display("autovacuum process", "", "");
!     set_ps_display("");

      SetProcessingMode(InitProcessing);

--- 239,245 ----
      MyProcPid = getpid();

      /* Identify myself via ps */
!     init_ps_display("autovacuum process", "", "", "");

      SetProcessingMode(InitProcessing);

***************
*** 416,422 ****
           */
          InitPostgres(db->name, NULL);
          SetProcessingMode(NormalProcessing);
!         set_ps_display(db->name);
          ereport(DEBUG1,
                  (errmsg("autovacuum: processing database \"%s\"", db->name)));

--- 415,421 ----
           */
          InitPostgres(db->name, NULL);
          SetProcessingMode(NormalProcessing);
!         set_ps_display(db->name, false);
          ereport(DEBUG1,
                  (errmsg("autovacuum: processing database \"%s\"", db->name)));

Index: src/backend/postmaster/pgarch.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/postmaster/pgarch.c,v
retrieving revision 1.23
diff -c -c -r1.23 pgarch.c
*** src/backend/postmaster/pgarch.c    18 Jun 2006 15:38:37 -0000    1.23
--- src/backend/postmaster/pgarch.c    26 Jun 2006 23:20:44 -0000
***************
*** 244,251 ****
      /*
       * Identify myself via ps
       */
!     init_ps_display("archiver process", "", "");
!     set_ps_display("");

      pgarch_MainLoop();

--- 244,250 ----
      /*
       * Identify myself via ps
       */
!     init_ps_display("archiver process", "", "", "");

      pgarch_MainLoop();

Index: src/backend/postmaster/pgstat.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/postmaster/pgstat.c,v
retrieving revision 1.130
diff -c -c -r1.130 pgstat.c
*** src/backend/postmaster/pgstat.c    20 Jun 2006 22:52:00 -0000    1.130
--- src/backend/postmaster/pgstat.c    26 Jun 2006 23:20:46 -0000
***************
*** 1743,1750 ****
      /*
       * Identify myself via ps
       */
!     init_ps_display("stats collector process", "", "");
!     set_ps_display("");

      /*
       * Arrange to write the initial status file right away
--- 1743,1749 ----
      /*
       * Identify myself via ps
       */
!     init_ps_display("stats collector process", "", "", "");

      /*
       * Arrange to write the initial status file right away
***************
*** 1975,1982 ****
      /*
       * Identify myself via ps
       */
!     init_ps_display("stats buffer process", "", "");
!     set_ps_display("");

      /*
       * We want to die if our child collector process does.    There are two ways
--- 1974,1980 ----
      /*
       * Identify myself via ps
       */
!     init_ps_display("stats buffer process", "", "", "");

      /*
       * We want to die if our child collector process does.    There are two ways
Index: src/backend/postmaster/postmaster.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/postmaster/postmaster.c,v
retrieving revision 1.488
diff -c -c -r1.488 postmaster.c
*** src/backend/postmaster/postmaster.c    20 Jun 2006 22:52:00 -0000    1.488
--- src/backend/postmaster/postmaster.c    26 Jun 2006 23:20:48 -0000
***************
*** 2713,2721 ****
       * Now that we have the user and database name, we can set the process
       * title for ps.  It's good to do this as early as possible in startup.
       */
!     init_ps_display(port->user_name, port->database_name, remote_ps_data);
!     set_ps_display("authentication");
!
      /*
       * Now perform authentication exchange.
       */
--- 2713,2721 ----
       * Now that we have the user and database name, we can set the process
       * title for ps.  It's good to do this as early as possible in startup.
       */
!     init_ps_display(port->user_name, port->database_name, remote_ps_data,
!         update_process_title ? "authentication" : "");
!
      /*
       * Now perform authentication exchange.
       */
Index: src/backend/postmaster/syslogger.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/postmaster/syslogger.c,v
retrieving revision 1.25
diff -c -c -r1.25 syslogger.c
*** src/backend/postmaster/syslogger.c    18 Jun 2006 15:38:37 -0000    1.25
--- src/backend/postmaster/syslogger.c    26 Jun 2006 23:20:48 -0000
***************
*** 141,148 ****

      am_syslogger = true;

!     init_ps_display("logger process", "", "");
!     set_ps_display("");

      /*
       * If we restarted, our stderr is already redirected into our own input
--- 141,147 ----

      am_syslogger = true;

!     init_ps_display("logger process", "", "", "");

      /*
       * If we restarted, our stderr is already redirected into our own input
Index: src/backend/storage/lmgr/lock.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/storage/lmgr/lock.c,v
retrieving revision 1.164
diff -c -c -r1.164 lock.c
*** src/backend/storage/lmgr/lock.c    14 Apr 2006 03:38:55 -0000    1.164
--- src/backend/storage/lmgr/lock.c    26 Jun 2006 23:20:49 -0000
***************
*** 1059,1077 ****
      LOCKMETHODID lockmethodid = LOCALLOCK_LOCKMETHOD(*locallock);
      LockMethod    lockMethodTable = LockMethods[lockmethodid];
      const char *old_status;
!     char       *new_status;
      int            len;

      LOCK_PRINT("WaitOnLock: sleeping on lock",
                 locallock->lock, locallock->tag.mode);

!     old_status = get_ps_display(&len);
!     new_status = (char *) palloc(len + 8 + 1);
!     memcpy(new_status, old_status, len);
!     strcpy(new_status + len, " waiting");
!     set_ps_display(new_status);
!     new_status[len] = '\0';        /* truncate off " waiting" */
!
      awaitedLock = locallock;
      awaitedOwner = owner;

--- 1059,1080 ----
      LOCKMETHODID lockmethodid = LOCALLOCK_LOCKMETHOD(*locallock);
      LockMethod    lockMethodTable = LockMethods[lockmethodid];
      const char *old_status;
!     char       *new_status = NULL;
      int            len;

      LOCK_PRINT("WaitOnLock: sleeping on lock",
                 locallock->lock, locallock->tag.mode);

!     if (update_process_title)
!     {
!         old_status = get_ps_display(&len);
!         new_status = (char *) palloc(len + 8 + 1);
!         memcpy(new_status, old_status, len);
!         strcpy(new_status + len, " waiting");
!         set_ps_display(new_status, false);
!         new_status[len] = '\0';        /* truncate off " waiting" */
!     }
!
      awaitedLock = locallock;
      awaitedOwner = owner;

***************
*** 1108,1115 ****

      awaitedLock = NULL;

!     set_ps_display(new_status);
!     pfree(new_status);

      LOCK_PRINT("WaitOnLock: wakeup on lock",
                 locallock->lock, locallock->tag.mode);
--- 1111,1121 ----

      awaitedLock = NULL;

!     if (update_process_title)
!     {
!         set_ps_display(new_status, false);
!         pfree(new_status);
!     }

      LOCK_PRINT("WaitOnLock: wakeup on lock",
                 locallock->lock, locallock->tag.mode);
Index: src/backend/tcop/postgres.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/tcop/postgres.c,v
retrieving revision 1.489
diff -c -c -r1.489 postgres.c
*** src/backend/tcop/postgres.c    20 Jun 2006 22:52:00 -0000    1.489
--- src/backend/tcop/postgres.c    26 Jun 2006 23:20:50 -0000
***************
*** 910,916 ****
           */
          commandTag = CreateCommandTag(parsetree);

!         set_ps_display(commandTag);

          BeginCommand(commandTag, dest);

--- 910,916 ----
           */
          commandTag = CreateCommandTag(parsetree);

!         set_ps_display(commandTag, false);

          BeginCommand(commandTag, dest);

***************
*** 1144,1150 ****

      pgstat_report_activity(query_string);

!     set_ps_display("PARSE");

      if (save_log_statement_stats)
          ResetUsage();
--- 1144,1150 ----

      pgstat_report_activity(query_string);

!     set_ps_display("PARSE", false);

      if (save_log_statement_stats)
          ResetUsage();
***************
*** 1376,1382 ****

      pgstat_report_activity("<BIND>");

!     set_ps_display("BIND");

      /*
       * Start up a transaction command so we can call functions etc. (Note that
--- 1376,1382 ----

      pgstat_report_activity("<BIND>");

!     set_ps_display("BIND", false);

      /*
       * Start up a transaction command so we can call functions etc. (Note that
***************
*** 1711,1717 ****
          pgstat_report_activity("<EXECUTE>");
      }

!     set_ps_display(portal->commandTag);

      /*
       * We use save_log_statement_stats so ShowUsage doesn't report incorrect
--- 1711,1717 ----
          pgstat_report_activity("<EXECUTE>");
      }

!     set_ps_display(portal->commandTag, false);

      /*
       * We use save_log_statement_stats so ShowUsage doesn't report incorrect
***************
*** 2486,2492 ****
      if (!IsUnderPostmaster)
          MemoryContextInit();

!     set_ps_display("startup");

      SetProcessingMode(InitProcessing);

--- 2486,2492 ----
      if (!IsUnderPostmaster)
          MemoryContextInit();

!     set_ps_display("startup", false);

      SetProcessingMode(InitProcessing);

***************
*** 3121,3134 ****
          {
              if (IsTransactionOrTransactionBlock())
              {
!                 set_ps_display("idle in transaction");
                  pgstat_report_activity("<IDLE> in transaction");
              }
              else
              {
                  pgstat_report_tabstat();

!                 set_ps_display("idle");
                  pgstat_report_activity("<IDLE>");
              }

--- 3121,3134 ----
          {
              if (IsTransactionOrTransactionBlock())
              {
!                 set_ps_display("idle in transaction", false);
                  pgstat_report_activity("<IDLE> in transaction");
              }
              else
              {
                  pgstat_report_tabstat();

!                 set_ps_display("idle", false);
                  pgstat_report_activity("<IDLE>");
              }

Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.322
diff -c -c -r1.322 guc.c
*** src/backend/utils/misc/guc.c    19 Jun 2006 01:51:21 -0000    1.322
--- src/backend/utils/misc/guc.c    26 Jun 2006 23:20:52 -0000
***************
*** 64,69 ****
--- 64,70 ----
  #include "utils/builtins.h"
  #include "utils/memutils.h"
  #include "utils/pg_locale.h"
+ #include "utils/ps_status.h"
  #include "pgstat.h"
  #include "access/gin.h"

***************
*** 729,734 ****
--- 730,745 ----
      },

      {
+         {"update_process_title", PGC_SUSET, STATS_COLLECTOR,
+             gettext_noop("Updates the process title to show the active SQL command."),
+             gettext_noop("Enables updating of the process title every time a new
+             SQL command is received by the server.")
+         },
+         &update_process_title,
+         true, NULL, NULL
+     },
+
+     {
          {"autovacuum", PGC_SIGHUP, AUTOVACUUM,
              gettext_noop("Starts the autovacuum subprocess."),
              NULL
Index: src/backend/utils/misc/postgresql.conf.sample
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/misc/postgresql.conf.sample,v
retrieving revision 1.180
diff -c -c -r1.180 postgresql.conf.sample
*** src/backend/utils/misc/postgresql.conf.sample    19 Jun 2006 01:51:21 -0000    1.180
--- src/backend/utils/misc/postgresql.conf.sample    26 Jun 2006 23:20:53 -0000
***************
*** 323,333 ****
--- 323,336 ----
  # - Query/Index Statistics Collector -

  #stats_command_string = off
+ #update_process_title = on
+
  #stats_start_collector = on        # needed for block or row stats
  #stats_block_level = off
  #stats_row_level = off
  #stats_reset_on_server_start = off

+
  # - Statistics Monitoring -

  #log_parser_stats = off
Index: src/backend/utils/misc/ps_status.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/misc/ps_status.c,v
retrieving revision 1.30
diff -c -c -r1.30 ps_status.c
*** src/backend/utils/misc/ps_status.c    12 Jun 2006 02:39:49 -0000    1.30
--- src/backend/utils/misc/ps_status.c    26 Jun 2006 23:20:53 -0000
***************
*** 31,36 ****
--- 31,37 ----
  #include "utils/ps_status.h"

  extern char **environ;
+ bool update_process_title = true;


  /*
***************
*** 210,216 ****
   */
  void
  init_ps_display(const char *username, const char *dbname,
!                 const char *host_info)
  {
      Assert(username);
      Assert(dbname);
--- 211,217 ----
   */
  void
  init_ps_display(const char *username, const char *dbname,
!                 const char *host_info, const char *initial_str)
  {
      Assert(username);
      Assert(dbname);
***************
*** 270,275 ****
--- 271,277 ----

      ps_buffer_fixed_size = strlen(ps_buffer);

+     set_ps_display(initial_str, true);
  #endif   /* not PS_USE_NONE */
  }

***************
*** 280,287 ****
   * indication of what you're currently doing passed in the argument.
   */
  void
! set_ps_display(const char *activity)
  {
  #ifndef PS_USE_NONE
      /* no ps display for stand-alone backend */
      if (!IsUnderPostmaster)
--- 282,293 ----
   * indication of what you're currently doing passed in the argument.
   */
  void
! set_ps_display(const char *activity, bool force)
  {
+
+     if (!force && !update_process_title)
+         return;
+
  #ifndef PS_USE_NONE
      /* no ps display for stand-alone backend */
      if (!IsUnderPostmaster)
Index: src/include/utils/ps_status.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/utils/ps_status.h,v
retrieving revision 1.26
diff -c -c -r1.26 ps_status.h
*** src/include/utils/ps_status.h    5 Nov 2005 03:04:53 -0000    1.26
--- src/include/utils/ps_status.h    26 Jun 2006 23:20:55 -0000
***************
*** 12,23 ****
  #ifndef PS_STATUS_H
  #define PS_STATUS_H

  extern char **save_ps_display_args(int argc, char **argv);

  extern void init_ps_display(const char *username, const char *dbname,
!                 const char *host_info);

! extern void set_ps_display(const char *activity);

  extern const char *get_ps_display(int *displen);

--- 12,25 ----
  #ifndef PS_STATUS_H
  #define PS_STATUS_H

+ extern bool update_process_title;
+
  extern char **save_ps_display_args(int argc, char **argv);

  extern void init_ps_display(const char *username, const char *dbname,
!                 const char *host_info, const char *initial_str);

! extern void set_ps_display(const char *activity, bool force);

  extern const char *get_ps_display(int *displen);