1 /*-------------------------------------------------------------------------
4 * Functions for accessing the statistics collector data
6 * Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
7 * Portions Copyright (c) 1994, Regents of the University of California
11 * $PostgreSQL: pgsql/src/backend/utils/adt/pgstatfuncs.c,v 1.34 2006/10/04 00:29:59 momjian Exp $
13 *-------------------------------------------------------------------------
18 #include "miscadmin.h"
20 #include "utils/builtins.h"
21 #include "utils/inet.h"
24 /* bogus ... these externs should be in a header file */
25 extern Datum pg_stat_get_numscans(PG_FUNCTION_ARGS);
26 extern Datum pg_stat_get_tuples_returned(PG_FUNCTION_ARGS);
27 extern Datum pg_stat_get_tuples_fetched(PG_FUNCTION_ARGS);
28 extern Datum pg_stat_get_tuples_inserted(PG_FUNCTION_ARGS);
29 extern Datum pg_stat_get_tuples_updated(PG_FUNCTION_ARGS);
30 extern Datum pg_stat_get_tuples_deleted(PG_FUNCTION_ARGS);
31 extern Datum pg_stat_get_blocks_fetched(PG_FUNCTION_ARGS);
32 extern Datum pg_stat_get_blocks_hit(PG_FUNCTION_ARGS);
33 extern Datum pg_stat_get_last_vacuum_time(PG_FUNCTION_ARGS);
34 extern Datum pg_stat_get_last_autovacuum_time(PG_FUNCTION_ARGS);
35 extern Datum pg_stat_get_last_analyze_time(PG_FUNCTION_ARGS);
36 extern Datum pg_stat_get_last_autoanalyze_time(PG_FUNCTION_ARGS);
38 extern Datum pg_stat_get_backend_idset(PG_FUNCTION_ARGS);
39 extern Datum pg_backend_pid(PG_FUNCTION_ARGS);
40 extern Datum pg_stat_reset(PG_FUNCTION_ARGS);
41 extern Datum pg_stat_get_backend_pid(PG_FUNCTION_ARGS);
42 extern Datum pg_stat_get_backend_dbid(PG_FUNCTION_ARGS);
43 extern Datum pg_stat_get_backend_userid(PG_FUNCTION_ARGS);
44 extern Datum pg_stat_get_backend_activity(PG_FUNCTION_ARGS);
45 extern Datum pg_stat_get_backend_waiting(PG_FUNCTION_ARGS);
46 extern Datum pg_stat_get_backend_activity_start(PG_FUNCTION_ARGS);
47 extern Datum pg_stat_get_backend_start(PG_FUNCTION_ARGS);
48 extern Datum pg_stat_get_backend_client_addr(PG_FUNCTION_ARGS);
49 extern Datum pg_stat_get_backend_client_port(PG_FUNCTION_ARGS);
51 extern Datum pg_stat_get_db_numbackends(PG_FUNCTION_ARGS);
52 extern Datum pg_stat_get_db_xact_commit(PG_FUNCTION_ARGS);
53 extern Datum pg_stat_get_db_xact_rollback(PG_FUNCTION_ARGS);
54 extern Datum pg_stat_get_db_blocks_fetched(PG_FUNCTION_ARGS);
55 extern Datum pg_stat_get_db_blocks_hit(PG_FUNCTION_ARGS);
59 pg_stat_get_numscans(PG_FUNCTION_ARGS)
61 Oid relid = PG_GETARG_OID(0);
63 PgStat_StatTabEntry *tabentry;
65 if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
68 result = (int64) (tabentry->numscans);
70 PG_RETURN_INT64(result);
75 pg_stat_get_tuples_returned(PG_FUNCTION_ARGS)
77 Oid relid = PG_GETARG_OID(0);
79 PgStat_StatTabEntry *tabentry;
81 if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
84 result = (int64) (tabentry->tuples_returned);
86 PG_RETURN_INT64(result);
91 pg_stat_get_tuples_fetched(PG_FUNCTION_ARGS)
93 Oid relid = PG_GETARG_OID(0);
95 PgStat_StatTabEntry *tabentry;
97 if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
100 result = (int64) (tabentry->tuples_fetched);
102 PG_RETURN_INT64(result);
107 pg_stat_get_tuples_inserted(PG_FUNCTION_ARGS)
109 Oid relid = PG_GETARG_OID(0);
111 PgStat_StatTabEntry *tabentry;
113 if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
116 result = (int64) (tabentry->tuples_inserted);
118 PG_RETURN_INT64(result);
123 pg_stat_get_tuples_updated(PG_FUNCTION_ARGS)
125 Oid relid = PG_GETARG_OID(0);
127 PgStat_StatTabEntry *tabentry;
129 if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
132 result = (int64) (tabentry->tuples_updated);
134 PG_RETURN_INT64(result);
139 pg_stat_get_tuples_deleted(PG_FUNCTION_ARGS)
141 Oid relid = PG_GETARG_OID(0);
143 PgStat_StatTabEntry *tabentry;
145 if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
148 result = (int64) (tabentry->tuples_deleted);
150 PG_RETURN_INT64(result);
155 pg_stat_get_blocks_fetched(PG_FUNCTION_ARGS)
157 Oid relid = PG_GETARG_OID(0);
159 PgStat_StatTabEntry *tabentry;
161 if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
164 result = (int64) (tabentry->blocks_fetched);
166 PG_RETURN_INT64(result);
171 pg_stat_get_blocks_hit(PG_FUNCTION_ARGS)
173 Oid relid = PG_GETARG_OID(0);
175 PgStat_StatTabEntry *tabentry;
177 if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
180 result = (int64) (tabentry->blocks_hit);
182 PG_RETURN_INT64(result);
186 pg_stat_get_last_vacuum_time(PG_FUNCTION_ARGS)
188 Oid relid = PG_GETARG_OID(0);
190 PgStat_StatTabEntry *tabentry;
192 if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
195 result = tabentry->vacuum_timestamp;
200 PG_RETURN_TIMESTAMPTZ(result);
204 pg_stat_get_last_autovacuum_time(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 = tabentry->autovac_vacuum_timestamp;
218 PG_RETURN_TIMESTAMPTZ(result);
222 pg_stat_get_last_analyze_time(PG_FUNCTION_ARGS)
224 Oid relid = PG_GETARG_OID(0);
226 PgStat_StatTabEntry *tabentry;
228 if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
231 result = tabentry->analyze_timestamp;
236 PG_RETURN_TIMESTAMPTZ(result);
240 pg_stat_get_last_autoanalyze_time(PG_FUNCTION_ARGS)
242 Oid relid = PG_GETARG_OID(0);
244 PgStat_StatTabEntry *tabentry;
246 if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
249 result = tabentry->autovac_analyze_timestamp;
254 PG_RETURN_TIMESTAMPTZ(result);
258 pg_stat_get_backend_idset(PG_FUNCTION_ARGS)
260 FuncCallContext *funcctx;
264 /* stuff done only on the first call of the function */
265 if (SRF_IS_FIRSTCALL())
267 /* create a function context for cross-call persistence */
268 funcctx = SRF_FIRSTCALL_INIT();
270 fctx = MemoryContextAlloc(funcctx->multi_call_memory_ctx,
272 funcctx->user_fctx = fctx;
275 fctx[1] = pgstat_fetch_stat_numbackends();
278 /* stuff done on every call of the function */
279 funcctx = SRF_PERCALL_SETUP();
280 fctx = funcctx->user_fctx;
285 if (result <= fctx[1])
287 /* do when there is more left to send */
288 SRF_RETURN_NEXT(funcctx, Int32GetDatum(result));
292 /* do when there is no more left */
293 SRF_RETURN_DONE(funcctx);
299 pg_backend_pid(PG_FUNCTION_ARGS)
301 PG_RETURN_INT32(MyProcPid);
305 * Built-in function for resetting the counters
308 pg_stat_reset(PG_FUNCTION_ARGS)
310 pgstat_reset_counters();
312 PG_RETURN_BOOL(true);
316 pg_stat_get_backend_pid(PG_FUNCTION_ARGS)
318 int32 beid = PG_GETARG_INT32(0);
319 PgBackendStatus *beentry;
321 if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
324 PG_RETURN_INT32(beentry->st_procpid);
329 pg_stat_get_backend_dbid(PG_FUNCTION_ARGS)
331 int32 beid = PG_GETARG_INT32(0);
332 PgBackendStatus *beentry;
334 if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
337 PG_RETURN_OID(beentry->st_databaseid);
342 pg_stat_get_backend_userid(PG_FUNCTION_ARGS)
344 int32 beid = PG_GETARG_INT32(0);
345 PgBackendStatus *beentry;
347 if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
350 PG_RETURN_OID(beentry->st_userid);
355 pg_stat_get_backend_activity(PG_FUNCTION_ARGS)
357 int32 beid = PG_GETARG_INT32(0);
359 PgBackendStatus *beentry;
361 const char *activity;
363 if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
364 activity = "<backend information not available>";
365 else if (!superuser() && beentry->st_userid != GetUserId())
366 activity = "<insufficient privilege>";
367 else if (*(beentry->st_activity) == '\0')
368 activity = "<command string not enabled>";
370 activity = beentry->st_activity;
372 len = strlen(activity);
373 result = palloc(VARHDRSZ + len);
374 VARATT_SIZEP(result) = VARHDRSZ + len;
375 memcpy(VARDATA(result), activity, len);
377 PG_RETURN_TEXT_P(result);
382 pg_stat_get_backend_waiting(PG_FUNCTION_ARGS)
384 int32 beid = PG_GETARG_INT32(0);
386 PgBackendStatus *beentry;
388 if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
391 if (!superuser() && beentry->st_userid != GetUserId())
394 result = beentry->st_waiting;
396 PG_RETURN_BOOL(result);
401 pg_stat_get_backend_activity_start(PG_FUNCTION_ARGS)
403 int32 beid = PG_GETARG_INT32(0);
405 PgBackendStatus *beentry;
407 if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
410 if (!superuser() && beentry->st_userid != GetUserId())
413 result = beentry->st_activity_start_timestamp;
416 * No time recorded for start of current query -- this is the case if the
417 * user hasn't enabled query-level stats collection.
422 PG_RETURN_TIMESTAMPTZ(result);
426 pg_stat_get_backend_start(PG_FUNCTION_ARGS)
428 int32 beid = PG_GETARG_INT32(0);
430 PgBackendStatus *beentry;
432 if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
435 if (!superuser() && beentry->st_userid != GetUserId())
438 result = beentry->st_proc_start_timestamp;
440 if (result == 0) /* probably can't happen? */
443 PG_RETURN_TIMESTAMPTZ(result);
448 pg_stat_get_backend_client_addr(PG_FUNCTION_ARGS)
450 int32 beid = PG_GETARG_INT32(0);
451 PgBackendStatus *beentry;
452 SockAddr zero_clientaddr;
453 char remote_host[NI_MAXHOST];
456 if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
459 if (!superuser() && beentry->st_userid != GetUserId())
462 /* A zeroed client addr means we don't know */
463 memset(&zero_clientaddr, 0, sizeof(zero_clientaddr));
464 if (memcmp(&(beentry->st_clientaddr), &zero_clientaddr,
465 sizeof(zero_clientaddr) == 0))
468 switch (beentry->st_clientaddr.addr.ss_family)
479 remote_host[0] = '\0';
480 ret = pg_getnameinfo_all(&beentry->st_clientaddr.addr,
481 beentry->st_clientaddr.salen,
482 remote_host, sizeof(remote_host),
484 NI_NUMERICHOST | NI_NUMERICSERV);
488 PG_RETURN_INET_P(DirectFunctionCall1(inet_in,
489 CStringGetDatum(remote_host)));
493 pg_stat_get_backend_client_port(PG_FUNCTION_ARGS)
495 int32 beid = PG_GETARG_INT32(0);
496 PgBackendStatus *beentry;
497 SockAddr zero_clientaddr;
498 char remote_port[NI_MAXSERV];
501 if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
504 if (!superuser() && beentry->st_userid != GetUserId())
507 /* A zeroed client addr means we don't know */
508 memset(&zero_clientaddr, 0, sizeof(zero_clientaddr));
509 if (memcmp(&(beentry->st_clientaddr), &zero_clientaddr,
510 sizeof(zero_clientaddr) == 0))
513 switch (beentry->st_clientaddr.addr.ss_family)
526 remote_port[0] = '\0';
527 ret = pg_getnameinfo_all(&beentry->st_clientaddr.addr,
528 beentry->st_clientaddr.salen,
530 remote_port, sizeof(remote_port),
531 NI_NUMERICHOST | NI_NUMERICSERV);
535 PG_RETURN_DATUM(DirectFunctionCall1(int4in,
536 CStringGetDatum(remote_port)));
541 pg_stat_get_db_numbackends(PG_FUNCTION_ARGS)
543 Oid dbid = PG_GETARG_OID(0);
545 int tot_backends = pgstat_fetch_stat_numbackends();
549 for (beid = 1; beid <= tot_backends; beid++)
551 PgBackendStatus *beentry = pgstat_fetch_stat_beentry(beid);
553 if (beentry && beentry->st_databaseid == dbid)
557 PG_RETURN_INT32(result);
562 pg_stat_get_db_xact_commit(PG_FUNCTION_ARGS)
564 Oid dbid = PG_GETARG_OID(0);
566 PgStat_StatDBEntry *dbentry;
568 if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
571 result = (int64) (dbentry->n_xact_commit);
573 PG_RETURN_INT64(result);
578 pg_stat_get_db_xact_rollback(PG_FUNCTION_ARGS)
580 Oid dbid = PG_GETARG_OID(0);
582 PgStat_StatDBEntry *dbentry;
584 if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
587 result = (int64) (dbentry->n_xact_rollback);
589 PG_RETURN_INT64(result);
594 pg_stat_get_db_blocks_fetched(PG_FUNCTION_ARGS)
596 Oid dbid = PG_GETARG_OID(0);
598 PgStat_StatDBEntry *dbentry;
600 if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
603 result = (int64) (dbentry->n_blocks_fetched);
605 PG_RETURN_INT64(result);
610 pg_stat_get_db_blocks_hit(PG_FUNCTION_ARGS)
612 Oid dbid = PG_GETARG_OID(0);
614 PgStat_StatDBEntry *dbentry;
616 if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
619 result = (int64) (dbentry->n_blocks_hit);
621 PG_RETURN_INT64(result);