granicus.if.org Git - postgresql/blob - src/backend/utils/adt/pgstatfuncs.c

   1 /*-------------------------------------------------------------------------
   2  *
   3  * pgstatfuncs.c
   4  *        Functions for accessing the statistics collector data
   5  *
   6  * Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
   7  * Portions Copyright (c) 1994, Regents of the University of California
   8  *
   9  *
  10  * IDENTIFICATION
  11  *        $PostgreSQL: pgsql/src/backend/utils/adt/pgstatfuncs.c,v 1.30 2006/06/19 01:51:21 tgl Exp $
  12  *
  13  *-------------------------------------------------------------------------
  14  */
  15 #include "postgres.h"
  16
  17 #include "access/xact.h"
  18 #include "fmgr.h"
  19 #include "funcapi.h"
  20 #include "miscadmin.h"
  21 #include "nodes/execnodes.h"
  22 #include "pgstat.h"
  23 #include "utils/hsearch.h"
  24 #include "utils/inet.h"
  25 #include "utils/builtins.h"
  26 #include "libpq/ip.h"
  27
  28 /* bogus ... these externs should be in a header file */
  29 extern Datum pg_stat_get_numscans(PG_FUNCTION_ARGS);
  30 extern Datum pg_stat_get_tuples_returned(PG_FUNCTION_ARGS);
  31 extern Datum pg_stat_get_tuples_fetched(PG_FUNCTION_ARGS);
  32 extern Datum pg_stat_get_tuples_inserted(PG_FUNCTION_ARGS);
  33 extern Datum pg_stat_get_tuples_updated(PG_FUNCTION_ARGS);
  34 extern Datum pg_stat_get_tuples_deleted(PG_FUNCTION_ARGS);
  35 extern Datum pg_stat_get_blocks_fetched(PG_FUNCTION_ARGS);
  36 extern Datum pg_stat_get_blocks_hit(PG_FUNCTION_ARGS);
  37 extern Datum pg_stat_get_last_vacuum_time(PG_FUNCTION_ARGS);
  38 extern Datum pg_stat_get_last_autovacuum_time(PG_FUNCTION_ARGS);
  39 extern Datum pg_stat_get_last_analyze_time(PG_FUNCTION_ARGS);
  40 extern Datum pg_stat_get_last_autoanalyze_time(PG_FUNCTION_ARGS);
  41
  42 extern Datum pg_stat_get_backend_idset(PG_FUNCTION_ARGS);
  43 extern Datum pg_backend_pid(PG_FUNCTION_ARGS);
  44 extern Datum pg_stat_reset(PG_FUNCTION_ARGS);
  45 extern Datum pg_stat_get_backend_pid(PG_FUNCTION_ARGS);
  46 extern Datum pg_stat_get_backend_dbid(PG_FUNCTION_ARGS);
  47 extern Datum pg_stat_get_backend_userid(PG_FUNCTION_ARGS);
  48 extern Datum pg_stat_get_backend_activity(PG_FUNCTION_ARGS);
  49 extern Datum pg_stat_get_backend_activity_start(PG_FUNCTION_ARGS);
  50 extern Datum pg_stat_get_backend_start(PG_FUNCTION_ARGS);
  51 extern Datum pg_stat_get_backend_client_addr(PG_FUNCTION_ARGS);
  52 extern Datum pg_stat_get_backend_client_port(PG_FUNCTION_ARGS);
  53
  54 extern Datum pg_stat_get_db_numbackends(PG_FUNCTION_ARGS);
  55 extern Datum pg_stat_get_db_xact_commit(PG_FUNCTION_ARGS);
  56 extern Datum pg_stat_get_db_xact_rollback(PG_FUNCTION_ARGS);
  57 extern Datum pg_stat_get_db_blocks_fetched(PG_FUNCTION_ARGS);
  58 extern Datum pg_stat_get_db_blocks_hit(PG_FUNCTION_ARGS);
  59
  60
  61 Datum
  62 pg_stat_get_numscans(PG_FUNCTION_ARGS)
  63 {
  64         Oid                     relid = PG_GETARG_OID(0);
  65         int64           result;
  66         PgStat_StatTabEntry *tabentry;
  67
  68         if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
  69                 result = 0;
  70         else
  71                 result = (int64) (tabentry->numscans);
  72
  73         PG_RETURN_INT64(result);
  74 }
  75
  76
  77 Datum
  78 pg_stat_get_tuples_returned(PG_FUNCTION_ARGS)
  79 {
  80         Oid                     relid = PG_GETARG_OID(0);
  81         int64           result;
  82         PgStat_StatTabEntry *tabentry;
  83
  84         if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
  85                 result = 0;
  86         else
  87                 result = (int64) (tabentry->tuples_returned);
  88
  89         PG_RETURN_INT64(result);
  90 }
  91
  92
  93 Datum
  94 pg_stat_get_tuples_fetched(PG_FUNCTION_ARGS)
  95 {
  96         Oid                     relid = PG_GETARG_OID(0);
  97         int64           result;
  98         PgStat_StatTabEntry *tabentry;
  99
 100         if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
 101                 result = 0;
 102         else
 103                 result = (int64) (tabentry->tuples_fetched);
 104
 105         PG_RETURN_INT64(result);
 106 }
 107
 108
 109 Datum
 110 pg_stat_get_tuples_inserted(PG_FUNCTION_ARGS)
 111 {
 112         Oid                     relid = PG_GETARG_OID(0);
 113         int64           result;
 114         PgStat_StatTabEntry *tabentry;
 115
 116         if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
 117                 result = 0;
 118         else
 119                 result = (int64) (tabentry->tuples_inserted);
 120
 121         PG_RETURN_INT64(result);
 122 }
 123
 124
 125 Datum
 126 pg_stat_get_tuples_updated(PG_FUNCTION_ARGS)
 127 {
 128         Oid                     relid = PG_GETARG_OID(0);
 129         int64           result;
 130         PgStat_StatTabEntry *tabentry;
 131
 132         if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
 133                 result = 0;
 134         else
 135                 result = (int64) (tabentry->tuples_updated);
 136
 137         PG_RETURN_INT64(result);
 138 }
 139
 140
 141 Datum
 142 pg_stat_get_tuples_deleted(PG_FUNCTION_ARGS)
 143 {
 144         Oid                     relid = PG_GETARG_OID(0);
 145         int64           result;
 146         PgStat_StatTabEntry *tabentry;
 147
 148         if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
 149                 result = 0;
 150         else
 151                 result = (int64) (tabentry->tuples_deleted);
 152
 153         PG_RETURN_INT64(result);
 154 }
 155
 156
 157 Datum
 158 pg_stat_get_blocks_fetched(PG_FUNCTION_ARGS)
 159 {
 160         Oid                     relid = PG_GETARG_OID(0);
 161         int64           result;
 162         PgStat_StatTabEntry *tabentry;
 163
 164         if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
 165                 result = 0;
 166         else
 167                 result = (int64) (tabentry->blocks_fetched);
 168
 169         PG_RETURN_INT64(result);
 170 }
 171
 172
 173 Datum
 174 pg_stat_get_blocks_hit(PG_FUNCTION_ARGS)
 175 {
 176         Oid                     relid = PG_GETARG_OID(0);
 177         int64           result;
 178         PgStat_StatTabEntry *tabentry;
 179
 180         if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
 181                 result = 0;
 182         else
 183                 result = (int64) (tabentry->blocks_hit);
 184
 185         PG_RETURN_INT64(result);
 186 }
 187
 188 Datum
 189 pg_stat_get_last_vacuum_time(PG_FUNCTION_ARGS)
 190 {
 191         Oid                     relid = PG_GETARG_OID(0);
 192         TimestampTz     result;
 193         PgStat_StatTabEntry *tabentry;
 194
 195         if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
 196                 result = 0;
 197         else
 198                 result = tabentry->vacuum_timestamp;
 199
 200         if (result == 0)
 201                 PG_RETURN_NULL();
 202         else
 203                 PG_RETURN_TIMESTAMPTZ(result);
 204 }
 205
 206 Datum
 207 pg_stat_get_last_autovacuum_time(PG_FUNCTION_ARGS)
 208 {
 209         Oid                     relid = PG_GETARG_OID(0);
 210         TimestampTz     result;
 211         PgStat_StatTabEntry *tabentry;
 212
 213         if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
 214                 result = 0;
 215         else
 216                 result = tabentry->autovac_vacuum_timestamp;
 217
 218         if (result == 0)
 219                 PG_RETURN_NULL();
 220         else
 221                 PG_RETURN_TIMESTAMPTZ(result);
 222 }
 223
 224 Datum
 225 pg_stat_get_last_analyze_time(PG_FUNCTION_ARGS)
 226 {
 227         Oid                     relid = PG_GETARG_OID(0);
 228         TimestampTz     result;
 229         PgStat_StatTabEntry *tabentry;
 230
 231         if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
 232                 result = 0;
 233         else
 234                 result = tabentry->analyze_timestamp;
 235
 236         if (result == 0)
 237                 PG_RETURN_NULL();
 238         else
 239                 PG_RETURN_TIMESTAMPTZ(result);
 240 }
 241
 242 Datum
 243 pg_stat_get_last_autoanalyze_time(PG_FUNCTION_ARGS)
 244 {
 245         Oid                     relid = PG_GETARG_OID(0);
 246         TimestampTz     result;
 247         PgStat_StatTabEntry *tabentry;
 248
 249         if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
 250                 result = 0;
 251         else
 252                 result = tabentry->autovac_analyze_timestamp;
 253
 254         if (result == 0)
 255                 PG_RETURN_NULL();
 256         else
 257                 PG_RETURN_TIMESTAMPTZ(result);
 258 }
 259
 260 Datum
 261 pg_stat_get_backend_idset(PG_FUNCTION_ARGS)
 262 {
 263         FuncCallContext *funcctx;
 264         int                *fctx;
 265         int32           result;
 266
 267         /* stuff done only on the first call of the function */
 268         if (SRF_IS_FIRSTCALL())
 269         {
 270                 /* create a function context for cross-call persistence */
 271                 funcctx = SRF_FIRSTCALL_INIT();
 272
 273                 fctx = MemoryContextAlloc(funcctx->multi_call_memory_ctx,
 274                                                                   2 * sizeof(int));
 275                 funcctx->user_fctx = fctx;
 276
 277                 fctx[0] = 0;
 278                 fctx[1] = pgstat_fetch_stat_numbackends();
 279         }
 280
 281         /* stuff done on every call of the function */
 282         funcctx = SRF_PERCALL_SETUP();
 283         fctx = funcctx->user_fctx;
 284
 285         fctx[0] += 1;
 286         result = fctx[0];
 287
 288         if (result <= fctx[1])
 289         {
 290                 /* do when there is more left to send */
 291                 SRF_RETURN_NEXT(funcctx, Int32GetDatum(result));
 292         }
 293         else
 294         {
 295                 /* do when there is no more left */
 296                 SRF_RETURN_DONE(funcctx);
 297         }
 298 }
 299
 300
 301 Datum
 302 pg_backend_pid(PG_FUNCTION_ARGS)
 303 {
 304         PG_RETURN_INT32(MyProcPid);
 305 }
 306
 307 /*
 308  * Built-in function for resetting the counters
 309  */
 310 Datum
 311 pg_stat_reset(PG_FUNCTION_ARGS)
 312 {
 313         pgstat_reset_counters();
 314
 315         PG_RETURN_BOOL(true);
 316 }
 317
 318 Datum
 319 pg_stat_get_backend_pid(PG_FUNCTION_ARGS)
 320 {
 321         int32           beid = PG_GETARG_INT32(0);
 322         PgBackendStatus *beentry;
 323
 324         if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
 325                 PG_RETURN_NULL();
 326
 327         PG_RETURN_INT32(beentry->st_procpid);
 328 }
 329
 330
 331 Datum
 332 pg_stat_get_backend_dbid(PG_FUNCTION_ARGS)
 333 {
 334         int32           beid = PG_GETARG_INT32(0);
 335         PgBackendStatus *beentry;
 336
 337         if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
 338                 PG_RETURN_NULL();
 339
 340         PG_RETURN_OID(beentry->st_databaseid);
 341 }
 342
 343
 344 Datum
 345 pg_stat_get_backend_userid(PG_FUNCTION_ARGS)
 346 {
 347         int32           beid = PG_GETARG_INT32(0);
 348         PgBackendStatus *beentry;
 349
 350         if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
 351                 PG_RETURN_NULL();
 352
 353         PG_RETURN_OID(beentry->st_userid);
 354 }
 355
 356
 357 Datum
 358 pg_stat_get_backend_activity(PG_FUNCTION_ARGS)
 359 {
 360         int32           beid = PG_GETARG_INT32(0);
 361         text       *result;
 362         PgBackendStatus *beentry;
 363         int                     len;
 364         const char *activity;
 365
 366         if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
 367                 activity = "<backend information not available>";
 368         else if (!superuser() && beentry->st_userid != GetUserId())
 369                 activity = "<insufficient privilege>";
 370         else if (*(beentry->st_activity) == '\0')
 371                 activity = "<command string not enabled>";
 372         else
 373                 activity = beentry->st_activity;
 374
 375         len = strlen(activity);
 376         result = palloc(VARHDRSZ + len);
 377         VARATT_SIZEP(result) = VARHDRSZ + len;
 378         memcpy(VARDATA(result), activity, len);
 379
 380         PG_RETURN_TEXT_P(result);
 381 }
 382
 383
 384 Datum
 385 pg_stat_get_backend_activity_start(PG_FUNCTION_ARGS)
 386 {
 387         int32           beid = PG_GETARG_INT32(0);
 388         TimestampTz result;
 389         PgBackendStatus *beentry;
 390
 391         if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
 392                 PG_RETURN_NULL();
 393
 394         if (!superuser() && beentry->st_userid != GetUserId())
 395                 PG_RETURN_NULL();
 396
 397         result = beentry->st_activity_start_timestamp;
 398
 399         /*
 400          * No time recorded for start of current query -- this is the case if the
 401          * user hasn't enabled query-level stats collection.
 402          */
 403         if (result == 0)
 404                 PG_RETURN_NULL();
 405
 406         PG_RETURN_TIMESTAMPTZ(result);
 407 }
 408
 409 Datum
 410 pg_stat_get_backend_start(PG_FUNCTION_ARGS)
 411 {
 412         int32           beid = PG_GETARG_INT32(0);
 413         TimestampTz result;
 414         PgBackendStatus *beentry;
 415
 416         if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
 417                 PG_RETURN_NULL();
 418
 419         if (!superuser() && beentry->st_userid != GetUserId())
 420                 PG_RETURN_NULL();
 421
 422         result = beentry->st_proc_start_timestamp;
 423
 424         if (result == 0)                        /* probably can't happen? */
 425                 PG_RETURN_NULL();
 426
 427         PG_RETURN_TIMESTAMPTZ(result);
 428 }
 429
 430
 431 Datum
 432 pg_stat_get_backend_client_addr(PG_FUNCTION_ARGS)
 433 {
 434         int32           beid = PG_GETARG_INT32(0);
 435         PgBackendStatus *beentry;
 436         SockAddr        zero_clientaddr;
 437         char            remote_host[NI_MAXHOST];
 438         int                     ret;
 439
 440         if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
 441                 PG_RETURN_NULL();
 442
 443         if (!superuser() && beentry->st_userid != GetUserId())
 444                 PG_RETURN_NULL();
 445
 446         /* A zeroed client addr means we don't know */
 447         memset(&zero_clientaddr, 0, sizeof(zero_clientaddr));
 448         if (memcmp(&(beentry->st_clientaddr), &zero_clientaddr,
 449                            sizeof(zero_clientaddr) == 0))
 450                 PG_RETURN_NULL();
 451
 452         switch (beentry->st_clientaddr.addr.ss_family)
 453         {
 454                 case AF_INET:
 455 #ifdef HAVE_IPV6
 456                 case AF_INET6:
 457 #endif
 458                         break;
 459                 default:
 460                         PG_RETURN_NULL();
 461         }
 462
 463         remote_host[0] = '\0';
 464         ret = pg_getnameinfo_all(&beentry->st_clientaddr.addr,
 465                                                          beentry->st_clientaddr.salen,
 466                                                          remote_host, sizeof(remote_host),
 467                                                          NULL, 0,
 468                                                          NI_NUMERICHOST | NI_NUMERICSERV);
 469         if (ret)
 470                 PG_RETURN_NULL();
 471
 472         PG_RETURN_INET_P(DirectFunctionCall1(inet_in,
 473                                                                                  CStringGetDatum(remote_host)));
 474 }
 475
 476 Datum
 477 pg_stat_get_backend_client_port(PG_FUNCTION_ARGS)
 478 {
 479         int32           beid = PG_GETARG_INT32(0);
 480         PgBackendStatus *beentry;
 481         SockAddr        zero_clientaddr;
 482         char            remote_port[NI_MAXSERV];
 483         int                     ret;
 484
 485         if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
 486                 PG_RETURN_NULL();
 487
 488         if (!superuser() && beentry->st_userid != GetUserId())
 489                 PG_RETURN_NULL();
 490
 491         /* A zeroed client addr means we don't know */
 492         memset(&zero_clientaddr, 0, sizeof(zero_clientaddr));
 493         if (memcmp(&(beentry->st_clientaddr), &zero_clientaddr,
 494                            sizeof(zero_clientaddr) == 0))
 495                 PG_RETURN_NULL();
 496
 497         switch (beentry->st_clientaddr.addr.ss_family)
 498         {
 499                 case AF_INET:
 500 #ifdef HAVE_IPV6
 501                 case AF_INET6:
 502 #endif
 503                         break;
 504                 case AF_UNIX:
 505                         PG_RETURN_INT32(-1);
 506                 default:
 507                         PG_RETURN_NULL();
 508         }
 509
 510         remote_port[0] = '\0';
 511         ret = pg_getnameinfo_all(&beentry->st_clientaddr.addr,
 512                                                          beentry->st_clientaddr.salen,
 513                                                          NULL, 0,
 514                                                          remote_port, sizeof(remote_port),
 515                                                          NI_NUMERICHOST | NI_NUMERICSERV);
 516         if (ret)
 517                 PG_RETURN_NULL();
 518
 519         PG_RETURN_DATUM(DirectFunctionCall1(int4in,
 520                                                                                 CStringGetDatum(remote_port)));
 521 }
 522
 523
 524 Datum
 525 pg_stat_get_db_numbackends(PG_FUNCTION_ARGS)
 526 {
 527         Oid                     dbid = PG_GETARG_OID(0);
 528         int32           result;
 529         int                     tot_backends = pgstat_fetch_stat_numbackends();
 530         int                     beid;
 531
 532         result = 0;
 533         for (beid = 1; beid <= tot_backends; beid++)
 534         {
 535                 PgBackendStatus *beentry = pgstat_fetch_stat_beentry(beid);
 536
 537                 if (beentry && beentry->st_databaseid == dbid)
 538                         result++;
 539         }
 540
 541         PG_RETURN_INT32(result);
 542 }
 543
 544
 545 Datum
 546 pg_stat_get_db_xact_commit(PG_FUNCTION_ARGS)
 547 {
 548         Oid                     dbid = PG_GETARG_OID(0);
 549         int64           result;
 550         PgStat_StatDBEntry *dbentry;
 551
 552         if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
 553                 result = 0;
 554         else
 555                 result = (int64) (dbentry->n_xact_commit);
 556
 557         PG_RETURN_INT64(result);
 558 }
 559
 560
 561 Datum
 562 pg_stat_get_db_xact_rollback(PG_FUNCTION_ARGS)
 563 {
 564         Oid                     dbid = PG_GETARG_OID(0);
 565         int64           result;
 566         PgStat_StatDBEntry *dbentry;
 567
 568         if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
 569                 result = 0;
 570         else
 571                 result = (int64) (dbentry->n_xact_rollback);
 572
 573         PG_RETURN_INT64(result);
 574 }
 575
 576
 577 Datum
 578 pg_stat_get_db_blocks_fetched(PG_FUNCTION_ARGS)
 579 {
 580         Oid                     dbid = PG_GETARG_OID(0);
 581         int64           result;
 582         PgStat_StatDBEntry *dbentry;
 583
 584         if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
 585                 result = 0;
 586         else
 587                 result = (int64) (dbentry->n_blocks_fetched);
 588
 589         PG_RETURN_INT64(result);
 590 }
 591
 592
 593 Datum
 594 pg_stat_get_db_blocks_hit(PG_FUNCTION_ARGS)
 595 {
 596         Oid                     dbid = PG_GETARG_OID(0);
 597         int64           result;
 598         PgStat_StatDBEntry *dbentry;
 599
 600         if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
 601                 result = 0;
 602         else
 603                 result = (int64) (dbentry->n_blocks_hit);
 604
 605         PG_RETURN_INT64(result);
 606 }