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(16, false); TupleDescInitEntry(tupdesc, (AttrNumber) 1, "datid", OIDOID, -1, 0); TupleDescInitEntry(tupdesc, (AttrNumber) 2, "pid", INT4OID, -1, 0); TupleDescInitEntry(tupdesc, (AttrNumber) 3, "usesysid", OIDOID, -1, 0); TupleDescInitEntry(tupdesc, (AttrNumber) 4, "application_name", TEXTOID, -1, 0); TupleDescInitEntry(tupdesc, (AttrNumber) 5, "state", TEXTOID, -1, 0); TupleDescInitEntry(tupdesc, (AttrNumber) 6, "query", TEXTOID, -1, 0); TupleDescInitEntry(tupdesc, (AttrNumber) 7, "waiting", BOOLOID, -1, 0); TupleDescInitEntry(tupdesc, (AttrNumber) 8, "act_start", TIMESTAMPTZOID, -1, 0); TupleDescInitEntry(tupdesc, (AttrNumber) 9, "query_start", TIMESTAMPTZOID, -1, 0); TupleDescInitEntry(tupdesc, (AttrNumber) 10, "backend_start", TIMESTAMPTZOID, -1, 0); TupleDescInitEntry(tupdesc, (AttrNumber) 11, "state_change", TIMESTAMPTZOID, -1, 0); TupleDescInitEntry(tupdesc, (AttrNumber) 12, "client_addr", INETOID, -1, 0); TupleDescInitEntry(tupdesc, (AttrNumber) 13, "client_hostname", TEXTOID, -1, 0); TupleDescInitEntry(tupdesc, (AttrNumber) 14, "client_port", INT4OID, -1, 0); TupleDescInitEntry(tupdesc, (AttrNumber) 15, "backend_xid", XIDOID, -1, 0); TupleDescInitEntry(tupdesc, (AttrNumber) 16, "backend_xmin", XIDOID, -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[16]; bool nulls[16]; HeapTuple tuple; LocalPgBackendStatus *local_beentry; PgBackendStatus *beentry; MemSet(values, 0, sizeof(values)); MemSet(nulls, 0, sizeof(nulls)); if (*(int *) (funcctx->user_fctx) > 0) { /* Get specific pid slot */ local_beentry = pgstat_fetch_stat_local_beentry(*(int *) (funcctx->user_fctx)); beentry = &local_beentry->backendStatus; } else { /* Get the next one in the list */ local_beentry = pgstat_fetch_stat_local_beentry(funcctx->call_cntr + 1); /* 1-based index */ beentry = &local_beentry->backendStatus; } if (!beentry) { int i; for (i = 0; i < sizeof(nulls) / sizeof(nulls[0]); i++) nulls[i] = true; nulls[5] = false; values[5] = 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); if (beentry->st_appname) values[3] = CStringGetTextDatum(beentry->st_appname); else nulls[3] = true; if (TransactionIdIsValid(local_beentry->backend_xid)) values[14] = TransactionIdGetDatum(local_beentry->backend_xid); else nulls[14] = true; if (TransactionIdIsValid(local_beentry->backend_xmin)) values[15] = TransactionIdGetDatum(local_beentry->backend_xmin); else nulls[15] = true; /* Values only available to same user or superuser */ if (superuser() || beentry->st_userid == GetUserId()) { SockAddr zero_clientaddr; switch (beentry->st_state) { case STATE_IDLE: values[4] = CStringGetTextDatum("idle"); break; case STATE_RUNNING: values[4] = CStringGetTextDatum("active"); break; case STATE_IDLEINTRANSACTION: values[4] = CStringGetTextDatum("idle in transaction"); break; case STATE_FASTPATH: values[4] = CStringGetTextDatum("fastpath function call"); break; case STATE_IDLEINTRANSACTION_ABORTED: values[4] = CStringGetTextDatum("idle in transaction (aborted)"); break; case STATE_DISABLED: values[4] = CStringGetTextDatum("disabled"); break; case STATE_UNDEFINED: nulls[4] = true; break; } 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); else nulls[7] = true; if (beentry->st_activity_start_timestamp != 0) values[8] = TimestampTzGetDatum(beentry->st_activity_start_timestamp); else nulls[8] = true; if (beentry->st_proc_start_timestamp != 0) values[9] = TimestampTzGetDatum(beentry->st_proc_start_timestamp); else nulls[9] = true; if (beentry->st_state_start_timestamp != 0) values[10] = TimestampTzGetDatum(beentry->st_state_start_timestamp); else nulls[10] = 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; } 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 == 0) { clean_ipv6_addr(beentry->st_clientaddr.addr.ss_family, remote_host); values[11] = DirectFunctionCall1(inet_in, CStringGetDatum(remote_host)); if (beentry->st_clienthostname && beentry->st_clienthostname[0]) values[12] = CStringGetTextDatum(beentry->st_clienthostname); else nulls[12] = true; values[13] = Int32GetDatum(atoi(remote_port)); } else { nulls[11] = true; nulls[12] = true; nulls[13] = true; } } 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[11] = true; nulls[12] = true; values[13] = DatumGetInt32(-1); } else { /* Unknown address type, should never happen */ nulls[11] = true; nulls[12] = true; nulls[13] = true; } } } else { /* No permissions to view data about this session */ values[5] = CStringGetTextDatum("<insufficient privilege>"); nulls[4] = true; nulls[6] = true; nulls[7] = true; nulls[8] = true; nulls[9] = true; nulls[10] = true; nulls[11] = true; nulls[12] = true; nulls[13] = true; } tuple = heap_form_tuple(funcctx->tuple_desc, values, nulls); SRF_RETURN_NEXT(funcctx, HeapTupleGetDatum(tuple)); } else { /* nothing left */ SRF_RETURN_DONE(funcctx); } }
/* * Returns activity of PG backends. */ Datum pg_stat_get_activity(PG_FUNCTION_ARGS) { #define PG_STAT_GET_ACTIVITY_COLS 22 int num_backends = pgstat_fetch_stat_numbackends(); int curr_backend; int pid = PG_ARGISNULL(0) ? -1 : PG_GETARG_INT32(0); ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; TupleDesc tupdesc; Tuplestorestate *tupstore; MemoryContext per_query_ctx; MemoryContext oldcontext; /* check to see if caller supports us returning a tuplestore */ if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo)) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("set-valued function called in context that cannot accept a set"))); if (!(rsinfo->allowedModes & SFRM_Materialize)) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("materialize mode required, but it is not " \ "allowed in this context"))); /* Build a tuple descriptor for our result type */ if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) elog(ERROR, "return type must be a row type"); per_query_ctx = rsinfo->econtext->ecxt_per_query_memory; oldcontext = MemoryContextSwitchTo(per_query_ctx); tupstore = tuplestore_begin_heap(true, false, work_mem); rsinfo->returnMode = SFRM_Materialize; rsinfo->setResult = tupstore; rsinfo->setDesc = tupdesc; MemoryContextSwitchTo(oldcontext); /* 1-based index */ for (curr_backend = 1; curr_backend <= num_backends; curr_backend++) { /* for each row */ Datum values[PG_STAT_GET_ACTIVITY_COLS]; bool nulls[PG_STAT_GET_ACTIVITY_COLS]; LocalPgBackendStatus *local_beentry; PgBackendStatus *beentry; MemSet(values, 0, sizeof(values)); MemSet(nulls, 0, sizeof(nulls)); if (pid != -1) { /* Skip any which are not the one we're looking for. */ PgBackendStatus *be = pgstat_fetch_stat_beentry(curr_backend); if (!be || be->st_procpid != pid) continue; } /* Get the next one in the list */ local_beentry = pgstat_fetch_stat_local_beentry(curr_backend); if (!local_beentry) continue; beentry = &local_beentry->backendStatus; if (!beentry) { int i; for (i = 0; i < sizeof(nulls) / sizeof(nulls[0]); i++) nulls[i] = true; nulls[5] = false; values[5] = CStringGetTextDatum("<backend information not available>"); tuplestore_putvalues(tupstore, tupdesc, values, nulls); continue; } /* Values available to all callers */ values[0] = ObjectIdGetDatum(beentry->st_databaseid); values[1] = Int32GetDatum(beentry->st_procpid); values[2] = ObjectIdGetDatum(beentry->st_userid); if (beentry->st_appname) values[3] = CStringGetTextDatum(beentry->st_appname); else nulls[3] = true; if (TransactionIdIsValid(local_beentry->backend_xid)) values[14] = TransactionIdGetDatum(local_beentry->backend_xid); else nulls[14] = true; if (TransactionIdIsValid(local_beentry->backend_xmin)) values[15] = TransactionIdGetDatum(local_beentry->backend_xmin); else nulls[15] = 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); } else { values[16] = BoolGetDatum(false); /* ssl */ nulls[17] = nulls[18] = nulls[19] = nulls[20] = nulls[21] = true; } /* Values only available to role member */ if (has_privs_of_role(GetUserId(), beentry->st_userid)) { SockAddr zero_clientaddr; switch (beentry->st_state) { case STATE_IDLE: values[4] = CStringGetTextDatum("idle"); break; case STATE_RUNNING: values[4] = CStringGetTextDatum("active"); break; case STATE_IDLEINTRANSACTION: values[4] = CStringGetTextDatum("idle in transaction"); break; case STATE_FASTPATH: values[4] = CStringGetTextDatum("fastpath function call"); break; case STATE_IDLEINTRANSACTION_ABORTED: values[4] = CStringGetTextDatum("idle in transaction (aborted)"); break; case STATE_DISABLED: values[4] = CStringGetTextDatum("disabled"); break; case STATE_UNDEFINED: nulls[4] = true; break; } 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); else nulls[7] = true; if (beentry->st_activity_start_timestamp != 0) values[8] = TimestampTzGetDatum(beentry->st_activity_start_timestamp); else nulls[8] = true; if (beentry->st_proc_start_timestamp != 0) values[9] = TimestampTzGetDatum(beentry->st_proc_start_timestamp); else nulls[9] = true; if (beentry->st_state_start_timestamp != 0) values[10] = TimestampTzGetDatum(beentry->st_state_start_timestamp); else nulls[10] = 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; } 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 == 0) { clean_ipv6_addr(beentry->st_clientaddr.addr.ss_family, remote_host); values[11] = DirectFunctionCall1(inet_in, CStringGetDatum(remote_host)); if (beentry->st_clienthostname && beentry->st_clienthostname[0]) values[12] = CStringGetTextDatum(beentry->st_clienthostname); else nulls[12] = true; values[13] = Int32GetDatum(atoi(remote_port)); } else { nulls[11] = true; nulls[12] = true; nulls[13] = true; } } 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[11] = true; nulls[12] = true; values[13] = DatumGetInt32(-1); } else { /* Unknown address type, should never happen */ nulls[11] = true; nulls[12] = true; nulls[13] = true; } } } else { /* No permissions to view data about this session */ values[5] = CStringGetTextDatum("<insufficient privilege>"); nulls[4] = true; nulls[6] = true; nulls[7] = true; nulls[8] = true; nulls[9] = true; nulls[10] = true; nulls[11] = true; nulls[12] = true; nulls[13] = true; } tuplestore_putvalues(tupstore, tupdesc, values, nulls); /* If only a single backend was requested, and we found it, break. */ if (pid != -1) break; } /* clean up and return the tuplestore */ tuplestore_donestoring(tupstore); return (Datum) 0; }
/* * Returns command progress information for the named command. */ Datum pg_stat_get_progress_info(PG_FUNCTION_ARGS) { #define PG_STAT_GET_PROGRESS_COLS PGSTAT_NUM_PROGRESS_PARAM + 3 int num_backends = pgstat_fetch_stat_numbackends(); int curr_backend; char *cmd = text_to_cstring(PG_GETARG_TEXT_PP(0)); ProgressCommandType cmdtype; TupleDesc tupdesc; Tuplestorestate *tupstore; ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; MemoryContext per_query_ctx; MemoryContext oldcontext; /* check to see if caller supports us returning a tuplestore */ if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo)) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("set-valued function called in context that cannot accept a set"))); if (!(rsinfo->allowedModes & SFRM_Materialize)) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("materialize mode required, but it is not " \ "allowed in this context"))); /* Build a tuple descriptor for our result type */ if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) elog(ERROR, "return type must be a row type"); /* Translate command name into command type code. */ if (pg_strcasecmp(cmd, "VACUUM") == 0) cmdtype = PROGRESS_COMMAND_VACUUM; else ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("invalid command name: \"%s\"", cmd))); per_query_ctx = rsinfo->econtext->ecxt_per_query_memory; oldcontext = MemoryContextSwitchTo(per_query_ctx); tupstore = tuplestore_begin_heap(true, false, work_mem); rsinfo->returnMode = SFRM_Materialize; rsinfo->setResult = tupstore; rsinfo->setDesc = tupdesc; MemoryContextSwitchTo(oldcontext); /* 1-based index */ for (curr_backend = 1; curr_backend <= num_backends; curr_backend++) { LocalPgBackendStatus *local_beentry; PgBackendStatus *beentry; Datum values[PG_STAT_GET_PROGRESS_COLS]; bool nulls[PG_STAT_GET_PROGRESS_COLS]; int i; MemSet(values, 0, sizeof(values)); MemSet(nulls, 0, sizeof(nulls)); local_beentry = pgstat_fetch_stat_local_beentry(curr_backend); if (!local_beentry) continue; beentry = &local_beentry->backendStatus; /* * Report values for only those backends which are running the given * command. */ if (!beentry || beentry->st_progress_command != cmdtype) continue; /* Value available to all callers */ values[0] = Int32GetDatum(beentry->st_procpid); values[1] = ObjectIdGetDatum(beentry->st_databaseid); /* show rest of the values including relid only to role members */ if (has_privs_of_role(GetUserId(), beentry->st_userid)) { values[2] = ObjectIdGetDatum(beentry->st_progress_command_target); for(i = 0; i < PGSTAT_NUM_PROGRESS_PARAM; i++) values[i+3] = UInt32GetDatum(beentry->st_progress_param[i]); } else { nulls[2] = true; for (i = 0; i < PGSTAT_NUM_PROGRESS_PARAM; i++) nulls[i+3] = true; } tuplestore_putvalues(tupstore, tupdesc, values, nulls); } /* clean up and return the tuplestore */ tuplestore_donestoring(tupstore); return (Datum) 0; }