Linux: show database state instead of OS state

8 files changed, 61 insertions, 43 deletions

diff --git a/HISTORY b/HISTORY
index 29a6256..c3cb569 100644
--- a/HISTORY
+++ b/HISTORY
@@ -6,6 +6,7 @@ Release 4.0.0
      * Remove table stats monitoring, use pgstat instead
      * Remove index stats monitoring, use pgstat instead
      * Remove kill and renice command, and stop displaying nice priority
+     * Show backend state instead of operating system state
 
 Release 3.7.0
 
diff --git a/machine.h b/machine.h
index a9ee29b..74782a9 100644
--- a/machine.h
+++ b/machine.h
@@ -160,7 +160,10 @@ char	   *format_header(char *);
 char	   *format_next_io(caddr_t, char *(*) (uid_t));
 char	   *format_next_process(caddr_t, char *(*) (uid_t));
 uid_t			proc_owner(pid_t);
+void		update_state(int *pgstate, char *state);
 
 extern int	mode_stats;
 
+extern char *backendstatenames[];
+
 #endif   /* _MACHINE_H_ */
diff --git a/machine/m_common.c b/machine/m_common.c
index cbab541..c8cf952 100644
--- a/machine/m_common.c
+++ b/machine/m_common.c
@@ -22,6 +22,12 @@
 
 #define QUERY_DATA_DIRECTORY "SHOW data_directory;"
 
+char *backendstatenames[] =
+{
+	"", "idle", "active", "idltxn", "fast", "abort", "disabl", NULL
+};
+
+
 /* Store data directory to avoid unnecessary requests to server */
 static char *data_directory = NULL;
 
@@ -145,3 +151,22 @@ get_data_directory(const char *values[])
 
 	return data_directory;
 }
+
+void
+update_state(int *pgstate, char *state)
+{
+	if (strcmp(state, "idle") == 0)
+		*pgstate = STATE_IDLE;
+	else if (strcmp(state, "active") == 0)
+		*pgstate = STATE_RUNNING;
+	else if (strcmp(state, "idle in transaction") == 0)
+		*pgstate = STATE_IDLEINTRANSACTION;
+	else if (strcmp(state, "fastpath function call") == 0)
+		*pgstate = STATE_FASTPATH;
+	else if (strcmp(state, "idle in transaction (aborted)") == 0)
+		*pgstate = STATE_IDLEINTRANSACTION_ABORTED;
+	else if (strcmp(state, "disabled") == 0)
+		*pgstate = STATE_DISABLED;
+	else
+		*pgstate = STATE_UNDEFINED;
+}
diff --git a/machine/m_linux.c b/machine/m_linux.c
index 0a54590..e72527f 100644
--- a/machine/m_linux.c
+++ b/machine/m_linux.c
@@ -80,6 +80,7 @@ struct top_proc
 	unsigned long size,
 				rss;			/* in k */
 	int			state;
+	int	    	pgstate;
 	unsigned long time;
 	unsigned long start_time;
 	double		pcpu,
@@ -125,11 +126,6 @@ struct io_node
 /*=STATE IDENT STRINGS==================================================*/
 
 #define NPROCSTATES 7
-static char *state_abbrev[NPROCSTATES + 1] =
-{
-	"", "run", "sleep", "disk", "zomb", "stop", "swap",
-	NULL
-};
 
 static char *procstatenames[NPROCSTATES + 1] =
 {
@@ -169,7 +165,7 @@ static char *swapnames[NSWAPSTATS + 1] =
 };
 
 static char fmt_header[] =
-"  PID X        PRI NICE  SIZE   RES STATE   TIME   WCPU    CPU COMMAND";
+"  PID X        PRI NICE  SIZE   RES STATE    TIME   WCPU    CPU COMMAND";
 
 /* these are names given to allowed sorting orders -- first is default */
 static char *ordernames[] = {"cpu", "size", "res", "time", "command", NULL};
@@ -372,7 +368,6 @@ free_proc(struct top_proc * proc)
 	freelist = proc;
 }
 
-
 int
 machine_init(struct statics * statics)
 
