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