1 /*-------------------------------------------------------------------------
4 * Functions for accessing the statistics collector data
6 * Portions Copyright (c) 1996-2012, PostgreSQL Global Development Group
7 * Portions Copyright (c) 1994, Regents of the University of California
11 * src/backend/utils/adt/pgstatfuncs.c
13 *-------------------------------------------------------------------------
17 #include "catalog/pg_type.h"
20 #include "miscadmin.h"
22 #include "utils/builtins.h"
23 #include "utils/inet.h"
24 #include "utils/timestamp.h"
26 /* bogus ... these externs should be in a header file */
27 extern Datum pg_stat_get_numscans(PG_FUNCTION_ARGS);
28 extern Datum pg_stat_get_tuples_returned(PG_FUNCTION_ARGS);
29 extern Datum pg_stat_get_tuples_fetched(PG_FUNCTION_ARGS);
30 extern Datum pg_stat_get_tuples_inserted(PG_FUNCTION_ARGS);
31 extern Datum pg_stat_get_tuples_updated(PG_FUNCTION_ARGS);
32 extern Datum pg_stat_get_tuples_deleted(PG_FUNCTION_ARGS);
33 extern Datum pg_stat_get_tuples_hot_updated(PG_FUNCTION_ARGS);
34 extern Datum pg_stat_get_live_tuples(PG_FUNCTION_ARGS);
35 extern Datum pg_stat_get_dead_tuples(PG_FUNCTION_ARGS);
36 extern Datum pg_stat_get_blocks_fetched(PG_FUNCTION_ARGS);
37 extern Datum pg_stat_get_blocks_hit(PG_FUNCTION_ARGS);
38 extern Datum pg_stat_get_last_vacuum_time(PG_FUNCTION_ARGS);
39 extern Datum pg_stat_get_last_autovacuum_time(PG_FUNCTION_ARGS);
40 extern Datum pg_stat_get_last_analyze_time(PG_FUNCTION_ARGS);
41 extern Datum pg_stat_get_last_autoanalyze_time(PG_FUNCTION_ARGS);
42 extern Datum pg_stat_get_vacuum_count(PG_FUNCTION_ARGS);
43 extern Datum pg_stat_get_autovacuum_count(PG_FUNCTION_ARGS);
44 extern Datum pg_stat_get_analyze_count(PG_FUNCTION_ARGS);
45 extern Datum pg_stat_get_autoanalyze_count(PG_FUNCTION_ARGS);
47 extern Datum pg_stat_get_function_calls(PG_FUNCTION_ARGS);
48 extern Datum pg_stat_get_function_time(PG_FUNCTION_ARGS);
49 extern Datum pg_stat_get_function_self_time(PG_FUNCTION_ARGS);
51 extern Datum pg_stat_get_backend_idset(PG_FUNCTION_ARGS);
52 extern Datum pg_stat_get_activity(PG_FUNCTION_ARGS);
53 extern Datum pg_backend_pid(PG_FUNCTION_ARGS);
54 extern Datum pg_stat_get_backend_pid(PG_FUNCTION_ARGS);
55 extern Datum pg_stat_get_backend_dbid(PG_FUNCTION_ARGS);
56 extern Datum pg_stat_get_backend_userid(PG_FUNCTION_ARGS);
57 extern Datum pg_stat_get_backend_activity(PG_FUNCTION_ARGS);
58 extern Datum pg_stat_get_backend_waiting(PG_FUNCTION_ARGS);
59 extern Datum pg_stat_get_backend_activity_start(PG_FUNCTION_ARGS);
60 extern Datum pg_stat_get_backend_xact_start(PG_FUNCTION_ARGS);
61 extern Datum pg_stat_get_backend_start(PG_FUNCTION_ARGS);
62 extern Datum pg_stat_get_backend_client_addr(PG_FUNCTION_ARGS);
63 extern Datum pg_stat_get_backend_client_port(PG_FUNCTION_ARGS);
65 extern Datum pg_stat_get_db_numbackends(PG_FUNCTION_ARGS);
66 extern Datum pg_stat_get_db_xact_commit(PG_FUNCTION_ARGS);
67 extern Datum pg_stat_get_db_xact_rollback(PG_FUNCTION_ARGS);
68 extern Datum pg_stat_get_db_blocks_fetched(PG_FUNCTION_ARGS);
69 extern Datum pg_stat_get_db_blocks_hit(PG_FUNCTION_ARGS);
70 extern Datum pg_stat_get_db_tuples_returned(PG_FUNCTION_ARGS);
71 extern Datum pg_stat_get_db_tuples_fetched(PG_FUNCTION_ARGS);
72 extern Datum pg_stat_get_db_tuples_inserted(PG_FUNCTION_ARGS);
73 extern Datum pg_stat_get_db_tuples_updated(PG_FUNCTION_ARGS);
74 extern Datum pg_stat_get_db_tuples_deleted(PG_FUNCTION_ARGS);
75 extern Datum pg_stat_get_db_conflict_tablespace(PG_FUNCTION_ARGS);
76 extern Datum pg_stat_get_db_conflict_lock(PG_FUNCTION_ARGS);
77 extern Datum pg_stat_get_db_conflict_snapshot(PG_FUNCTION_ARGS);
78 extern Datum pg_stat_get_db_conflict_bufferpin(PG_FUNCTION_ARGS);
79 extern Datum pg_stat_get_db_conflict_startup_deadlock(PG_FUNCTION_ARGS);
80 extern Datum pg_stat_get_db_conflict_all(PG_FUNCTION_ARGS);
81 extern Datum pg_stat_get_db_deadlocks(PG_FUNCTION_ARGS);
82 extern Datum pg_stat_get_db_stat_reset_time(PG_FUNCTION_ARGS);
83 extern Datum pg_stat_get_db_temp_files(PG_FUNCTION_ARGS);
84 extern Datum pg_stat_get_db_temp_bytes(PG_FUNCTION_ARGS);
85 extern Datum pg_stat_get_db_block_time_read(PG_FUNCTION_ARGS);
86 extern Datum pg_stat_get_db_block_time_write(PG_FUNCTION_ARGS);
88 extern Datum pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS);
89 extern Datum pg_stat_get_bgwriter_requested_checkpoints(PG_FUNCTION_ARGS);
90 extern Datum pg_stat_get_checkpoint_write_time(PG_FUNCTION_ARGS);
91 extern Datum pg_stat_get_checkpoint_sync_time(PG_FUNCTION_ARGS);
92 extern Datum pg_stat_get_bgwriter_buf_written_checkpoints(PG_FUNCTION_ARGS);
93 extern Datum pg_stat_get_bgwriter_buf_written_clean(PG_FUNCTION_ARGS);
94 extern Datum pg_stat_get_bgwriter_maxwritten_clean(PG_FUNCTION_ARGS);
95 extern Datum pg_stat_get_bgwriter_stat_reset_time(PG_FUNCTION_ARGS);
96 extern Datum pg_stat_get_buf_written_backend(PG_FUNCTION_ARGS);
97 extern Datum pg_stat_get_buf_fsync_backend(PG_FUNCTION_ARGS);
98 extern Datum pg_stat_get_buf_alloc(PG_FUNCTION_ARGS);
100 extern Datum pg_stat_get_xact_numscans(PG_FUNCTION_ARGS);
101 extern Datum pg_stat_get_xact_tuples_returned(PG_FUNCTION_ARGS);
102 extern Datum pg_stat_get_xact_tuples_fetched(PG_FUNCTION_ARGS);
103 extern Datum pg_stat_get_xact_tuples_inserted(PG_FUNCTION_ARGS);
104 extern Datum pg_stat_get_xact_tuples_updated(PG_FUNCTION_ARGS);
105 extern Datum pg_stat_get_xact_tuples_deleted(PG_FUNCTION_ARGS);
106 extern Datum pg_stat_get_xact_tuples_hot_updated(PG_FUNCTION_ARGS);
107 extern Datum pg_stat_get_xact_blocks_fetched(PG_FUNCTION_ARGS);
108 extern Datum pg_stat_get_xact_blocks_hit(PG_FUNCTION_ARGS);
110 extern Datum pg_stat_get_xact_function_calls(PG_FUNCTION_ARGS);
111 extern Datum pg_stat_get_xact_function_time(PG_FUNCTION_ARGS);
112 extern Datum pg_stat_get_xact_function_self_time(PG_FUNCTION_ARGS);
114 extern Datum pg_stat_clear_snapshot(PG_FUNCTION_ARGS);
115 extern Datum pg_stat_reset(PG_FUNCTION_ARGS);
116 extern Datum pg_stat_reset_shared(PG_FUNCTION_ARGS);
117 extern Datum pg_stat_reset_single_table_counters(PG_FUNCTION_ARGS);
118 extern Datum pg_stat_reset_single_function_counters(PG_FUNCTION_ARGS);
120 /* Global bgwriter statistics, from bgwriter.c */
121 extern PgStat_MsgBgWriter bgwriterStats;
124 pg_stat_get_numscans(PG_FUNCTION_ARGS)
126 Oid relid = PG_GETARG_OID(0);
128 PgStat_StatTabEntry *tabentry;
130 if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
133 result = (int64) (tabentry->numscans);
135 PG_RETURN_INT64(result);
140 pg_stat_get_tuples_returned(PG_FUNCTION_ARGS)
142 Oid relid = PG_GETARG_OID(0);
144 PgStat_StatTabEntry *tabentry;
146 if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
149 result = (int64) (tabentry->tuples_returned);
151 PG_RETURN_INT64(result);
156 pg_stat_get_tuples_fetched(PG_FUNCTION_ARGS)
158 Oid relid = PG_GETARG_OID(0);
160 PgStat_StatTabEntry *tabentry;
162 if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
165 result = (int64) (tabentry->tuples_fetched);
167 PG_RETURN_INT64(result);
172 pg_stat_get_tuples_inserted(PG_FUNCTION_ARGS)
174 Oid relid = PG_GETARG_OID(0);
176 PgStat_StatTabEntry *tabentry;
178 if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
181 result = (int64) (tabentry->tuples_inserted);
183 PG_RETURN_INT64(result);
188 pg_stat_get_tuples_updated(PG_FUNCTION_ARGS)
190 Oid relid = PG_GETARG_OID(0);
192 PgStat_StatTabEntry *tabentry;
194 if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
197 result = (int64) (tabentry->tuples_updated);
199 PG_RETURN_INT64(result);
204 pg_stat_get_tuples_deleted(PG_FUNCTION_ARGS)
206 Oid relid = PG_GETARG_OID(0);
208 PgStat_StatTabEntry *tabentry;
210 if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
213 result = (int64) (tabentry->tuples_deleted);
215 PG_RETURN_INT64(result);
220 pg_stat_get_tuples_hot_updated(PG_FUNCTION_ARGS)
222 Oid relid = PG_GETARG_OID(0);
224 PgStat_StatTabEntry *tabentry;
226 if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
229 result = (int64) (tabentry->tuples_hot_updated);
231 PG_RETURN_INT64(result);
236 pg_stat_get_live_tuples(PG_FUNCTION_ARGS)
238 Oid relid = PG_GETARG_OID(0);
240 PgStat_StatTabEntry *tabentry;
242 if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
245 result = (int64) (tabentry->n_live_tuples);
247 PG_RETURN_INT64(result);
252 pg_stat_get_dead_tuples(PG_FUNCTION_ARGS)
254 Oid relid = PG_GETARG_OID(0);
256 PgStat_StatTabEntry *tabentry;
258 if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
261 result = (int64) (tabentry->n_dead_tuples);
263 PG_RETURN_INT64(result);
268 pg_stat_get_blocks_fetched(PG_FUNCTION_ARGS)
270 Oid relid = PG_GETARG_OID(0);
272 PgStat_StatTabEntry *tabentry;
274 if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
277 result = (int64) (tabentry->blocks_fetched);
279 PG_RETURN_INT64(result);
284 pg_stat_get_blocks_hit(PG_FUNCTION_ARGS)
286 Oid relid = PG_GETARG_OID(0);
288 PgStat_StatTabEntry *tabentry;
290 if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
293 result = (int64) (tabentry->blocks_hit);
295 PG_RETURN_INT64(result);
299 pg_stat_get_last_vacuum_time(PG_FUNCTION_ARGS)
301 Oid relid = PG_GETARG_OID(0);
303 PgStat_StatTabEntry *tabentry;
305 if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
308 result = tabentry->vacuum_timestamp;
313 PG_RETURN_TIMESTAMPTZ(result);
317 pg_stat_get_last_autovacuum_time(PG_FUNCTION_ARGS)
319 Oid relid = PG_GETARG_OID(0);
321 PgStat_StatTabEntry *tabentry;
323 if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
326 result = tabentry->autovac_vacuum_timestamp;
331 PG_RETURN_TIMESTAMPTZ(result);
335 pg_stat_get_last_analyze_time(PG_FUNCTION_ARGS)
337 Oid relid = PG_GETARG_OID(0);
339 PgStat_StatTabEntry *tabentry;
341 if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
344 result = tabentry->analyze_timestamp;
349 PG_RETURN_TIMESTAMPTZ(result);
353 pg_stat_get_last_autoanalyze_time(PG_FUNCTION_ARGS)
355 Oid relid = PG_GETARG_OID(0);
357 PgStat_StatTabEntry *tabentry;
359 if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
362 result = tabentry->autovac_analyze_timestamp;
367 PG_RETURN_TIMESTAMPTZ(result);
371 pg_stat_get_vacuum_count(PG_FUNCTION_ARGS)
373 Oid relid = PG_GETARG_OID(0);
375 PgStat_StatTabEntry *tabentry;
377 if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
380 result = (int64) (tabentry->vacuum_count);
382 PG_RETURN_INT64(result);
386 pg_stat_get_autovacuum_count(PG_FUNCTION_ARGS)
388 Oid relid = PG_GETARG_OID(0);
390 PgStat_StatTabEntry *tabentry;
392 if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
395 result = (int64) (tabentry->autovac_vacuum_count);
397 PG_RETURN_INT64(result);
401 pg_stat_get_analyze_count(PG_FUNCTION_ARGS)
403 Oid relid = PG_GETARG_OID(0);
405 PgStat_StatTabEntry *tabentry;
407 if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
410 result = (int64) (tabentry->analyze_count);
412 PG_RETURN_INT64(result);
416 pg_stat_get_autoanalyze_count(PG_FUNCTION_ARGS)
418 Oid relid = PG_GETARG_OID(0);
420 PgStat_StatTabEntry *tabentry;
422 if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
425 result = (int64) (tabentry->autovac_analyze_count);
427 PG_RETURN_INT64(result);
431 pg_stat_get_function_calls(PG_FUNCTION_ARGS)
433 Oid funcid = PG_GETARG_OID(0);
434 PgStat_StatFuncEntry *funcentry;
436 if ((funcentry = pgstat_fetch_stat_funcentry(funcid)) == NULL)
438 PG_RETURN_INT64(funcentry->f_numcalls);
442 pg_stat_get_function_time(PG_FUNCTION_ARGS)
444 Oid funcid = PG_GETARG_OID(0);
445 PgStat_StatFuncEntry *funcentry;
447 if ((funcentry = pgstat_fetch_stat_funcentry(funcid)) == NULL)
449 PG_RETURN_INT64(funcentry->f_time);
453 pg_stat_get_function_self_time(PG_FUNCTION_ARGS)
455 Oid funcid = PG_GETARG_OID(0);
456 PgStat_StatFuncEntry *funcentry;
458 if ((funcentry = pgstat_fetch_stat_funcentry(funcid)) == NULL)
460 PG_RETURN_INT64(funcentry->f_time_self);
464 pg_stat_get_backend_idset(PG_FUNCTION_ARGS)
466 FuncCallContext *funcctx;
470 /* stuff done only on the first call of the function */
471 if (SRF_IS_FIRSTCALL())
473 /* create a function context for cross-call persistence */
474 funcctx = SRF_FIRSTCALL_INIT();
476 fctx = MemoryContextAlloc(funcctx->multi_call_memory_ctx,
478 funcctx->user_fctx = fctx;
481 fctx[1] = pgstat_fetch_stat_numbackends();
484 /* stuff done on every call of the function */
485 funcctx = SRF_PERCALL_SETUP();
486 fctx = funcctx->user_fctx;
491 if (result <= fctx[1])
493 /* do when there is more left to send */
494 SRF_RETURN_NEXT(funcctx, Int32GetDatum(result));
498 /* do when there is no more left */
499 SRF_RETURN_DONE(funcctx);
504 pg_stat_get_activity(PG_FUNCTION_ARGS)
506 FuncCallContext *funcctx;
508 if (SRF_IS_FIRSTCALL())
510 MemoryContext oldcontext;
513 funcctx = SRF_FIRSTCALL_INIT();
515 oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
517 tupdesc = CreateTemplateTupleDesc(14, false);
518 TupleDescInitEntry(tupdesc, (AttrNumber) 1, "datid",
520 TupleDescInitEntry(tupdesc, (AttrNumber) 2, "pid",
522 TupleDescInitEntry(tupdesc, (AttrNumber) 3, "usesysid",
524 TupleDescInitEntry(tupdesc, (AttrNumber) 4, "application_name",
526 TupleDescInitEntry(tupdesc, (AttrNumber) 5, "state",
528 TupleDescInitEntry(tupdesc, (AttrNumber) 6, "query",
530 TupleDescInitEntry(tupdesc, (AttrNumber) 7, "waiting",
532 TupleDescInitEntry(tupdesc, (AttrNumber) 8, "act_start",
533 TIMESTAMPTZOID, -1, 0);
534 TupleDescInitEntry(tupdesc, (AttrNumber) 9, "query_start",
535 TIMESTAMPTZOID, -1, 0);
536 TupleDescInitEntry(tupdesc, (AttrNumber) 10, "backend_start",
537 TIMESTAMPTZOID, -1, 0);
538 TupleDescInitEntry(tupdesc, (AttrNumber) 11, "state_change",
539 TIMESTAMPTZOID, -1, 0);
540 TupleDescInitEntry(tupdesc, (AttrNumber) 12, "client_addr",
542 TupleDescInitEntry(tupdesc, (AttrNumber) 13, "client_hostname",
544 TupleDescInitEntry(tupdesc, (AttrNumber) 14, "client_port",
547 funcctx->tuple_desc = BlessTupleDesc(tupdesc);
549 funcctx->user_fctx = palloc0(sizeof(int));
552 /* Get all backends */
553 funcctx->max_calls = pgstat_fetch_stat_numbackends();
558 * Get one backend - locate by pid.
560 * We lookup the backend early, so we can return zero rows if it
561 * doesn't exist, instead of returning a single row full of NULLs.
563 int pid = PG_GETARG_INT32(0);
565 int n = pgstat_fetch_stat_numbackends();
567 for (i = 1; i <= n; i++)
569 PgBackendStatus *be = pgstat_fetch_stat_beentry(i);
573 if (be->st_procpid == pid)
575 *(int *) (funcctx->user_fctx) = i;
581 if (*(int *) (funcctx->user_fctx) == 0)
582 /* Pid not found, return zero rows */
583 funcctx->max_calls = 0;
585 funcctx->max_calls = 1;
588 MemoryContextSwitchTo(oldcontext);
591 /* stuff done on every call of the function */
592 funcctx = SRF_PERCALL_SETUP();
594 if (funcctx->call_cntr < funcctx->max_calls)
600 PgBackendStatus *beentry;
601 SockAddr zero_clientaddr;
603 MemSet(values, 0, sizeof(values));
604 MemSet(nulls, 0, sizeof(nulls));
606 if (*(int *) (funcctx->user_fctx) > 0)
608 /* Get specific pid slot */
609 beentry = pgstat_fetch_stat_beentry(*(int *) (funcctx->user_fctx));
613 /* Get the next one in the list */
614 beentry = pgstat_fetch_stat_beentry(funcctx->call_cntr + 1); /* 1-based index */
620 for (i = 0; i < sizeof(nulls) / sizeof(nulls[0]); i++)
624 values[5] = CStringGetTextDatum("<backend information not available>");
626 tuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
627 SRF_RETURN_NEXT(funcctx, HeapTupleGetDatum(tuple));
630 /* Values available to all callers */
631 values[0] = ObjectIdGetDatum(beentry->st_databaseid);
632 values[1] = Int32GetDatum(beentry->st_procpid);
633 values[2] = ObjectIdGetDatum(beentry->st_userid);
634 if (beentry->st_appname)
635 values[3] = CStringGetTextDatum(beentry->st_appname);
639 /* Values only available to same user or superuser */
640 if (superuser() || beentry->st_userid == GetUserId())
642 switch (beentry->st_state)
645 values[4] = CStringGetTextDatum("idle");
648 values[4] = CStringGetTextDatum("active");
650 case STATE_IDLEINTRANSACTION:
651 values[4] = CStringGetTextDatum("idle in transaction");
654 values[4] = CStringGetTextDatum("fastpath function call");
656 case STATE_IDLEINTRANSACTION_ABORTED:
657 values[4] = CStringGetTextDatum("idle in transaction (aborted)");
660 values[4] = CStringGetTextDatum("disabled");
662 case STATE_UNDEFINED:
666 if (beentry->st_state == STATE_UNDEFINED ||
667 beentry->st_state == STATE_DISABLED)
669 values[5] = CStringGetTextDatum("");
673 values[5] = CStringGetTextDatum(beentry->st_activity);
675 values[6] = BoolGetDatum(beentry->st_waiting);
677 if (beentry->st_xact_start_timestamp != 0)
678 values[7] = TimestampTzGetDatum(beentry->st_xact_start_timestamp);
682 if (beentry->st_activity_start_timestamp != 0)
683 values[8] = TimestampTzGetDatum(beentry->st_activity_start_timestamp);
687 if (beentry->st_proc_start_timestamp != 0)
688 values[9] = TimestampTzGetDatum(beentry->st_proc_start_timestamp);
692 if (beentry->st_state_start_timestamp != 0)
693 values[10] = TimestampTzGetDatum(beentry->st_state_start_timestamp);
697 /* A zeroed client addr means we don't know */
698 memset(&zero_clientaddr, 0, sizeof(zero_clientaddr));
699 if (memcmp(&(beentry->st_clientaddr), &zero_clientaddr,
700 sizeof(zero_clientaddr) == 0))
708 if (beentry->st_clientaddr.addr.ss_family == AF_INET
710 || beentry->st_clientaddr.addr.ss_family == AF_INET6
714 char remote_host[NI_MAXHOST];
715 char remote_port[NI_MAXSERV];
718 remote_host[0] = '\0';
719 remote_port[0] = '\0';
720 ret = pg_getnameinfo_all(&beentry->st_clientaddr.addr,
721 beentry->st_clientaddr.salen,
722 remote_host, sizeof(remote_host),
723 remote_port, sizeof(remote_port),
724 NI_NUMERICHOST | NI_NUMERICSERV);
727 clean_ipv6_addr(beentry->st_clientaddr.addr.ss_family, remote_host);
728 values[11] = DirectFunctionCall1(inet_in,
729 CStringGetDatum(remote_host));
730 if (beentry->st_clienthostname)
731 values[12] = CStringGetTextDatum(beentry->st_clienthostname);
734 values[13] = Int32GetDatum(atoi(remote_port));
743 else if (beentry->st_clientaddr.addr.ss_family == AF_UNIX)
746 * Unix sockets always reports NULL for host and -1 for
747 * port, so it's possible to tell the difference to
748 * connections we have no permissions to view, or with
753 values[13] = DatumGetInt32(-1);
757 /* Unknown address type, should never happen */
766 /* No permissions to view data about this session */
767 values[5] = CStringGetTextDatum("<insufficient privilege>");
779 tuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
781 SRF_RETURN_NEXT(funcctx, HeapTupleGetDatum(tuple));
786 SRF_RETURN_DONE(funcctx);
792 pg_backend_pid(PG_FUNCTION_ARGS)
794 PG_RETURN_INT32(MyProcPid);
799 pg_stat_get_backend_pid(PG_FUNCTION_ARGS)
801 int32 beid = PG_GETARG_INT32(0);
802 PgBackendStatus *beentry;
804 if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
807 PG_RETURN_INT32(beentry->st_procpid);
812 pg_stat_get_backend_dbid(PG_FUNCTION_ARGS)
814 int32 beid = PG_GETARG_INT32(0);
815 PgBackendStatus *beentry;
817 if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
820 PG_RETURN_OID(beentry->st_databaseid);
825 pg_stat_get_backend_userid(PG_FUNCTION_ARGS)
827 int32 beid = PG_GETARG_INT32(0);
828 PgBackendStatus *beentry;
830 if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
833 PG_RETURN_OID(beentry->st_userid);
838 pg_stat_get_backend_activity(PG_FUNCTION_ARGS)
840 int32 beid = PG_GETARG_INT32(0);
841 PgBackendStatus *beentry;
842 const char *activity;
844 if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
845 activity = "<backend information not available>";
846 else if (!superuser() && beentry->st_userid != GetUserId())
847 activity = "<insufficient privilege>";
848 else if (*(beentry->st_activity) == '\0')
849 activity = "<command string not enabled>";
851 activity = beentry->st_activity;
853 PG_RETURN_TEXT_P(cstring_to_text(activity));
858 pg_stat_get_backend_waiting(PG_FUNCTION_ARGS)
860 int32 beid = PG_GETARG_INT32(0);
862 PgBackendStatus *beentry;
864 if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
867 if (!superuser() && beentry->st_userid != GetUserId())
870 result = beentry->st_waiting;
872 PG_RETURN_BOOL(result);
877 pg_stat_get_backend_activity_start(PG_FUNCTION_ARGS)
879 int32 beid = PG_GETARG_INT32(0);
881 PgBackendStatus *beentry;
883 if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
886 if (!superuser() && beentry->st_userid != GetUserId())
889 result = beentry->st_activity_start_timestamp;
892 * No time recorded for start of current query -- this is the case if the
893 * user hasn't enabled query-level stats collection.
898 PG_RETURN_TIMESTAMPTZ(result);
903 pg_stat_get_backend_xact_start(PG_FUNCTION_ARGS)
905 int32 beid = PG_GETARG_INT32(0);
907 PgBackendStatus *beentry;
909 if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
912 if (!superuser() && beentry->st_userid != GetUserId())
915 result = beentry->st_xact_start_timestamp;
917 if (result == 0) /* not in a transaction */
920 PG_RETURN_TIMESTAMPTZ(result);
925 pg_stat_get_backend_start(PG_FUNCTION_ARGS)
927 int32 beid = PG_GETARG_INT32(0);
929 PgBackendStatus *beentry;
931 if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
934 if (!superuser() && beentry->st_userid != GetUserId())
937 result = beentry->st_proc_start_timestamp;
939 if (result == 0) /* probably can't happen? */
942 PG_RETURN_TIMESTAMPTZ(result);
947 pg_stat_get_backend_client_addr(PG_FUNCTION_ARGS)
949 int32 beid = PG_GETARG_INT32(0);
950 PgBackendStatus *beentry;
951 SockAddr zero_clientaddr;
952 char remote_host[NI_MAXHOST];
955 if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
958 if (!superuser() && beentry->st_userid != GetUserId())
961 /* A zeroed client addr means we don't know */
962 memset(&zero_clientaddr, 0, sizeof(zero_clientaddr));
963 if (memcmp(&(beentry->st_clientaddr), &zero_clientaddr,
964 sizeof(zero_clientaddr) == 0))
967 switch (beentry->st_clientaddr.addr.ss_family)
978 remote_host[0] = '\0';
979 ret = pg_getnameinfo_all(&beentry->st_clientaddr.addr,
980 beentry->st_clientaddr.salen,
981 remote_host, sizeof(remote_host),
983 NI_NUMERICHOST | NI_NUMERICSERV);
987 clean_ipv6_addr(beentry->st_clientaddr.addr.ss_family, remote_host);
989 PG_RETURN_INET_P(DirectFunctionCall1(inet_in,
990 CStringGetDatum(remote_host)));
994 pg_stat_get_backend_client_port(PG_FUNCTION_ARGS)
996 int32 beid = PG_GETARG_INT32(0);
997 PgBackendStatus *beentry;
998 SockAddr zero_clientaddr;
999 char remote_port[NI_MAXSERV];
1002 if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
1005 if (!superuser() && beentry->st_userid != GetUserId())
1008 /* A zeroed client addr means we don't know */
1009 memset(&zero_clientaddr, 0, sizeof(zero_clientaddr));
1010 if (memcmp(&(beentry->st_clientaddr), &zero_clientaddr,
1011 sizeof(zero_clientaddr) == 0))
1014 switch (beentry->st_clientaddr.addr.ss_family)
1022 PG_RETURN_INT32(-1);
1027 remote_port[0] = '\0';
1028 ret = pg_getnameinfo_all(&beentry->st_clientaddr.addr,
1029 beentry->st_clientaddr.salen,
1031 remote_port, sizeof(remote_port),
1032 NI_NUMERICHOST | NI_NUMERICSERV);
1036 PG_RETURN_DATUM(DirectFunctionCall1(int4in,
1037 CStringGetDatum(remote_port)));
1042 pg_stat_get_db_numbackends(PG_FUNCTION_ARGS)
1044 Oid dbid = PG_GETARG_OID(0);
1046 int tot_backends = pgstat_fetch_stat_numbackends();
1050 for (beid = 1; beid <= tot_backends; beid++)
1052 PgBackendStatus *beentry = pgstat_fetch_stat_beentry(beid);
1054 if (beentry && beentry->st_databaseid == dbid)
1058 PG_RETURN_INT32(result);
1063 pg_stat_get_db_xact_commit(PG_FUNCTION_ARGS)
1065 Oid dbid = PG_GETARG_OID(0);
1067 PgStat_StatDBEntry *dbentry;
1069 if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
1072 result = (int64) (dbentry->n_xact_commit);
1074 PG_RETURN_INT64(result);
1079 pg_stat_get_db_xact_rollback(PG_FUNCTION_ARGS)
1081 Oid dbid = PG_GETARG_OID(0);
1083 PgStat_StatDBEntry *dbentry;
1085 if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
1088 result = (int64) (dbentry->n_xact_rollback);
1090 PG_RETURN_INT64(result);
1095 pg_stat_get_db_blocks_fetched(PG_FUNCTION_ARGS)
1097 Oid dbid = PG_GETARG_OID(0);
1099 PgStat_StatDBEntry *dbentry;
1101 if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
1104 result = (int64) (dbentry->n_blocks_fetched);
1106 PG_RETURN_INT64(result);
1111 pg_stat_get_db_blocks_hit(PG_FUNCTION_ARGS)
1113 Oid dbid = PG_GETARG_OID(0);
1115 PgStat_StatDBEntry *dbentry;
1117 if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
1120 result = (int64) (dbentry->n_blocks_hit);
1122 PG_RETURN_INT64(result);
1127 pg_stat_get_db_tuples_returned(PG_FUNCTION_ARGS)
1129 Oid dbid = PG_GETARG_OID(0);
1131 PgStat_StatDBEntry *dbentry;
1133 if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
1136 result = (int64) (dbentry->n_tuples_returned);
1138 PG_RETURN_INT64(result);
1143 pg_stat_get_db_tuples_fetched(PG_FUNCTION_ARGS)
1145 Oid dbid = PG_GETARG_OID(0);
1147 PgStat_StatDBEntry *dbentry;
1149 if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
1152 result = (int64) (dbentry->n_tuples_fetched);
1154 PG_RETURN_INT64(result);
1159 pg_stat_get_db_tuples_inserted(PG_FUNCTION_ARGS)
1161 Oid dbid = PG_GETARG_OID(0);
1163 PgStat_StatDBEntry *dbentry;
1165 if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
1168 result = (int64) (dbentry->n_tuples_inserted);
1170 PG_RETURN_INT64(result);
1175 pg_stat_get_db_tuples_updated(PG_FUNCTION_ARGS)
1177 Oid dbid = PG_GETARG_OID(0);
1179 PgStat_StatDBEntry *dbentry;
1181 if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
1184 result = (int64) (dbentry->n_tuples_updated);
1186 PG_RETURN_INT64(result);
1191 pg_stat_get_db_tuples_deleted(PG_FUNCTION_ARGS)
1193 Oid dbid = PG_GETARG_OID(0);
1195 PgStat_StatDBEntry *dbentry;
1197 if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
1200 result = (int64) (dbentry->n_tuples_deleted);
1202 PG_RETURN_INT64(result);
1206 pg_stat_get_db_stat_reset_time(PG_FUNCTION_ARGS)
1208 Oid dbid = PG_GETARG_OID(0);
1210 PgStat_StatDBEntry *dbentry;
1212 if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
1215 result = dbentry->stat_reset_timestamp;
1220 PG_RETURN_TIMESTAMPTZ(result);
1224 pg_stat_get_db_temp_files(PG_FUNCTION_ARGS)
1226 Oid dbid = PG_GETARG_OID(0);
1228 PgStat_StatDBEntry *dbentry;
1230 if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
1233 result = dbentry->n_temp_files;
1235 PG_RETURN_INT64(result);
1240 pg_stat_get_db_temp_bytes(PG_FUNCTION_ARGS)
1242 Oid dbid = PG_GETARG_OID(0);
1244 PgStat_StatDBEntry *dbentry;
1246 if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
1249 result = dbentry->n_temp_bytes;
1251 PG_RETURN_INT64(result);
1255 pg_stat_get_db_conflict_tablespace(PG_FUNCTION_ARGS)
1257 Oid dbid = PG_GETARG_OID(0);
1259 PgStat_StatDBEntry *dbentry;
1261 if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
1264 result = (int64) (dbentry->n_conflict_tablespace);
1266 PG_RETURN_INT64(result);
1270 pg_stat_get_db_conflict_lock(PG_FUNCTION_ARGS)
1272 Oid dbid = PG_GETARG_OID(0);
1274 PgStat_StatDBEntry *dbentry;
1276 if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
1279 result = (int64) (dbentry->n_conflict_lock);
1281 PG_RETURN_INT64(result);
1285 pg_stat_get_db_conflict_snapshot(PG_FUNCTION_ARGS)
1287 Oid dbid = PG_GETARG_OID(0);
1289 PgStat_StatDBEntry *dbentry;
1291 if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
1294 result = (int64) (dbentry->n_conflict_snapshot);
1296 PG_RETURN_INT64(result);
1300 pg_stat_get_db_conflict_bufferpin(PG_FUNCTION_ARGS)
1302 Oid dbid = PG_GETARG_OID(0);
1304 PgStat_StatDBEntry *dbentry;
1306 if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
1309 result = (int64) (dbentry->n_conflict_bufferpin);
1311 PG_RETURN_INT64(result);
1315 pg_stat_get_db_conflict_startup_deadlock(PG_FUNCTION_ARGS)
1317 Oid dbid = PG_GETARG_OID(0);
1319 PgStat_StatDBEntry *dbentry;
1321 if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
1324 result = (int64) (dbentry->n_conflict_startup_deadlock);
1326 PG_RETURN_INT64(result);
1330 pg_stat_get_db_conflict_all(PG_FUNCTION_ARGS)
1332 Oid dbid = PG_GETARG_OID(0);
1334 PgStat_StatDBEntry *dbentry;
1336 if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
1340 dbentry->n_conflict_tablespace +
1341 dbentry->n_conflict_lock +
1342 dbentry->n_conflict_snapshot +
1343 dbentry->n_conflict_bufferpin +
1344 dbentry->n_conflict_startup_deadlock);
1346 PG_RETURN_INT64(result);
1350 pg_stat_get_db_deadlocks(PG_FUNCTION_ARGS)
1352 Oid dbid = PG_GETARG_OID(0);
1354 PgStat_StatDBEntry *dbentry;
1356 if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
1359 result = (int64) (dbentry->n_deadlocks);
1361 PG_RETURN_INT64(result);
1365 pg_stat_get_db_block_time_read(PG_FUNCTION_ARGS)
1367 Oid dbid = PG_GETARG_OID(0);
1369 PgStat_StatDBEntry *dbentry;
1371 if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
1374 result = (int64) (dbentry->n_block_time_read);
1376 PG_RETURN_INT64(result);
1380 pg_stat_get_db_block_time_write(PG_FUNCTION_ARGS)
1382 Oid dbid = PG_GETARG_OID(0);
1384 PgStat_StatDBEntry *dbentry;
1386 if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
1389 result = (int64) (dbentry->n_block_time_write);
1391 PG_RETURN_INT64(result);
1395 pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS)
1397 PG_RETURN_INT64(pgstat_fetch_global()->timed_checkpoints);
1401 pg_stat_get_bgwriter_requested_checkpoints(PG_FUNCTION_ARGS)
1403 PG_RETURN_INT64(pgstat_fetch_global()->requested_checkpoints);
1407 pg_stat_get_bgwriter_buf_written_checkpoints(PG_FUNCTION_ARGS)
1409 PG_RETURN_INT64(pgstat_fetch_global()->buf_written_checkpoints);
1413 pg_stat_get_bgwriter_buf_written_clean(PG_FUNCTION_ARGS)
1415 PG_RETURN_INT64(pgstat_fetch_global()->buf_written_clean);
1419 pg_stat_get_bgwriter_maxwritten_clean(PG_FUNCTION_ARGS)
1421 PG_RETURN_INT64(pgstat_fetch_global()->maxwritten_clean);
1425 pg_stat_get_checkpoint_write_time(PG_FUNCTION_ARGS)
1427 PG_RETURN_INT64(pgstat_fetch_global()->checkpoint_write_time);
1431 pg_stat_get_checkpoint_sync_time(PG_FUNCTION_ARGS)
1433 PG_RETURN_INT64(pgstat_fetch_global()->checkpoint_sync_time);
1437 pg_stat_get_bgwriter_stat_reset_time(PG_FUNCTION_ARGS)
1439 PG_RETURN_TIMESTAMPTZ(pgstat_fetch_global()->stat_reset_timestamp);
1443 pg_stat_get_buf_written_backend(PG_FUNCTION_ARGS)
1445 PG_RETURN_INT64(pgstat_fetch_global()->buf_written_backend);
1449 pg_stat_get_buf_fsync_backend(PG_FUNCTION_ARGS)
1451 PG_RETURN_INT64(pgstat_fetch_global()->buf_fsync_backend);
1455 pg_stat_get_buf_alloc(PG_FUNCTION_ARGS)
1457 PG_RETURN_INT64(pgstat_fetch_global()->buf_alloc);
1461 pg_stat_get_xact_numscans(PG_FUNCTION_ARGS)
1463 Oid relid = PG_GETARG_OID(0);
1465 PgStat_TableStatus *tabentry;
1467 if ((tabentry = find_tabstat_entry(relid)) == NULL)
1470 result = (int64) (tabentry->t_counts.t_numscans);
1472 PG_RETURN_INT64(result);
1476 pg_stat_get_xact_tuples_returned(PG_FUNCTION_ARGS)
1478 Oid relid = PG_GETARG_OID(0);
1480 PgStat_TableStatus *tabentry;
1482 if ((tabentry = find_tabstat_entry(relid)) == NULL)
1485 result = (int64) (tabentry->t_counts.t_tuples_returned);
1487 PG_RETURN_INT64(result);
1491 pg_stat_get_xact_tuples_fetched(PG_FUNCTION_ARGS)
1493 Oid relid = PG_GETARG_OID(0);
1495 PgStat_TableStatus *tabentry;
1497 if ((tabentry = find_tabstat_entry(relid)) == NULL)
1500 result = (int64) (tabentry->t_counts.t_tuples_fetched);
1502 PG_RETURN_INT64(result);
1506 pg_stat_get_xact_tuples_inserted(PG_FUNCTION_ARGS)
1508 Oid relid = PG_GETARG_OID(0);
1510 PgStat_TableStatus *tabentry;
1511 PgStat_TableXactStatus *trans;
1513 if ((tabentry = find_tabstat_entry(relid)) == NULL)
1517 result = tabentry->t_counts.t_tuples_inserted;
1518 /* live subtransactions' counts aren't in t_tuples_inserted yet */
1519 for (trans = tabentry->trans; trans != NULL; trans = trans->upper)
1520 result += trans->tuples_inserted;
1523 PG_RETURN_INT64(result);
1527 pg_stat_get_xact_tuples_updated(PG_FUNCTION_ARGS)
1529 Oid relid = PG_GETARG_OID(0);
1531 PgStat_TableStatus *tabentry;
1532 PgStat_TableXactStatus *trans;
1534 if ((tabentry = find_tabstat_entry(relid)) == NULL)
1538 result = tabentry->t_counts.t_tuples_updated;
1539 /* live subtransactions' counts aren't in t_tuples_updated yet */
1540 for (trans = tabentry->trans; trans != NULL; trans = trans->upper)
1541 result += trans->tuples_updated;
1544 PG_RETURN_INT64(result);
1548 pg_stat_get_xact_tuples_deleted(PG_FUNCTION_ARGS)
1550 Oid relid = PG_GETARG_OID(0);
1552 PgStat_TableStatus *tabentry;
1553 PgStat_TableXactStatus *trans;
1555 if ((tabentry = find_tabstat_entry(relid)) == NULL)
1559 result = tabentry->t_counts.t_tuples_deleted;
1560 /* live subtransactions' counts aren't in t_tuples_deleted yet */
1561 for (trans = tabentry->trans; trans != NULL; trans = trans->upper)
1562 result += trans->tuples_deleted;
1565 PG_RETURN_INT64(result);
1569 pg_stat_get_xact_tuples_hot_updated(PG_FUNCTION_ARGS)
1571 Oid relid = PG_GETARG_OID(0);
1573 PgStat_TableStatus *tabentry;
1575 if ((tabentry = find_tabstat_entry(relid)) == NULL)
1578 result = (int64) (tabentry->t_counts.t_tuples_hot_updated);
1580 PG_RETURN_INT64(result);
1584 pg_stat_get_xact_blocks_fetched(PG_FUNCTION_ARGS)
1586 Oid relid = PG_GETARG_OID(0);
1588 PgStat_TableStatus *tabentry;
1590 if ((tabentry = find_tabstat_entry(relid)) == NULL)
1593 result = (int64) (tabentry->t_counts.t_blocks_fetched);
1595 PG_RETURN_INT64(result);
1599 pg_stat_get_xact_blocks_hit(PG_FUNCTION_ARGS)
1601 Oid relid = PG_GETARG_OID(0);
1603 PgStat_TableStatus *tabentry;
1605 if ((tabentry = find_tabstat_entry(relid)) == NULL)
1608 result = (int64) (tabentry->t_counts.t_blocks_hit);
1610 PG_RETURN_INT64(result);
1614 pg_stat_get_xact_function_calls(PG_FUNCTION_ARGS)
1616 Oid funcid = PG_GETARG_OID(0);
1617 PgStat_BackendFunctionEntry *funcentry;
1619 if ((funcentry = find_funcstat_entry(funcid)) == NULL)
1621 PG_RETURN_INT64(funcentry->f_counts.f_numcalls);
1625 pg_stat_get_xact_function_time(PG_FUNCTION_ARGS)
1627 Oid funcid = PG_GETARG_OID(0);
1628 PgStat_BackendFunctionEntry *funcentry;
1630 if ((funcentry = find_funcstat_entry(funcid)) == NULL)
1632 PG_RETURN_INT64(INSTR_TIME_GET_MICROSEC(funcentry->f_counts.f_time));
1636 pg_stat_get_xact_function_self_time(PG_FUNCTION_ARGS)
1638 Oid funcid = PG_GETARG_OID(0);
1639 PgStat_BackendFunctionEntry *funcentry;
1641 if ((funcentry = find_funcstat_entry(funcid)) == NULL)
1643 PG_RETURN_INT64(INSTR_TIME_GET_MICROSEC(funcentry->f_counts.f_time_self));
1647 /* Discard the active statistics snapshot */
1649 pg_stat_clear_snapshot(PG_FUNCTION_ARGS)
1651 pgstat_clear_snapshot();
1657 /* Reset all counters for the current database */
1659 pg_stat_reset(PG_FUNCTION_ARGS)
1661 pgstat_reset_counters();
1666 /* Reset some shared cluster-wide counters */
1668 pg_stat_reset_shared(PG_FUNCTION_ARGS)
1670 char *target = text_to_cstring(PG_GETARG_TEXT_PP(0));
1672 pgstat_reset_shared_counters(target);
1677 /* Reset a a single counter in the current database */
1679 pg_stat_reset_single_table_counters(PG_FUNCTION_ARGS)
1681 Oid taboid = PG_GETARG_OID(0);
1683 pgstat_reset_single_counter(taboid, RESET_TABLE);
1689 pg_stat_reset_single_function_counters(PG_FUNCTION_ARGS)
1691 Oid funcoid = PG_GETARG_OID(0);
1693 pgstat_reset_single_counter(funcoid, RESET_FUNCTION);