@@ -926,9 +921,7 @@ get_process_info(struct system_info * si,
 			read_one_proc_stat(pid, proc, sel);
 			if (sel->fullcmd == 2)
 				update_procname(proc, PQgetvalue(pgresult, i, 1));
-
-			if (proc->state == 0)
-				continue;
+			update_state(&proc->pgstate, PQgetvalue(pgresult, i, 2));
 
 			total_procs++;
 			process_states[proc->state]++;
@@ -1102,14 +1095,14 @@ format_next_process(caddr_t handle, char *(*get_userid) (uid_t))
 	struct top_proc *p = *nextactive++;
 
 	snprintf(fmt, sizeof(fmt),
-			 "%5d %-8.8s %3d %4d %5s %5s %-5s %6s %5.2f%% %5.2f%% %s",
+			 "%5d %-8.8s %3d %4d %5s %5s %-6s %5s %5.2f%% %5.2f%% %s",
 			 p->pid,
 			 (*get_userid) (p->uid),
 			 p->pri < -99 ? -99 : p->pri,
 			 p->nice,
 			 format_k(p->size),
 			 format_k(p->rss),
-			 state_abbrev[p->state],
+			 backendstatenames[p->pgstate],
 			 format_time(p->time / HZ),
 			 p->wcpu * 100.0,
 			 p->pcpu * 100.0,
@@ -1141,8 +1134,7 @@ format_next_process(caddr_t handle, char *(*get_userid) (uid_t))
 #define ORDERKEY_PCTCPU  if (dresult = p2->pcpu - p1->pcpu,\
 			 (result = dresult > 0.0 ? 1 : dresult < 0.0 ? -1 : 0) == 0)
 #define ORDERKEY_CPTICKS if ((result = (long)p2->time - (long)p1->time) == 0)
-#define ORDERKEY_STATE	 if ((result = (sort_state[p2->state] - \
-			 sort_state[p1->state])) == 0)
+#define ORDERKEY_STATE	 if ((result = p1->pgstate < p2->pgstate))
 #define ORDERKEY_PRIO	 if ((result = p2->pri - p1->pri) == 0)
 #define ORDERKEY_RSSIZE  if ((result = p2->rss - p1->rss) == 0)
 #define ORDERKEY_MEM	 if ((result = p2->size - p1->size) == 0)
@@ -1156,19 +1148,6 @@ format_next_process(caddr_t handle, char *(*get_userid) (uid_t))
 #define ORDERKEY_WRITES	 if ((result = p1->write_bytes - p2->write_bytes) == 0)
 #define ORDERKEY_CWRITES if ((result = p1->cancelled_write_bytes - p2->cancelled_write_bytes) == 0)
 
-/* Now the array that maps process state to a weight */
-
-unsigned char sort_state[] =
-{
-	0,							/* empty */
-	6,							/* run */
-	3,							/* sleep */
-	5,							/* disk wait */
-	1,							/* zombie */
-	2,							/* stop */
-	4							/* swap */
-};
-
 
 /* compare_cpu - the comparison function for sorting by cpu percentage */
 
diff --git a/machine/m_remote.c b/machine/m_remote.c
index 285fc13..ae319bc 100644
--- a/machine/m_remote.c
+++ b/machine/m_remote.c
@@ -27,15 +27,16 @@
 		"FROM pg_memusage()"
 
 #define QUERY_PROCTAB \
-		"SELECT pid, comm, fullcomm, state, utime, stime, priority, nice,\n" \
+		"SELECT a.pid, comm, fullcomm, a.state, utime, stime, priority, nice,\n" \
 		"       starttime, vsize, rss, uid, username, rchar, wchar,\n" \
-		"       syscr, syscw, reads, writes, cwrites\n" \
-		"FROM pg_proctab()"
+		"       syscr, syscw, reads, writes, cwrites, b.state\n" \
+		"FROM pg_proctab() a LEFT OUTER JOIN pg_stat_activity b\n" \
+		"                    ON a.pid = b.pid"
 
 #define QUERY_PROCTAB_QUERY \
 		"SELECT a.pid, comm, query, a.state, utime, stime, priority, nice,\n" \
 		"       starttime, vsize, rss, uid, username, rchar, wchar,\n" \
-		"       syscr, syscw, reads, writes, cwrites\n" \
+		"       syscr, syscw, reads, writes, cwrites, b.state\n" \
 		"FROM pg_proctab() a LEFT OUTER JOIN pg_stat_activity b\n" \
 		"                    ON a.pid = b.pid"
 
@@ -51,7 +52,8 @@ enum column_memusage { c_memused, c_memfree, c_memshared, c_membuffers,
 		c_memcached, c_swapused, c_swapfree, c_swapcached};
 enum column_proctab { c_pid, c_comm, c_fullcomm, c_state, c_utime, c_stime,
 		c_priority, c_nice, c_starttime, c_vsize, c_rss, c_uid, c_username,
-		c_rchar, c_wchar, c_syscr, c_syscw, c_reads, c_writes, c_cwrites };
+		c_rchar, c_wchar, c_syscr, c_syscw, c_reads, c_writes, c_cwrites,
+		c_pgstate};
 
 #define HASH_SIZE (1003)
 #define HASH(x) (((x) * 1686629713U) % HASH_SIZE)
