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