contrib/pgstattuple/pgstattuple.c

   1 /*
   2  * $PostgreSQL$
   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
  40 PG_MODULE_MAGIC;
  41
  42 PG_FUNCTION_INFO_V1(pgstattuple);
  43 PG_FUNCTION_INFO_V1(pgstattuplebyid);
  44
  45 extern Datum pgstattuple(PG_FUNCTION_ARGS);
  46 extern Datum pgstattuplebyid(PG_FUNCTION_ARGS);
  47
  48 /*
  49  * struct pgstattuple_type
  50  *
  51  * tuple_percent, dead_tuple_percent and free_percent are computable,
  52  * so not defined here.
  53  */
  54 typedef struct pgstattuple_type
  55 {
  56         uint64          table_len;
  57         uint64          tuple_count;
  58         uint64          tuple_len;
  59         uint64          dead_tuple_count;
  60         uint64          dead_tuple_len;
  61         uint64          free_space;             /* free/reusable space in bytes */
  62 }       pgstattuple_type;
  63
  64 typedef void (*pgstat_page) (pgstattuple_type *, Relation, BlockNumber);
  65
  66 static Datum build_pgstattuple_type(pgstattuple_type * stat,
  67                                            FunctionCallInfo fcinfo);
  68 static Datum pgstat_relation(Relation rel, FunctionCallInfo fcinfo);
  69 static Datum pgstat_heap(Relation rel, FunctionCallInfo fcinfo);
  70 static void pgstat_btree_page(pgstattuple_type * stat,
  71                                   Relation rel, BlockNumber blkno);
  72 static void pgstat_hash_page(pgstattuple_type * stat,
  73                                  Relation rel, BlockNumber blkno);
  74 static void pgstat_gist_page(pgstattuple_type * stat,
  75                                  Relation rel, BlockNumber blkno);
  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         switch (rel->rd_rel->relkind)
 203         {
 204                 case RELKIND_RELATION:
 205                 case RELKIND_TOASTVALUE:
 206                 case RELKIND_UNCATALOGED:
 207                 case RELKIND_SEQUENCE:
 208                         return pgstat_heap(rel, fcinfo);
 209                 case RELKIND_INDEX:
 210                         switch (rel->rd_rel->relam)
 211                         {
 212                                 case BTREE_AM_OID:
 213                                         return pgstat_index(rel, BTREE_METAPAGE + 1,
 214                                                                                 pgstat_btree_page, fcinfo);
 215                                 case HASH_AM_OID:
 216                                         return pgstat_index(rel, HASH_METAPAGE + 1,
 217                                                                                 pgstat_hash_page, fcinfo);
 218                                 case GIST_AM_OID:
 219                                         return pgstat_index(rel, GIST_ROOT_BLKNO + 1,
 220                                                                                 pgstat_gist_page, fcinfo);
 221                                 case GIN_AM_OID:
 222                                         err = "gin index";
 223                                         break;
 224                                 default:
 225                                         err = "unknown index";
 226                                         break;
 227                         }
 228                         break;
 229                 case RELKIND_VIEW:
 230                         err = "view";
 231                         break;
 232                 case RELKIND_COMPOSITE_TYPE:
 233                         err = "composite type";
 234                         break;
 235                 default:
 236                         err = "unknown";
 237                         break;
 238         }
 239
 240         ereport(ERROR,
 241                         (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 242                          errmsg("\"%s\" (%s) is not supported",
 243                                         RelationGetRelationName(rel), err)));
 244         return 0;                                       /* should not happen */
 245 }
 246
 247 /*
 248  * pgstat_heap -- returns live/dead tuples info in a heap
 249  */
 250 static Datum
 251 pgstat_heap(Relation rel, FunctionCallInfo fcinfo)
 252 {
 253         HeapScanDesc scan;
 254         HeapTuple       tuple;
 255         BlockNumber nblocks;
 256         BlockNumber block = 0;          /* next block to count free space in */
 257         BlockNumber tupblock;
 258         Buffer          buffer;
 259         pgstattuple_type stat = {0};
 260
 261         /* Disable syncscan because we assume we scan from block zero upwards */
 262         scan = heap_beginscan_strat(rel, SnapshotAny, 0, NULL, true, false);
 263
 264         nblocks = scan->rs_nblocks; /* # blocks to be scanned */
 265
 266         /* scan the relation */
 267         while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
 268         {
 269                 /* must hold a buffer lock to call HeapTupleSatisfiesVisibility */
 270                 LockBuffer(scan->rs_cbuf, BUFFER_LOCK_SHARE);
 271
 272                 if (HeapTupleSatisfiesVisibility(tuple, SnapshotNow, scan->rs_cbuf))
 273                 {
 274                         stat.tuple_len += tuple->t_len;
 275                         stat.tuple_count++;
 276                 }
 277                 else
 278                 {
 279                         stat.dead_tuple_len += tuple->t_len;
 280                         stat.dead_tuple_count++;
 281                 }
 282
 283                 LockBuffer(scan->rs_cbuf, BUFFER_LOCK_UNLOCK);
 284
 285                 /*
 286                  * To avoid physically reading the table twice, try to do the
 287                  * free-space scan in parallel with the heap scan.      However,
 288                  * heap_getnext may find no tuples on a given page, so we cannot
 289                  * simply examine the pages returned by the heap scan.
 290                  */
 291                 tupblock = BlockIdGetBlockNumber(&tuple->t_self.ip_blkid);
 292
 293                 while (block <= tupblock)
 294                 {
 295                         buffer = ReadBuffer(rel, block);
 296                         LockBuffer(buffer, BUFFER_LOCK_SHARE);
 297                         stat.free_space += PageGetHeapFreeSpace((Page) BufferGetPage(buffer));
 298                         UnlockReleaseBuffer(buffer);
 299                         block++;
 300                 }
 301         }
 302         heap_endscan(scan);
 303
 304         while (block < nblocks)
 305         {
 306                 buffer = ReadBuffer(rel, block);
 307                 LockBuffer(buffer, BUFFER_LOCK_SHARE);
 308                 stat.free_space += PageGetHeapFreeSpace((Page) BufferGetPage(buffer));
 309                 UnlockReleaseBuffer(buffer);
 310                 block++;
 311         }
 312
 313         relation_close(rel, AccessShareLock);
 314
 315         stat.table_len = (uint64) nblocks *BLCKSZ;
 316
 317         return build_pgstattuple_type(&stat, fcinfo);
 318 }
 319
 320 /*
 321  * pgstat_btree_page -- check tuples in a btree page
 322  */
 323 static void
 324 pgstat_btree_page(pgstattuple_type * stat, Relation rel, BlockNumber blkno)
 325 {
 326         Buffer          buf;
 327         Page            page;
 328
 329         buf = ReadBuffer(rel, blkno);
 330         LockBuffer(buf, BT_READ);
 331         page = BufferGetPage(buf);
 332
 333         /* Page is valid, see what to do with it */
 334         if (PageIsNew(page))
 335         {
 336                 /* fully empty page */
 337                 stat->free_space += BLCKSZ;
 338         }
 339         else
 340         {
 341                 BTPageOpaque opaque;
 342
 343                 opaque = (BTPageOpaque) PageGetSpecialPointer(page);
 344                 if (opaque->btpo_flags & (BTP_DELETED | BTP_HALF_DEAD))
 345                 {
 346                         /* recyclable page */
 347                         stat->free_space += BLCKSZ;
 348                 }
 349                 else if (P_ISLEAF(opaque))
 350                 {
 351                         pgstat_index_page(stat, page, P_FIRSTDATAKEY(opaque),
 352                                                           PageGetMaxOffsetNumber(page));
 353                 }
 354                 else
 355                 {
 356                         /* root or node */
 357                 }
 358         }
 359
 360         _bt_relbuf(rel, buf);
 361 }
 362
 363 /*
 364  * pgstat_hash_page -- check tuples in a hash page
 365  */
 366 static void
 367 pgstat_hash_page(pgstattuple_type * stat, Relation rel, BlockNumber blkno)
 368 {
 369         Buffer          buf;
 370         Page            page;
 371
 372         _hash_getlock(rel, blkno, HASH_SHARE);
 373         buf = _hash_getbuf(rel, blkno, HASH_READ, 0);
 374         page = BufferGetPage(buf);
 375
 376         if (PageGetSpecialSize(page) == MAXALIGN(sizeof(HashPageOpaqueData)))
 377         {
 378                 HashPageOpaque opaque;
 379
 380                 opaque = (HashPageOpaque) PageGetSpecialPointer(page);
 381                 switch (opaque->hasho_flag)
 382                 {
 383                         case LH_UNUSED_PAGE:
 384                                 stat->free_space += BLCKSZ;
 385                                 break;
 386                         case LH_BUCKET_PAGE:
 387                         case LH_OVERFLOW_PAGE:
 388                                 pgstat_index_page(stat, page, FirstOffsetNumber,
 389                                                                   PageGetMaxOffsetNumber(page));
 390                                 break;
 391                         case LH_BITMAP_PAGE:
 392                         case LH_META_PAGE:
 393                         default:
 394                                 break;
 395                 }
 396         }
 397         else
 398         {
 399                 /* maybe corrupted */
 400         }
 401
 402         _hash_relbuf(rel, buf);
 403         _hash_droplock(rel, blkno, HASH_SHARE);
 404 }
 405
 406 /*
 407  * pgstat_gist_page -- check tuples in a gist page
 408  */
 409 static void
 410 pgstat_gist_page(pgstattuple_type * stat, Relation rel, BlockNumber blkno)
 411 {
 412         Buffer          buf;
 413         Page            page;
 414
 415         buf = ReadBuffer(rel, blkno);
 416         LockBuffer(buf, GIST_SHARE);
 417         gistcheckpage(rel, buf);
 418         page = BufferGetPage(buf);
 419
 420         if (GistPageIsLeaf(page))
 421         {
 422                 pgstat_index_page(stat, page, FirstOffsetNumber,
 423                                                   PageGetMaxOffsetNumber(page));
 424         }
 425         else
 426         {
 427                 /* root or node */
 428         }
 429
 430         UnlockReleaseBuffer(buf);
 431 }
 432
 433 /*
 434  * pgstat_index -- returns live/dead tuples info in a generic index
 435  */
 436 static Datum
 437 pgstat_index(Relation rel, BlockNumber start, pgstat_page pagefn,
 438                          FunctionCallInfo fcinfo)
 439 {
 440         BlockNumber nblocks;
 441         BlockNumber blkno;
 442         pgstattuple_type stat = {0};
 443
 444         blkno = start;
 445         for (;;)
 446         {
 447                 /* Get the current relation length */
 448                 LockRelationForExtension(rel, ExclusiveLock);
 449                 nblocks = RelationGetNumberOfBlocks(rel);
 450                 UnlockRelationForExtension(rel, ExclusiveLock);
 451
 452                 /* Quit if we've scanned the whole relation */
 453                 if (blkno >= nblocks)
 454                 {
 455                         stat.table_len = (uint64) nblocks *BLCKSZ;
 456
 457                         break;
 458                 }
 459
 460                 for (; blkno < nblocks; blkno++)
 461                         pagefn(&stat, rel, blkno);
 462         }
 463
 464         relation_close(rel, AccessShareLock);
 465
 466         return build_pgstattuple_type(&stat, fcinfo);
 467 }
 468
 469 /*
 470  * pgstat_index_page -- for generic index page
 471  */
 472 static void
 473 pgstat_index_page(pgstattuple_type * stat, Page page,
 474                                   OffsetNumber minoff, OffsetNumber maxoff)
 475 {
 476         OffsetNumber i;
 477
 478         stat->free_space += PageGetFreeSpace(page);
 479
 480         for (i = minoff; i <= maxoff; i = OffsetNumberNext(i))
 481         {
 482                 ItemId          itemid = PageGetItemId(page, i);
 483
 484                 if (ItemIdIsDead(itemid))
 485                 {
 486                         stat->dead_tuple_count++;
 487                         stat->dead_tuple_len += ItemIdGetLength(itemid);
 488                 }
 489                 else
 490                 {
 491                         stat->tuple_count++;
 492                         stat->tuple_len += ItemIdGetLength(itemid);
 493                 }
 494         }
 495 }