@@ -88,6 +90,7 @@ struct top_proc
 	unsigned long size;
 	unsigned long rss; /* in k */
 	int state;
+	int pgstate;
 	unsigned long time;
 	unsigned long start_time;
 	double pcpu;
@@ -142,11 +145,6 @@ static char *procstatenames[NPROCSTATES + 1] =
 	" stopped, ", " swapping, ", NULL
 };
 
-static char *state_abbrev[NPROCSTATES + 1] =
-{
-	"", "run", "sleep", "disk", "zomb", "stop", "swap", NULL
-};
-
 static char *swapnames[NSWAPSTATS + 1] =
 {
 	"K used, ", "K free, ", "K cached", NULL
@@ -182,8 +180,7 @@ static int64_t cp_diff[NCPUSTATES];
 #define ORDERKEY_PCTCPU  if (dresult = p2->pcpu - p1->pcpu,\
 			 (result = dresult > 0.0 ? 1 : dresult < 0.0 ? -1 : 0) == 0)
 #define ORDERKEY_CPTICKS if ((result = (long)p2->time - (long)p1->time) == 0)
-#define ORDERKEY_STATE   if ((result = (sort_state_r[p2->state] - \
-			 sort_state_r[p1->state])) == 0)
+#define ORDERKEY_STATE	 if ((result = p1->pgstate < p2->pgstate))
 #define ORDERKEY_PRIO	if ((result = p2->pri - p1->pri) == 0)
 #define ORDERKEY_RSSIZE  if ((result = p2->rss - p1->rss) == 0)
 #define ORDERKEY_MEM	 if ((result = p2->size - p1->size) == 0)
@@ -421,7 +418,7 @@ format_next_process_r(caddr_t handler)
 			p->nice,
 			format_k(p->size),
 			format_k(p->rss),
-			state_abbrev[p->state],
+			backendstatenames[p->pgstate],
 			format_time(p->time),
 			p->wcpu * 100.0,
 			p->pcpu * 100.0,
@@ -669,6 +666,7 @@ get_process_info_r(struct system_info *si, struct process_select *sel,
 		case '\0':
 			continue;
 		}
+		update_state(&proc->pgstate, PQgetvalue(pgresult, i, c_pgstate));
 
 		proc->time = (unsigned long) atol(PQgetvalue(pgresult, i, c_utime));
 		proc->time += (unsigned long) atol(PQgetvalue(pgresult, i, c_stime));
diff --git a/pg.c b/pg.c
index ab1f112..1cc4a14 100644
--- a/pg.c
+++ b/pg.c
@@ -9,7 +9,7 @@
 #include "pg_top.h"
 
 #define QUERY_PROCESSES \
-		"SELECT pid, query\n" \
+		"SELECT pid, query, state\n" \
 		"FROM pg_stat_activity;"
 
 #define QUERY_PROCESSES_9_1 \
diff --git a/pg.h b/pg.h
index e368351..2ea852b 100644
--- a/pg.h
+++ b/pg.h
@@ -11,4 +11,15 @@ PGresult   *pg_locks(PGconn *, int);
 PGresult   *pg_processes(PGconn *);
 PGresult   *pg_query(PGconn *, int);
 
+enum BackendState
+{
+    STATE_UNDEFINED,
+    STATE_IDLE,
+    STATE_RUNNING,
+    STATE_IDLEINTRANSACTION,
+    STATE_FASTPATH,
+    STATE_IDLEINTRANSACTION_ABORTED,
+    STATE_DISABLED
+};
+
 #endif   /* _PG_H_ */
diff --git a/pg_top.1.in b/pg_top.1.in
index 6cb1702..381ad83 100644
--- a/pg_top.1.in
+++ b/pg_top.1.in
@@ -321,8 +321,9 @@ Resident memory: current amount of process memory that resides in physical
 memory, given in kilobytes.
 .TP
 .B STATE
-Current state (typically one of \*(lqsleep\*(rq,
-\*(lqrun\*(rq, \*(lqidl\*(rq, \*(lqzomb\*(rq, or \*(lqstop\*(rq).
+Current backend state (typically one of \*(lqidle\*(rq,
+\*(lqactive\*(rq, \*(lqidltxn\*(rq, \*(lqfast\*(rq, \*(lqdisabl\*(eq, or
+\*(lqstop\*(rq).
 .TP
 .B TIME
 Number of system and user cpu seconds that the process has used.