Make the pg_stat_activity view call a SRF (pg_stat_get_activity())

instead of calling a bunch of individual functions.

This function can also be called directly, taking a PID as an argument, to
return only the data for a single PID.

2 files changed, 242 insertions, 17 deletions

diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 0bbf48ac07..a6bb57a002 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -3,7 +3,7 @@
  *
  * Copyright (c) 1996-2008, PostgreSQL Global Development Group
  *
- * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.49 2008/03/10 12:55:13 mha Exp $
+ * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.50 2008/05/07 14:41:55 mha Exp $
  */
 
 CREATE VIEW pg_roles AS 
@@ -341,23 +341,26 @@ CREATE VIEW pg_statio_user_sequences AS
 
 CREATE VIEW pg_stat_activity AS 
     SELECT 
-            D.oid AS datid, 
-            D.datname AS datname, 
-            pg_stat_get_backend_pid(S.backendid) AS procpid, 
-            pg_stat_get_backend_userid(S.backendid) AS usesysid, 
-            U.rolname AS usename, 
-            pg_stat_get_backend_activity(S.backendid) AS current_query,
-            pg_stat_get_backend_waiting(S.backendid) AS waiting,
-            pg_stat_get_backend_xact_start(S.backendid) AS xact_start,
-            pg_stat_get_backend_activity_start(S.backendid) AS query_start,
-            pg_stat_get_backend_start(S.backendid) AS backend_start,
-            pg_stat_get_backend_client_addr(S.backendid) AS client_addr,
-            pg_stat_get_backend_client_port(S.backendid) AS client_port
+            S.datid AS datid,
+            D.datname AS datname,
+            S.procpid,
+            S.usesysid,
+            U.rolname AS usename,
+            S.current_query,
+            S.waiting,
+            S.xact_start,
+            S.query_start,
+            S.backend_start,
+            S.client_addr,
+            S.client_port
     FROM pg_database D, 
-            (SELECT pg_stat_get_backend_idset() AS backendid) AS S, 
+            pg_stat_get_activity(NULL) AS S(datid oid, procpid int,
+               usesysid oid, current_query text, waiting boolean,
+               xact_start timestamptz, query_start timestamptz,
+               backend_start timestamptz, client_addr inet, client_port int),
             pg_authid U 
-    WHERE pg_stat_get_backend_dbid(S.backendid) = D.oid AND 
-            pg_stat_get_backend_userid(S.backendid) = U.oid;
+    WHERE S.datid = D.oid AND 
+            S.usesysid = U.oid;
 
 CREATE VIEW pg_stat_database AS 
     SELECT 
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index f4c047b7ed..f31c2c5266 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/utils/adt/pgstatfuncs.c,v 1.49 2008/03/25 22:42:44 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/utils/adt/pgstatfuncs.c,v 1.50 2008/05/07 14:41:55 mha Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -17,6 +17,8 @@
 #include "funcapi.h"
 #include "miscadmin.h"
 #include "pgstat.h"
+#include "catalog/pg_type.h"
+#include "access/heapam.h"
 #include "utils/builtins.h"
 #include "utils/inet.h"
 #include "libpq/ip.h"
