pgstat SRF?

Index: src/backend/utils/adt/pgstatfuncs.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/pgstatfuncs.c,v
retrieving revision 1.49
diff -c -r1.49 pgstatfuncs.c
*** src/backend/utils/adt/pgstatfuncs.c	25 Mar 2008 22:42:44 -0000	1.49
--- src/backend/utils/adt/pgstatfuncs.c	28 Apr 2008 13:36:57 -0000
***************
*** 17,22 ****
--- 17,24 ----
  #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,44 ****
--- 41,47 ----
  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,368 ****
--- 366,590 ----
  	}
  }
  
+ 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)
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.492
diff -c -r1.492 pg_proc.h
*** src/include/catalog/pg_proc.h	17 Apr 2008 20:56:41 -0000	1.492
--- src/include/catalog/pg_proc.h	28 Apr 2008 13:36:57 -0000
***************
*** 2899,2904 ****
--- 2899,2906 ----
  DESCR("statistics: last auto analyze time for a table");
  DATA(insert OID = 1936 (  pg_stat_get_backend_idset		PGNSP PGUID 12 1 100 f f t t s 0 23 "" _null_ _null_ _null_ pg_stat_get_backend_idset - _null_ _null_ ));
  DESCR("statistics: currently active backend IDs");
+ DATA(insert OID = 2022 (  pg_stat_get_activity			PGNSP PGUID 12 1 100 f f f t s 1 2249 "23" _null_ _null_ _null_ pg_stat_get_activity - _null_ _null_ ));
+ DESCR("statistics: information about currently active backends");
  DATA(insert OID = 2026 (  pg_backend_pid				PGNSP PGUID 12 1 0 f f t f s 0 23 "" _null_ _null_ _null_ pg_backend_pid - _null_ _null_ ));
  DESCR("statistics: current backend PID");
  DATA(insert OID = 1937 (  pg_stat_get_backend_pid		PGNSP PGUID 12 1 0 f f t f s 1 23 "23" _null_ _null_ _null_ pg_stat_get_backend_pid - _null_ _null_ ));