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