Provide much better wait information in pg_stat_activity.

When a process is waiting for a heavyweight lock, we will now indicate
the type of heavyweight lock for which it is waiting.  Also, you can
now see when a process is waiting for a lightweight lock - in which
case we will indicate the individual lock name or the tranche, as
appropriate - or for a buffer pin.

Amit Kapila, Ildus Kurbangaliev, reviewed by me.  Lots of helpful
discussion and suggestions by many others, including Alexander
Korotkov, Vladimir Borodin, and many others.

1 files changed, 87 insertions, 39 deletions

diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 2fb51fa6788..0f6f891f8ac 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -20,6 +20,8 @@
 #include "libpq/ip.h"
 #include "miscadmin.h"
 #include "pgstat.h"
+#include "storage/proc.h"
+#include "storage/procarray.h"
 #include "utils/acl.h"
 #include "utils/builtins.h"
 #include "utils/inet.h"
@@ -58,7 +60,8 @@ extern Datum pg_stat_get_backend_pid(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_backend_dbid(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_backend_userid(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_backend_activity(PG_FUNCTION_ARGS);
-extern Datum pg_stat_get_backend_waiting(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_backend_wait_event_type(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_backend_wait_event(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_backend_activity_start(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_backend_xact_start(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_backend_start(PG_FUNCTION_ARGS);
@@ -633,7 +636,7 @@ pg_stat_get_progress_info(PG_FUNCTION_ARGS)
 Datum
 pg_stat_get_activity(PG_FUNCTION_ARGS)
 {
-#define PG_STAT_GET_ACTIVITY_COLS	22
+#define PG_STAT_GET_ACTIVITY_COLS	23
 	int			num_backends = pgstat_fetch_stat_numbackends();
 	int			curr_backend;
 	int			pid = PG_ARGISNULL(0) ? -1 : PG_GETARG_INT32(0);
@@ -676,6 +679,9 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
 		bool		nulls[PG_STAT_GET_ACTIVITY_COLS];
 		LocalPgBackendStatus *local_beentry;
 		PgBackendStatus *beentry;
+		PGPROC	   *proc;
+		const char *wait_event_type;
+		const char *wait_event;
 
 		MemSet(values, 0, sizeof(values));
 		MemSet(nulls, 0, sizeof(nulls));
@@ -720,28 +726,28 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
 			nulls[3] = true;
 
 		if (TransactionIdIsValid(local_beentry->backend_xid))
-			values[14] = TransactionIdGetDatum(local_beentry->backend_xid);
+			values[15] = TransactionIdGetDatum(local_beentry->backend_xid);
 		else
-			nulls[14] = true;
+			nulls[15] = true;
 
 		if (TransactionIdIsValid(local_beentry->backend_xmin))
-			values[15] = TransactionIdGetDatum(local_beentry->backend_xmin);
+			values[16] = TransactionIdGetDatum(local_beentry->backend_xmin);
 		else
-			nulls[15] = true;
+			nulls[16] = true;
 
 		if (beentry->st_ssl)
 		{
-			values[16] = BoolGetDatum(true);	/* ssl */
-			values[17] = CStringGetTextDatum(beentry->st_sslstatus->ssl_version);
-			values[18] = CStringGetTextDatum(beentry->st_sslstatus->ssl_cipher);
-			values[19] = Int32GetDatum(beentry->st_sslstatus->ssl_bits);
-			values[20] = BoolGetDatum(beentry->st_sslstatus->ssl_compression);
-			values[21] = CStringGetTextDatum(beentry->st_sslstatus->ssl_clientdn);
+			values[17] = BoolGetDatum(true);	/* ssl */
+			values[18] = CStringGetTextDatum(beentry->st_sslstatus->ssl_version);
+			values[19] = CStringGetTextDatum(beentry->st_sslstatus->ssl_cipher);
+			values[20] = Int32GetDatum(beentry->st_sslstatus->ssl_bits);
+			values[21] = BoolGetDatum(beentry->st_sslstatus->ssl_compression);
+			values[22] = CStringGetTextDatum(beentry->st_sslstatus->ssl_clientdn);
 		}
 		else
 		{
-			values[16] = BoolGetDatum(false);	/* ssl */
-			nulls[17] = nulls[18] = nulls[19] = nulls[20] = nulls[21] = true;
+			values[17] = BoolGetDatum(false);	/* ssl */
+			nulls[18] = nulls[19] = nulls[20] = nulls[21] = nulls[22] = true;
 		}
 
 		/* Values only available to role member */
@@ -775,36 +781,48 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
 			}
 
 			values[5] = CStringGetTextDatum(beentry->st_activity);
-			values[6] = BoolGetDatum(beentry->st_waiting);
 
-			if (beentry->st_xact_start_timestamp != 0)
-				values[7] = TimestampTzGetDatum(beentry->st_xact_start_timestamp);
+			proc = BackendPidGetProc(beentry->st_procpid);
+			wait_event_type = pgstat_get_wait_event_type(proc->wait_event_info);
+			if (wait_event_type)
+				values[6] = CStringGetTextDatum(wait_event_type);
+			else
+				nulls[6] = true;
+
+			wait_event = pgstat_get_wait_event(proc->wait_event_info);
+			if (wait_event)
+				values[7] = CStringGetTextDatum(wait_event);
 			else
 				nulls[7] = true;
 
-			if (beentry->st_activity_start_timestamp != 0)
-				values[8] = TimestampTzGetDatum(beentry->st_activity_start_timestamp);
+			if (beentry->st_xact_start_timestamp != 0)
+				values[8] = TimestampTzGetDatum(beentry->st_xact_start_timestamp);
 			else
 				nulls[8] = true;
 
-			if (beentry->st_proc_start_timestamp != 0)
-				values[9] = TimestampTzGetDatum(beentry->st_proc_start_timestamp);
+			if (beentry->st_activity_start_timestamp != 0)
+				values[9] = TimestampTzGetDatum(beentry->st_activity_start_timestamp);
 			else
 				nulls[9] = true;
 
-			if (beentry->st_state_start_timestamp != 0)
-				values[10] = TimestampTzGetDatum(beentry->st_state_start_timestamp);
+			if (beentry->st_proc_start_timestamp != 0)
+				values[10] = TimestampTzGetDatum(beentry->st_proc_start_timestamp);
 			else
 				nulls[10] = true;
 
+			if (beentry->st_state_start_timestamp != 0)
+				values[11] = TimestampTzGetDatum(beentry->st_state_start_timestamp);
+			else
+				nulls[11] = 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[11] = true;
 				nulls[12] = true;
 				nulls[13] = true;
+				nulls[14] = true;
 			}
 			else
 			{
@@ -828,20 +846,20 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
 					if (ret == 0)
 					{
 						clean_ipv6_addr(beentry->st_clientaddr.addr.ss_family, remote_host);
-						values[11] = DirectFunctionCall1(inet_in,
+						values[12] = DirectFunctionCall1(inet_in,
 											   CStringGetDatum(remote_host));
 						if (beentry->st_clienthostname &&
 							beentry->st_clienthostname[0])
-							values[12] = CStringGetTextDatum(beentry->st_clienthostname);
+							values[13] = CStringGetTextDatum(beentry->st_clienthostname);
 						else
-							nulls[12] = true;
-						values[13] = Int32GetDatum(atoi(remote_port));
+							nulls[13] = true;
+						values[14] = Int32GetDatum(atoi(remote_port));
 					}
 					else
 					{
-						nulls[11] = true;
 						nulls[12] = true;
 						nulls[13] = true;
+						nulls[14] = true;
 					}
 				}
 				else if (beentry->st_clientaddr.addr.ss_family == AF_UNIX)
@@ -852,16 +870,16 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
 					 * connections we have no permissions to view, or with
 					 * errors.
 					 */
-					nulls[11] = true;
 					nulls[12] = true;
-					values[13] = DatumGetInt32(-1);
+					nulls[13] = true;
+					values[14] = DatumGetInt32(-1);
 				}
 				else
 				{
 					/* Unknown address type, should never happen */
-					nulls[11] = true;
 					nulls[12] = true;
 					nulls[13] = true;
+					nulls[14] = true;
 				}
 			}
 		}
@@ -878,6 +896,7 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
 			nulls[11] = true;
 			nulls[12] = true;
 			nulls[13] = true;
+			nulls[14] = true;
 		}
 
 		tuplestore_putvalues(tupstore, tupdesc, values, nulls);
@@ -959,23 +978,52 @@ pg_stat_get_backend_activity(PG_FUNCTION_ARGS)
 	PG_RETURN_TEXT_P(cstring_to_text(activity));
 }
 
-
 Datum
-pg_stat_get_backend_waiting(PG_FUNCTION_ARGS)
+pg_stat_get_backend_wait_event_type(PG_FUNCTION_ARGS)
 {
 	int32		beid = PG_GETARG_INT32(0);
-	bool		result;
 	PgBackendStatus *beentry;
+	PGPROC	   *proc;
+	const char *wait_event_type;
 
 	if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
-		PG_RETURN_NULL();
+		wait_event_type = "<backend information not available>";
+	else if (!has_privs_of_role(GetUserId(), beentry->st_userid))
+		wait_event_type = "<insufficient privilege>";
+	else
+	{
+		proc = BackendPidGetProc(beentry->st_procpid);
+		wait_event_type = pgstat_get_wait_event_type(proc->wait_event_info);
+	}
 
-	if (!has_privs_of_role(GetUserId(), beentry->st_userid))
+	if (!wait_event_type)
 		PG_RETURN_NULL();
 
-	result = beentry->st_waiting;
+	PG_RETURN_TEXT_P(cstring_to_text(wait_event_type));
+}
+
+Datum
+pg_stat_get_backend_wait_event(PG_FUNCTION_ARGS)
+{
+	int32		beid = PG_GETARG_INT32(0);
+	PgBackendStatus *beentry;
+	PGPROC	   *proc;
+	const char *wait_event;
+
+	if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
+		wait_event = "<backend information not available>";
+	else if (!has_privs_of_role(GetUserId(), beentry->st_userid))
+		wait_event = "<insufficient privilege>";
+	else
+	{
+		proc = BackendPidGetProc(beentry->st_procpid);
+		wait_event = pgstat_get_wait_event(proc->wait_event_info);
+	}
+
+	if (!wait_event)
+		PG_RETURN_NULL();
 
-	PG_RETURN_BOOL(result);
+	PG_RETURN_TEXT_P(cstring_to_text(wait_event));
 }