granicus.if.org Git - postgresql/blob - contrib/pgstattuple/pgstattuple.c

   1 /*
   2  * contrib/pgstattuple/pgstattuple.c
   3  *
   4  * Copyright (c) 2001,2002      Tatsuo Ishii
   5  *
   6  * Permission to use, copy, modify, and distribute this software and
   7  * its documentation for any purpose, without fee, and without a
   8  * written agreement is hereby granted, provided that the above
   9  * copyright notice and this paragraph and the following two
  10  * paragraphs appear in all copies.
  11  *
  12  * IN NO EVENT SHALL THE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT,
  13  * INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
  14  * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
  15  * DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED
  16  * OF THE POSSIBILITY OF SUCH DAMAGE.
  17  *
  18  * THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT
  19  * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
  20  * A PARTICULAR PURPOSE.  THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS
  21  * IS" BASIS, AND THE AUTHOR HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE,
  22  * SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
  23  */
  24
  25 #include "postgres.h"
  26
  27 #include "access/gist_private.h"
  28 #include "access/hash.h"
  29 #include "access/nbtree.h"
  30 #include "access/relscan.h"
  31 #include "catalog/namespace.h"
  32 #include "funcapi.h"
  33 #include "miscadmin.h"
  34 #include "storage/bufmgr.h"
  35 #include "storage/lmgr.h"
  36 #include "utils/builtins.h"
  37 #include "utils/tqual.h"
  38
  39 PG_MODULE_MAGIC;
  40
  41 PG_FUNCTION_INFO_V1(pgstattuple);
  42 PG_FUNCTION_INFO_V1(pgstattuplebyid);
  43
  44 /*
  45  * struct pgstattuple_type
  46  *
  47  * tuple_percent, dead_tuple_percent and free_percent are computable,
  48  * so not defined here.
  49  */
  50 typedef struct pgstattuple_type
  51 {
  52         uint64          table_len;
  53         uint64          tuple_count;
  54         uint64          tuple_len;
  55         uint64          dead_tuple_count;
  56         uint64          dead_tuple_len;
  57         uint64          free_space;             /* free/reusable space in bytes */
  58 } pgstattuple_type;
  59
  60 typedef void (*pgstat_page) (pgstattuple_type *, Relation, BlockNumber,
  61                                                                                  BufferAccessStrategy);
  62
  63 static Datum build_pgstattuple_type(pgstattuple_type *stat,
  64                                            FunctionCallInfo fcinfo);
  65 static Datum pgstat_relation(Relation rel, FunctionCallInfo fcinfo);
  66 static Datum pgstat_heap(Relation rel, FunctionCallInfo fcinfo);
  67 static void pgstat_btree_page(pgstattuple_type *stat,
  68                                   Relation rel, BlockNumber blkno,
  69                                   BufferAccessStrategy bstrategy);
  70 static void pgstat_hash_page(pgstattuple_type *stat,
  71                                  Relation rel, BlockNumber blkno,
  72                                  BufferAccessStrategy bstrategy);
  73 static void pgstat_gist_page(pgstattuple_type *stat,
  74                                  Relation rel, BlockNumber blkno,
  75                                  BufferAccessStrategy bstrategy);
  76 static Datum pgstat_index(Relation rel, BlockNumber start,
  77                          pgstat_page pagefn, FunctionCallInfo fcinfo);
  78 static void pgstat_index_page(pgstattuple_type *stat, Page page,
  79                                   OffsetNumber minoff, OffsetNumber maxoff);
  80
  81 /*
  82  * build_pgstattuple_type -- build a pgstattuple_type tuple
  83  */
  84 static Datum
  85 build_pgstattuple_type(pgstattuple_type *stat, FunctionCallInfo fcinfo)
  86 {
  87 #define NCOLUMNS        9
  88 #define NCHARS          32
  89
  90         HeapTuple       tuple;
  91         char       *values[NCOLUMNS];
  92         char            values_buf[NCOLUMNS][NCHARS];
  93         int                     i;
  94         double          tuple_percent;
  95         double          dead_tuple_percent;
  96         double          free_percent;   /* free/reusable space in % */
  97         TupleDesc       tupdesc;
  98         AttInMetadata *attinmeta;
  99
 100         /* Build a tuple descriptor for our result type */
 101         if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
 102                 elog(ERROR, "return type must be a row type");
 103
 104         /*
 105          * Generate attribute metadata needed later to produce tuples from raw C
 106          * strings
 107          */
 108         attinmeta = TupleDescGetAttInMetadata(tupdesc);
 109
 110         if (stat->table_len == 0)
 111         {
 112                 tuple_percent = 0.0;
 113                 dead_tuple_percent = 0.0;
 114                 free_percent = 0.0;
 115         }
 116         else
 117         {
 118                 tuple_percent = 100.0 * stat->tuple_len / stat->table_len;
 119                 dead_tuple_percent = 100.0 * stat->dead_tuple_len / stat->table_len;
 120                 free_percent = 100.0 * stat->free_space / stat->table_len;
 121         }
 122
 123         /*
 124          * Prepare a values array for constructing the tuple. This should be an
 125          * array of C strings which will be processed later by the appropriate
 126          * "in" functions.
 127          */
 128         for (i = 0; i < NCOLUMNS; i++)
 129                 values[i] = values_buf[i];
 130         i = 0;
 131         snprintf(values[i++], NCHARS, INT64_FORMAT, stat->table_len);
 132         snprintf(values[i++], NCHARS, INT64_FORMAT, stat->tuple_count);
 133         snprintf(values[i++], NCHARS, INT64_FORMAT, stat->tuple_len);
 134         snprintf(values[i++], NCHARS, "%.2f", tuple_percent);
 135         snprintf(values[i++], NCHARS, INT64_FORMAT, stat->dead_tuple_count);
 136         snprintf(values[i++], NCHARS, INT64_FORMAT, stat->dead_tuple_len);
 137         snprintf(values[i++], NCHARS, "%.2f", dead_tuple_percent);
 138         snprintf(values[i++], NCHARS, INT64_FORMAT, stat->free_space);
 139         snprintf(values[i++], NCHARS, "%.2f", free_percent);
 140
 141         /* build a tuple */
 142         tuple = BuildTupleFromCStrings(attinmeta, values);
 143
 144         /* make the tuple into a datum */
 145         return HeapTupleGetDatum(tuple);
 146 }
 147
 148 /* ----------
 149  * pgstattuple:
 150  * returns live/dead tuples info
 151  *
 152  * C FUNCTION definition
 153  * pgstattuple(text) returns pgstattuple_type
 154  * see pgstattuple.sql for pgstattuple_type
 155  * ----------
 156  */
 157
 158 Datum
 159 pgstattuple(PG_FUNCTION_ARGS)
 160 {
 161         text       *relname = PG_GETARG_TEXT_P(0);
 162         RangeVar   *relrv;
 163         Relation        rel;
 164
 165         if (!superuser())
 166                 ereport(ERROR,
 167                                 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 168                                  (errmsg("must be superuser to use pgstattuple functions"))));
 169
 170         /* open relation */
 171         relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
 172         rel = relation_openrv(relrv, AccessShareLock);
 173
 174         PG_RETURN_DATUM(pgstat_relation(rel, fcinfo));
 175 }
 176
 177 Datum
 178 pgstattuplebyid(PG_FUNCTION_ARGS)
 179 {
 180         Oid                     relid = PG_GETARG_OID(0);
 181         Relation        rel;
 182
 183         if (!superuser())
 184                 ereport(ERROR,
 185                                 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 186                                  (errmsg("must be superuser to use pgstattuple functions"))));
 187
 188         /* open relation */
 189         rel = relation_open(relid, AccessShareLock);
 190
 191         PG_RETURN_DATUM(pgstat_relation(rel, fcinfo));
 192 }
 193
 194 /*
 195  * pgstat_relation
 196  */
 197 static Datum
 198 pgstat_relation(Relation rel, FunctionCallInfo fcinfo)
 199 {
 200         const char *err;
 201
 202         /*
 203          * Reject attempts to read non-local temporary relations; we would be
 204          * likely to get wrong data since we have no visibility into the owning
 205          * session's local buffers.
 206          */
 207         if (RELATION_IS_OTHER_TEMP(rel))
 208                 ereport(ERROR,
 209                                 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 210                                  errmsg("cannot access temporary tables of other sessions")));
 211
 212         switch (rel->rd_rel->relkind)
 213         {
 214                 case RELKIND_RELATION:
 215                 case RELKIND_MATVIEW:
 216                 case RELKIND_TOASTVALUE:
 217                 case RELKIND_SEQUENCE:
 218                         return pgstat_heap(rel, fcinfo);
 219                 case RELKIND_INDEX:
 220                         switch (rel->rd_rel->relam)
 221                         {
 222                                 case BTREE_AM_OID:
 223                                         return pgstat_index(rel, BTREE_METAPAGE + 1,
 224                                                                                 pgstat_btree_page, fcinfo);
 225                                 case HASH_AM_OID:
 226                                         return pgstat_index(rel, HASH_METAPAGE + 1,
 227                                                                                 pgstat_hash_page, fcinfo);
 228                                 case GIST_AM_OID:
 229                                         return pgstat_index(rel, GIST_ROOT_BLKNO + 1,
 230                                                                                 pgstat_gist_page, fcinfo);
 231                                 case GIN_AM_OID:
 232                                         err = "gin index";
 233                                         break;
 234                                 case SPGIST_AM_OID:
 235                                         err = "spgist index";
 236                                         break;
 237                                 default:
 238                                         err = "unknown index";
 239                                         break;
 240                         }
 241                         break;
 242                 case RELKIND_VIEW:
 243                         err = "view";
 244                         break;
 245                 case RELKIND_COMPOSITE_TYPE:
 246                         err = "composite type";
 247                         break;
 248                 case RELKIND_FOREIGN_TABLE:
 249                         err = "foreign table";
 250                         break;
 251                 default:
 252                         err = "unknown";
 253                         break;
 254         }
 255
 256         ereport(ERROR,
 257                         (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 258                          errmsg("\"%s\" (%s) is not supported",
 259                                         RelationGetRelationName(rel), err)));
 260         return 0;                                       /* should not happen */
 261 }
 262
 263 /*
 264  * pgstat_heap -- returns live/dead tuples info in a heap
 265  */
 266 static Datum
 267 pgstat_heap(Relation rel, FunctionCallInfo fcinfo)
 268 {
 269         HeapScanDesc scan;
 270         HeapTuple       tuple;
 271         BlockNumber nblocks;
 272         BlockNumber block = 0;          /* next block to count free space in */
 273         BlockNumber tupblock;
 274         Buffer          buffer;
 275         pgstattuple_type stat = {0};
 276         SnapshotData SnapshotDirty;
 277
 278         /* Disable syncscan because we assume we scan from block zero upwards */
 279         scan = heap_beginscan_strat(rel, SnapshotAny, 0, NULL, true, false);
 280         InitDirtySnapshot(SnapshotDirty);
 281
 282         nblocks = scan->rs_nblocks; /* # blocks to be scanned */
 283
 284         /* scan the relation */
 285         while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
 286         {
 287                 CHECK_FOR_INTERRUPTS();
 288
 289                 /* must hold a buffer lock to call HeapTupleSatisfiesVisibility */
 290                 LockBuffer(scan->rs_cbuf, BUFFER_LOCK_SHARE);
 291
 292                 if (HeapTupleSatisfiesVisibility(tuple, &SnapshotDirty, scan->rs_cbuf))
 293                 {
 294                         stat.tuple_len += tuple->t_len;
 295                         stat.tuple_count++;
 296                 }
 297                 else
 298                 {
 299                         stat.dead_tuple_len += tuple->t_len;
 300                         stat.dead_tuple_count++;
 301                 }
 302
 303                 LockBuffer(scan->rs_cbuf, BUFFER_LOCK_UNLOCK);
 304
 305                 /*
 306                  * To avoid physically reading the table twice, try to do the
 307                  * free-space scan in parallel with the heap scan.  However,
 308                  * heap_getnext may find no tuples on a given page, so we cannot
 309                  * simply examine the pages returned by the heap scan.
 310                  */
 311                 tupblock = BlockIdGetBlockNumber(&tuple->t_self.ip_blkid);
 312
 313                 while (block <= tupblock)
 314                 {
 315                         CHECK_FOR_INTERRUPTS();
 316
 317                         buffer = ReadBufferExtended(rel, MAIN_FORKNUM, block,
 318                                                                                 RBM_NORMAL, scan->rs_strategy);
 319                         LockBuffer(buffer, BUFFER_LOCK_SHARE);
 320                         stat.free_space += PageGetHeapFreeSpace((Page) BufferGetPage(buffer));
 321                         UnlockReleaseBuffer(buffer);
 322                         block++;
 323                 }
 324         }
 325
 326         while (block < nblocks)
 327         {
 328                 CHECK_FOR_INTERRUPTS();
 329
 330                 buffer = ReadBufferExtended(rel, MAIN_FORKNUM, block,
 331                                                                         RBM_NORMAL, scan->rs_strategy);
 332                 LockBuffer(buffer, BUFFER_LOCK_SHARE);
 333                 stat.free_space += PageGetHeapFreeSpace((Page) BufferGetPage(buffer));
 334                 UnlockReleaseBuffer(buffer);
 335                 block++;
 336         }
 337
 338         heap_endscan(scan);
 339         relation_close(rel, AccessShareLock);
 340
 341         stat.table_len = (uint64) nblocks *BLCKSZ;
 342
 343         return build_pgstattuple_type(&stat, fcinfo);
 344 }
 345
 346 /*
 347  * pgstat_btree_page -- check tuples in a btree page
 348  */
 349 static void
 350 pgstat_btree_page(pgstattuple_type *stat, Relation rel, BlockNumber blkno,
 351                                   BufferAccessStrategy bstrategy)
 352 {
 353         Buffer          buf;
 354         Page            page;
 355
 356         buf = ReadBufferExtended(rel, MAIN_FORKNUM, blkno, RBM_NORMAL, bstrategy);
 357         LockBuffer(buf, BT_READ);
 358         page = BufferGetPage(buf);
 359
 360         /* Page is valid, see what to do with it */
 361         if (PageIsNew(page))
 362         {
 363                 /* fully empty page */
 364                 stat->free_space += BLCKSZ;
 365         }
 366         else
 367         {
 368                 BTPageOpaque opaque;
 369
 370                 opaque = (BTPageOpaque) PageGetSpecialPointer(page);
 371                 if (opaque->btpo_flags & (BTP_DELETED | BTP_HALF_DEAD))
 372                 {
 373                         /* recyclable page */
 374                         stat->free_space += BLCKSZ;
 375                 }
 376                 else if (P_ISLEAF(opaque))
 377                 {
 378                         pgstat_index_page(stat, page, P_FIRSTDATAKEY(opaque),
 379                                                           PageGetMaxOffsetNumber(page));
 380                 }
 381                 else
 382                 {
 383                         /* root or node */
 384                 }
 385         }
 386
 387         _bt_relbuf(rel, buf);
 388 }
 389
 390 /*
 391  * pgstat_hash_page -- check tuples in a hash page
 392  */
 393 static void
 394 pgstat_hash_page(pgstattuple_type *stat, Relation rel, BlockNumber blkno,
 395                                  BufferAccessStrategy bstrategy)
 396 {
 397         Buffer          buf;
 398         Page            page;
 399
 400         _hash_getlock(rel, blkno, HASH_SHARE);
 401         buf = _hash_getbuf_with_strategy(rel, blkno, HASH_READ, 0, bstrategy);
 402         page = BufferGetPage(buf);
 403
 404         if (PageGetSpecialSize(page) == MAXALIGN(sizeof(HashPageOpaqueData)))
 405         {
 406                 HashPageOpaque opaque;
 407
 408                 opaque = (HashPageOpaque) PageGetSpecialPointer(page);
 409                 switch (opaque->hasho_flag)
 410                 {
 411                         case LH_UNUSED_PAGE:
 412                                 stat->free_space += BLCKSZ;
 413                                 break;
 414                         case LH_BUCKET_PAGE:
 415                         case LH_OVERFLOW_PAGE:
 416                                 pgstat_index_page(stat, page, FirstOffsetNumber,
 417                                                                   PageGetMaxOffsetNumber(page));
 418                                 break;
 419                         case LH_BITMAP_PAGE:
 420                         case LH_META_PAGE:
 421                         default:
 422                                 break;
 423                 }
 424         }
 425         else
 426         {
 427                 /* maybe corrupted */
 428         }
 429
 430         _hash_relbuf(rel, buf);
 431         _hash_droplock(rel, blkno, HASH_SHARE);
 432 }
 433
 434 /*
 435  * pgstat_gist_page -- check tuples in a gist page
 436  */
 437 static void
 438 pgstat_gist_page(pgstattuple_type *stat, Relation rel, BlockNumber blkno,
 439                                  BufferAccessStrategy bstrategy)
 440 {
 441         Buffer          buf;
 442         Page            page;
 443
 444         buf = ReadBufferExtended(rel, MAIN_FORKNUM, blkno, RBM_NORMAL, bstrategy);
 445         LockBuffer(buf, GIST_SHARE);
 446         gistcheckpage(rel, buf);
 447         page = BufferGetPage(buf);
 448
 449         if (GistPageIsLeaf(page))
 450         {
 451                 pgstat_index_page(stat, page, FirstOffsetNumber,
 452                                                   PageGetMaxOffsetNumber(page));
 453         }
 454         else
 455         {
 456                 /* root or node */
 457         }
 458
 459         UnlockReleaseBuffer(buf);
 460 }
 461
 462 /*
 463  * pgstat_index -- returns live/dead tuples info in a generic index
 464  */
 465 static Datum
 466 pgstat_index(Relation rel, BlockNumber start, pgstat_page pagefn,
 467                          FunctionCallInfo fcinfo)
 468 {
 469         BlockNumber nblocks;
 470         BlockNumber blkno;
 471         BufferAccessStrategy bstrategy;
 472         pgstattuple_type stat = {0};
 473
 474         /* prepare access strategy for this index */
 475         bstrategy = GetAccessStrategy(BAS_BULKREAD);
 476
 477         blkno = start;
 478         for (;;)
 479         {
 480                 /* Get the current relation length */
 481                 LockRelationForExtension(rel, ExclusiveLock);
 482                 nblocks = RelationGetNumberOfBlocks(rel);
 483                 UnlockRelationForExtension(rel, ExclusiveLock);
 484
 485                 /* Quit if we've scanned the whole relation */
 486                 if (blkno >= nblocks)
 487                 {
 488                         stat.table_len = (uint64) nblocks *BLCKSZ;
 489
 490                         break;
 491                 }
 492
 493                 for (; blkno < nblocks; blkno++)
 494                 {
 495                         CHECK_FOR_INTERRUPTS();
 496
 497                         pagefn(&stat, rel, blkno, bstrategy);
 498                 }
 499         }
 500
 501         relation_close(rel, AccessShareLock);
 502
 503         return build_pgstattuple_type(&stat, fcinfo);
 504 }
 505
 506 /*
 507  * pgstat_index_page -- for generic index page
 508  */
 509 static void
 510 pgstat_index_page(pgstattuple_type *stat, Page page,
 511                                   OffsetNumber minoff, OffsetNumber maxoff)
 512 {
 513         OffsetNumber i;
 514
 515         stat->free_space += PageGetFreeSpace(page);
 516
 517         for (i = minoff; i <= maxoff; i = OffsetNumberNext(i))
 518         {
 519                 ItemId          itemid = PageGetItemId(page, i);
 520
 521                 if (ItemIdIsDead(itemid))
 522                 {
 523                         stat->dead_tuple_count++;
 524                         stat->dead_tuple_len += ItemIdGetLength(itemid);
 525                 }
 526                 else
 527                 {
 528                         stat->tuple_count++;
 529                         stat->tuple_len += ItemIdGetLength(itemid);
 530                 }
 531         }
 532 }