@@ -39,6 +41,7 @@ extern Datum pg_stat_get_last_analyze_time(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_last_autoanalyze_time(PG_FUNCTION_ARGS);
 
 extern Datum pg_stat_get_backend_idset(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_activity(PG_FUNCTION_ARGS);
 extern Datum pg_backend_pid(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_backend_pid(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_backend_dbid(PG_FUNCTION_ARGS);
@@ -363,6 +366,225 @@ pg_stat_get_backend_idset(PG_FUNCTION_ARGS)
 	}
 }
 
+Datum
+pg_stat_get_activity(PG_FUNCTION_ARGS)
+{
+	FuncCallContext *funcctx;
+
+	if (SRF_IS_FIRSTCALL())
+	{
+		MemoryContext oldcontext;
+		TupleDesc tupdesc;
+		
+		funcctx = SRF_FIRSTCALL_INIT();
+		
+		oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+		tupdesc = CreateTemplateTupleDesc(10, false);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 1, "datid", OIDOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 2, "procpid", INT4OID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 3, "usesysid", OIDOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 4, "current_query", TEXTOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 5, "waiting", BOOLOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 6, "act_start", TIMESTAMPTZOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 7, "query_start", TIMESTAMPTZOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 8, "backend_start", TIMESTAMPTZOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 9, "client_addr", INETOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 10, "client_port", INT4OID, -1, 0);
+
+		funcctx->tuple_desc = BlessTupleDesc(tupdesc);
+
+		funcctx->user_fctx = palloc0(sizeof(int));
+		if (PG_ARGISNULL(0))
+		{
+			/* Get all backends */
+			funcctx->max_calls = pgstat_fetch_stat_numbackends();
+		}
+		else
+		{
+			/* 
+			 * Get one backend - locate by pid.
+			 *
+			 * We lookup the backend early, so we can return zero rows if it doesn't
+			 * exist, instead of returning a single row full of NULLs.
+			 */
+			int		pid = PG_GETARG_INT32(0);
+			int		i;
+			int		n = pgstat_fetch_stat_numbackends();
+			
+			for (i = 1; i <= n; i++)
+			{
+				PgBackendStatus *be = pgstat_fetch_stat_beentry(i);
+				if (be)
+				{
+					if (be->st_procpid == pid)
+					{
+						*(int *)(funcctx->user_fctx) = i;
+						break;
+					}
+				}
+			}
+
+			if (*(int *)(funcctx->user_fctx) == 0)
+				/* Pid not found, return zero rows */
+				funcctx->max_calls = 0;
+			else
+				funcctx->max_calls = 1;
+		}
+		
+		MemoryContextSwitchTo(oldcontext);
+	}
+
+	/* stuff done on every call of the function */
+	funcctx = SRF_PERCALL_SETUP();
+
+	if (funcctx->call_cntr < funcctx->max_calls)
+	{
+		/* for each row */
+		Datum			values[10];
+		bool			nulls[10];
+		HeapTuple		tuple;
+		PgBackendStatus *beentry;
+		SockAddr		zero_clientaddr;
+
+		MemSet(values, 0, sizeof(values));
+		MemSet(nulls, 0, sizeof(nulls));
+		
+		if (*(int *)(funcctx->user_fctx) > 0)
+			/* Get specific pid slot */
+			beentry = pgstat_fetch_stat_beentry(*(int *)(funcctx->user_fctx));
+		else
+			/* Get the next one in the list */
+			beentry = pgstat_fetch_stat_beentry(funcctx->call_cntr+1); /* 1-based index */
+		if (!beentry)
+		{
+			int i;
+
+			for (i = 0; i < sizeof(nulls)/sizeof(nulls[0]); i++)
+				nulls[i] = true;
+
+			nulls[3] = false;
+			values[3] = CStringGetTextDatum("<backend information not available>");
+
+			tuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
+			SRF_RETURN_NEXT(funcctx, HeapTupleGetDatum(tuple));
+		}
+
+		/* Values available to all callers */
+		values[0] = ObjectIdGetDatum(beentry->st_databaseid);
+		values[1] = Int32GetDatum(beentry->st_procpid);
+		values[2] = ObjectIdGetDatum(beentry->st_userid);
+
+		/* Values only available to same user or superuser */
+		if (superuser() || beentry->st_userid == GetUserId())
+		{
+			if (*(beentry->st_activity) == '\0')
+			{
+				values[3] = CStringGetTextDatum("<command string not enabled>");
+			}
+			else
+			{
+				values[3] = CStringGetTextDatum(beentry->st_activity);
+			}
+
+			values[4] = BoolGetDatum(beentry->st_waiting);
+
+			if (beentry->st_xact_start_timestamp != 0)
+				values[5] = TimestampTzGetDatum(beentry->st_xact_start_timestamp);
+			else
+				nulls[5] = true;
+
+			if (beentry->st_activity_start_timestamp != 0)
+				values[6] = TimestampTzGetDatum(beentry->st_activity_start_timestamp);
+			else
+				nulls[6] = true;
+
+			if (beentry->st_proc_start_timestamp != 0)
+				values[7] = TimestampTzGetDatum(beentry->st_proc_start_timestamp);
+			else
+				nulls[7] = true;
+
+			/* A zeroed client addr means we don't know */
+			memset(&zero_clientaddr, 0, sizeof(zero_clientaddr));
+			if (memcmp(&(beentry->st_clientaddr), &zero_clientaddr,
+									  sizeof(zero_clientaddr) == 0))
+			{
+				nulls[8] = true;
+				nulls[9] = true;
+			}
+			else
+			{
+				if (beentry->st_clientaddr.addr.ss_family == AF_INET
+#ifdef HAVE_IPV6
+					|| beentry->st_clientaddr.addr.ss_family == AF_INET6
+#endif
+				   )
+				{
+					char        remote_host[NI_MAXHOST];
+					char		remote_port[NI_MAXSERV];
+					int			ret;
+
+					remote_host[0] = '\0';
+					remote_port[0] = '\0';
+					ret = pg_getnameinfo_all(&beentry->st_clientaddr.addr,
+											 beentry->st_clientaddr.salen,
+											 remote_host, sizeof(remote_host),
+											 remote_port, sizeof(remote_port),
+											 NI_NUMERICHOST | NI_NUMERICSERV);
+					if (ret)
+					{
+						nulls[8] = true;
+						nulls[9] = true;
+					}
+					else
+					{
+						clean_ipv6_addr(beentry->st_clientaddr.addr.ss_family, remote_host);
+						values[8] = DirectFunctionCall1(inet_in,
+													   CStringGetDatum(remote_host));
+						values[9] = Int32GetDatum(atoi(remote_port));
+					}
+				}
+				else if (beentry->st_clientaddr.addr.ss_family == AF_UNIX)
+				{
+					/*
+					 * Unix sockets always reports NULL for host and -1 for port, so it's
+					 * possible to tell the difference to connections we have no
+					 * permissions to view, or with errors.
+					 */
+					nulls[8] = true;
+					values[9] = DatumGetInt32(-1);
+				}
+				else
+				{
+					/* Unknown address type, should never happen */
+					nulls[8] = true;
+					nulls[9] = true;
+				}
+			}
+		}
+		else
+		{
+			/* No permissions to view data about this session */
+			values[3] = CStringGetTextDatum("<insufficient privilege>");
+			nulls[4] = true;
+			nulls[5] = true;
+			nulls[6] = true;
+			nulls[7] = true;
+			nulls[8] = true;
+			nulls[9] = true;
+		}
+
+		tuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
+
+		SRF_RETURN_NEXT(funcctx, HeapTupleGetDatum(tuple));
+	}
+	else
+	{
+		/* nothing left */
+		SRF_RETURN_DONE(funcctx);
+	}
+}
+
 
 Datum
 pg_backend_pid(PG_FUNCTION_ARGS)